I'm trying to find a way to import Evernote note body text (i.e. the actual "note" part of the note) into Excel.

Importing things like titles, tags, dates, etc. is easy, because you can copy from EN's list view and paste into Excel. But that does not include the note body text of those notes.

Also, to be clear, I want to import lots of notes, not just one note.

So I found a macro for Excel that does a good job of importing note text from .enex files. The trouble is that it stops working when the enex file is too big. I've had a 70,000 KB enex file import, but not one for 140,000 KB. Anyone have any idea how to address this limitation? The macro I'm using is below, and the line it seems to get hung up on is WholeFileContent = Input$(LOF(fp), fp). Thanks!


Sub ReadNotesXML()
Dim fdgOpen As FileDialog
Dim fp As Integer
Dim i As Integer
Dim DataLine As String, WholeFileContent As String
Dim RE As Object, allMatches As Object
Set RE = CreateObject("vbscript.regexp")

Set fdgOpen = Application.FileDialog(msoFileDialogOpen)
With fdgOpen
.Filters.Add "Evernote files", "*.enex", 1
.Title = "Please open Evernote file..."
.InitialFileName = "."
.InitialView = msoFileDialogViewDetails
.Show
End With
' MsgBox fdgOpen.SelectedItems(1)
fp = FreeFile()
WholeFileContent = ""
Open fdgOpen.SelectedItems(1) For Input As #fp
WholeFileContent = Input$(LOF(fp), fp)
Close #fp
' Removing CR&LF line endings
WholeFileContent = Replace(WholeFileContent, Chr(10), "")
WholeFileContent = Replace(WholeFileContent, Chr(13), "")
' Worksheets(1).Cells(5, 5) = WholeFileContent
' First line
Worksheets(1).Cells(1, 1) = "Title"
Worksheets(1).Cells(1, 2) = "Content"
Worksheets(1).Cells(1, 3) = "Created"
Worksheets(1).Cells(1, 4) = "Updated"
' Filter for title
RE.Pattern = "<title>(.*?)<\/title>"
RE.IgnoreCase = True
RE.Global = True
RE.MultiLine = True
Set allMatches = RE.Execute(WholeFileContent)
For i = 0 To allMatches.Count - 1
Worksheets(1).Cells(2 + i, 1) = allMatches(i).submatches(0)
Next
' Filter for content
RE.Pattern = "<content>(.*?)<\/content>"
'RE.IgnoreCase = True
'RE.Global = True
Set allMatches = RE.Execute(WholeFileContent)
For i = 0 To allMatches.Count - 1
Worksheets(1).Cells(2 + i, 2) = StripTags(allMatches(i).submatches(0))
Next
' Filter for created
RE.Pattern = "<created>(.*?)<\/created>"
'RE.IgnoreCase = True
'RE.Global = True
Set allMatches = RE.Execute(WholeFileContent)
For i = 0 To allMatches.Count - 1
Worksheets(1).Cells(2 + i, 3) = allMatches(i).submatches(0)
Next
' Filter for updated
RE.Pattern = "<updated>(.*?)<\/updated>"
'RE.IgnoreCase = True
'RE.Global = True
Set allMatches = RE.Execute(WholeFileContent)
For i = 0 To allMatches.Count - 1
Worksheets(1).Cells(2 + i, 4) = allMatches(i).submatches(0)
Next
' Free
Set RE = Nothing
Set allMatches = Nothing
End Sub

Function StripTags(inString As String) As String
Dim RE As Object, allMatches As Object
Set RE = CreateObject("vbscript.regexp")
' Keeping enters
inString = Replace(inString, "</div>", " ")
' Removing other <tag>-s
RE.Pattern = "<[^>]+>"
RE.IgnoreCase = True
RE.Global = True
StripTags = RE.Replace(inString, "")
' Cleaning up strange things
StripTags = Replace(StripTags, "]]>", "")
StripTags = Replace(StripTags, "&apos;", "'")
StripTags = Replace(StripTags, "&nbsp;", " ")
' Free
Set RE = Nothing
Set allMatches = Nothing
End Function