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 in Others by Kithuzzz
• 33,140 points
70 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 by narikkadan
• 58,640 points

Related Questions In Others

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
• 58,640 points
219 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
• 58,640 points
166 views
0 votes
1 answer

I have filtered my Excel data and now I want to number the rows. How do I do that?

Solution Filter your data. Select the cells you want ...READ MORE

answered Nov 14, 2022 in Others by narikkadan
• 58,640 points
233 views
0 votes
1 answer

How can I store the data of an open excel workbook in a collection using BluePrism?

To do what you want is like ...READ MORE

answered Nov 24, 2022 in Others by narikkadan
• 58,640 points
224 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
• 58,640 points
219 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
• 58,640 points
262 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
• 58,640 points
639 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
• 58,640 points
602 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
• 58,640 points
163 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