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

0 votes

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

Screenshot of the imported data 

This is how it looks in excel.

enter image description here

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
Jan 5, 2023 in Others by Kithuzzz
• 38,020 points
1,058 views

1 answer to this question.

0 votes

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.

answered Jan 5, 2023 by narikkadan
• 63,720 points

Related Questions In Others

0 votes
1 answer

How do I stop python from appending data to the same row in excel?

There is no indication in your code ...READ MORE

answered Mar 25, 2023 in Others by narikkadan
• 63,720 points
491 views
0 votes
1 answer

How Can I Round Prices to the nearest 0.95 with an Excel Formula?

Try this: =IF(OR(A3-FLOOR(A3,1)>0.95,A3=CEILING(A3,1)),CEILING ...READ MORE

answered Oct 9, 2022 in Others by narikkadan
• 63,720 points
665 views
0 votes
1 answer

How can I use a command button in excel to set the value of multiple cells in one click?

Try this: Private Scan As Integer Private Sub CommandButton1_Click() ...READ MORE

answered Oct 24, 2022 in Others by narikkadan
• 63,720 points
740 views
0 votes
1 answer

How can I sort one set of data to match another set of data in Excel?

In addition, INDEX MATCH is a more ...READ MORE

answered Oct 29, 2022 in Others by narikkadan
• 63,720 points
2,254 views
0 votes
1 answer
0 votes
1 answer

Unable to import data in excel from another website using VB code

Replace : Set ieTable = ieDoc.all.Item("report-table") With: Set ieTable = ...READ MORE

answered Sep 21, 2022 in Others by narikkadan
• 63,720 points
711 views
0 votes
1 answer

Runtime error 438 while importing data in excel from secured website using VBA

Replace With ieDoc.forms(0) .userType.Value = "1" ...READ MORE

answered Sep 23, 2022 in Others by narikkadan
• 63,720 points
852 views
0 votes
1 answer

Convert image (jpg) to base64 in Excel VBA?

Heres a function. Can't remember where I ...READ MORE

answered Sep 27, 2022 in Others by narikkadan
• 63,720 points
2,910 views
0 votes
1 answer

How can I convert excel to PDF by Libreoffice and keep all format from excel file?

"Times New Roman" typeface does not have ...READ MORE

answered Oct 3, 2022 in Others by narikkadan
• 63,720 points
1,603 views
0 votes
1 answer

How do I change the format of an excel workbook from 'General' to 'Text'

Only cells have a format for numbers. ...READ MORE

answered Mar 23, 2023 in Others by narikkadan
• 63,720 points
343 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP