Jump to content

Power Query (Excel) to ENEX Conversion


Recommended Posts

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?






  • Like 1
Link to comment

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


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


Link to comment

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Create New...