Power Query (Excel) to ENEX Conversion

[Anklebuster 1]

Since ENEX is simply an XML file, it's not too hard to convert spreadsheets to Evernote notes. Way back in 2012, I wrote a clumsy converter. Too bad I hadn't found Marty Zigman's blog post (Importing Microsoft Excel Data into Evernote without a Premium Account) before I had finished mine. Anyway, in the comments, many Excel users chimed in with various hints, questions and, most importantly, modifications to Marty's script.

I have to use Doc.Write and UTF-8, because I want to preserve the Chinese characters that are pasted into Excel. Unfortunately, I can't figure out how to force the linebreaks to appear in Evernote. Since I'm transforming the file with Power Query, I thought I could use its linefeed representation, which is #(lf). When that didn't work, I tried \n, which didn't work, either.

Has anyone been able to preserve embedded line breaks in spreadsheet cells?

 

Cheers,

 

Mitch

 

[Anklebuster 1]

In case anyone is interested, I fixed the line break issue and another, related, issue by taking the following steps:

In Power Query - Replace values for each character that needs special handling. #(lf) is changed to <br /> and & is changed to &amp;

In Excel VBA - Although the referenced macro (and variants) make the above transformations in a function called cBr, I found that it didn't work with Doc.Write. So I skipped the call. The relevant code,

DOC.WriteText CBr(.Cells(iRow, "B").Value) 'Note

becomes

DOC.WriteText .Cells(iRow, "B").Value 'Note