How can I preserve the format while exporting data from excel to evernote

Question

I have the following code in excel which imports the data from every row and column into a single note for every row. But it doesn't format the data as it is in excel but just prints the cell contents as it is.

This is how it looks now when I import the .enex file

This is how it looks in excel.

Code

Option Explicit

Sub OutputNotesXML()

Dim iRow As Long

Close #1
With ActiveSheet
    'For iRow = 2 To 2
    Open ThisWorkbook.Path & "\evernote-import.enex" For Output As #1
        Print #1, "<?xml version=" & Chr(34) & "1.0" & Chr(34) & " encoding=" & Chr(34) & "UTF-8" & Chr(34) & "?>"
        Print #1, "<!DOCTYPE en-export SYSTEM " & Chr(34) & "http://xml.evernote.com/pub/evernote-export.dtd" & Chr(34) & ">"
        Print #1, "<en-export export-date=" & Chr(34) & "20120202T073208Z" & Chr(34) & " application=" & Chr(34) & "Evernote/Windows" & Chr(34) & " version=" & Chr(34) & "4.x" & Chr(34) & ">"

    For iRow = 2 To .Cells(.Rows.Count, "A").End(xlUp).Row
        Print #1, "<note><title>"
        Print #1, .Cells(iRow, "A").Value 'Title
        Print #1, "</title><content><![CDATA[<?xml version=" & Chr(34) & "1.0" & Chr(34) & " encoding=" & Chr(34) & "UTF-8" & Chr(34) & "?>"
        Print #1, "<!DOCTYPE en-note SYSTEM " & Chr(34) & "http://xml.evernote.com/pub/enml2.dtd" & Chr(34) & ">"
        Print #1, "<en-note style=" & Chr(34) & "word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space;" & Chr(34) & ">"
        Print #1, CBr(.Cells(iRow, "B").Value) & vbNewLine 'Note
        Print #1, CBr(.Cells(iRow, "C").Value) & vbNewLine 'Note
        Print #1, CBr(.Cells(iRow, "D").Value) & vbNewLine 'Note

        Print #1, CBr(.Cells(iRow, "E").Value) 'Note
        Print #1, CBr(.Cells(iRow, "F").Value) 'Note
        Print #1, CBr(.Cells(iRow, "G").Value) 'Note
        Print #1, CBr(.Cells(iRow, "H").Value) 'Note
        Print #1, CBr(.Cells(iRow, "I").Value) 'Note
        Print #1, CBr(.Cells(iRow, "J").Value) 'Note
        Print #1, CBr(.Cells(iRow, "K").Value) 'Note
        Print #1, CBr(.Cells(iRow, "L").Value) 'Note
        Print #1, CBr(.Cells(iRow, "M").Value) 'Note
        Print #1, CBr(.Cells(iRow, "N").Value) 'Note
        Print #1, CBr(.Cells(iRow, "O").Value) 'Note
        Print #1, CBr(.Cells(iRow, "P").Value) 'Note
        Print #1, CBr(.Cells(iRow, "Q").Value) 'Note
        Print #1, CBr(.Cells(iRow, "R").Value) 'Note
        Print #1, CBr(.Cells(iRow, "S").Value) 'Note
        Print #1, CBr(.Cells(iRow, "T").Value) 'Note
        Print #1, CBr(.Cells(iRow, "U").Value) 'Note
        Print #1, CBr(.Cells(iRow, "V").Value) 'Note
        Print #1, CBr(.Cells(iRow, "W").Value) 'Note
        Print #1, CBr(.Cells(iRow, "X").Value) 'Note
        Print #1, CBr(.Cells(iRow, "Y").Value) 'Note
        Print #1, CBr(.Cells(iRow, "Z").Value) 'Note
        Print #1, CBr(.Cells(iRow, "AA").Value) 'Note
        Print #1, CBr(.Cells(iRow, "AB").Value) 'Note
        Print #1, CBr(.Cells(iRow, "AC").Value) 'Note
        Print #1, "</en-note>]]></content><created>"




        'Print #1, .Cells(iRow, "D").Text 'Created Date in Evernote Time Format...
        'To get the evernote time, first convert your time to Zulu/UTC time.
        'Put this formula in Column D: =C2+TIME(6,0,0) where 6 is the hours UTC is ahead of you.
        'Then right click on your date column, select format, then select custom. Use this custom code: yyyymmddThhmmssZ
        Print #1, "</created><updated>201206025T000001Z</updated></note>"
    Next iRow
    Print #1, "</en-export>"
    Close #1

End With

End Sub

Function CBr(val) As String
    'parse hard breaks into to HTML breaks
    CBr = Replace(val, Chr(13), "")
    CBr = Replace(CBr, "&", "&amp;")
End Function

'I modified this code from Marty Zigman's post here: http://blog.prolecto.com/2012/01/31/importing-excel-data-into-evernote-without-a-premium-account

narikkadan · Answer 1 · Jan 5, 2023

The contents for an Evernote note are in ENML, which is a superset of xHTML. You'll see that the list of permitted elements includes tags like <table>, <tr> and <td>, so you can use those to construct an html table for the note content.

An alternative solution would be to do it via CSS. The caveat there is that the CSS will have to go in the style attribute for each element, as an inline style. Note that the <br/> tag is also supported.