Jump to content
  • 0
Robert C.

Evernote to Excel Sheet - SO CLOSE TO BEING AWESOME

Idea


(This post has been edited to clear up some confusion).


 


I would like to suggest that Evernote implement a basic rich text option for copying of its note tables for use in Excel or Word that includes the note body text. [edit]


 


Right now, if I select some notes in the table view and copy (CTRL C), I can get a very usable table for Excel or Word, like the table attached (see below). It has everything (tags, title, dates...) but not the note body text. [edit]


 


This table is quite useful, but it would be awesome if it could also included a rich text version of the body of the note [edit].  I know Evernote can do this because rich text is basically what you get with the HTML export. But the HTML export is a mess for Excel.  All that Evernote needs is to enable rich text for this table list copy.  Something like that would be EXTREMELY useful for utilizing Evernote with Excel.

 


(The rest of this post is my particular situation, which may be of interest, but is not necessary reading for my suggestion).


 


REVIEW PROJECT FROM HELL:  My situation is that I'm an attorney that has been asked to review 1.8 TB (yes, TERAbytes) of video, audio, documents, and other files.  No one knew how it could be done.  Even legal document software suites balked at the size and presented very difficult (and expensive) problems if used.


 


EVERNOTE BUSINESS TO ALLOW MULTIPLE ATTORNEY REVIEW: My solution was Evernote Business.  I signed up about 8 attorneys in our firm to Evernote Business (which we hadn't been using previously). 


 


HOW TO REVIEW 1.8 TB OF DATA ON EB:  The real problem was how to get Evernote set up to review all that data.  Importing wasn't an option. I wasn't about to bomb Evernote with 1.8 TB of data!


 


What I came up with was importing file shortcuts from a network drive into Evernote Business.  So all the EB notes have file shortcuts to our network drive, which work flawlessly while EB is used in-house.  It doesn't work remotely, but I never expected (or wanted) it to.


 


NOT A PAINLESS PROCESS, BUT IT WORKED!   Creating thousands of notes from shortcut files was a bit laborious.  At first blush, it's a simple process: select a bunch of file shortcuts and "Send to" Evernote.  Great... but it turns out that EB starts having problems when too many files are imported this way (and we are just talking shortcuts, mostly).  I knew I was totally pushing EB to it's "I really don't think it's designed for this" limits.  But I managed it by breaking up the import into parts (usually 300 files at a time or so).  It has it's glitches and was a bit of a pain.  I learned to pray to just about every imaginable deity when I pressed the SYNC button.  But overall, it worked, and I got my firm up and running on this huge review.


 


PRODUCT TO CLIENT:  Here's the rub.  EB is great for in-house, but we need to provide the client with some kind of work product.  Excel was the obvious choice (everyone has it and knows or can figure out how to use it).  The client didn't need the actual data (who the hell DOES need 1.8 TB of data?).  The client just needed our notes/tag for review of the data.  Moreover, the client did not use (nor want) to utilize EB for a laundry list of reasons.  The question is HOW to get a good Excel table out of Evernote?


 


I researched high and low but never found an EB option for simple and useable export notes to Excel.  I've rigged a Word macro to help me clean up HTML exports (which I then copy to Excel), but that is a headache and incredibly time consuming with the large number of notes I have to deal with.  And the simple CTRL C is SO SO close to EXACTLY what I need.  It kills me that EB doesn't have this.


post-90978-0-75027500-1410797565_thumb.p

  • Like 1

Share this post


Link to post

26 replies to this idea

Recommended Posts

  • 0

Hi - as you've found,  there's no easy way to copy notes directly to Excel.  Despite your detailed exposition I didn't understand exactly what you wanted to copy - was it just the table of links?  Or some comments typed in above/ below that table?  Or both?

 

If you're working with the Windows client you may have access to a Table of Contents note which,  if you select a group of other notes,  will give you a TOC listing links to those notes.  If you then copy and pasted the TOC elsewhere,  the tables at least would paste into Excel (I think - not tested!!)

 

Likewise if you search in List view,  then Ctrl-A / Ctrl-V from that window,  the results will paste into Excel.

 

Apologies if that's not helpful,  but I'm not getting what you need to copy...

Share this post


Link to post
  • 0

My apologies for any confusion.  I want the note body text to be able to be copied as part of a table (like list view). Everything else can be copied with ease to Excel or Word. Tags, tite, dates.... everything that shows up in the list view can be easily copied as a table (with links or without). But the note body text (which isn't part of the list view) is the thing that's not possible at this time.

 

Hope that clears that up. 

 

This is what I can easily do:

 

1) First, I select some notes (CTRL C)... (I tried like heck to attach a screenshot of that, but the uploader is not playing nice today).

 

2) Then I paste into Excel or Word (CTRL V).  With no fuss, insto-table. And that's pasted text, not links (ala table of contents).

 

I just want all that plus a body text column. I'm hoping for EB to have this capability in the future.  It practically has it now.

 

We know EB can do a simple list view copy of note data for everything BUT the note body text.  We also know that EB can export to HTML (which is rich text), and the note body text is exported as rich text in that process.   So the capability is there in EB.   What I propose is an export option that takes the data from the list view and adds the note body text as a column of data.

 

Obviously, note attachments, color and such could not be part of the exported table. But that's fine because you don't use Excel for attachments and frills. 

 

It just seems to me that this isn't that much of a stretch of EB existing capabilities.  And it would be a simple integration into spreadsheet and word processing programs.

  • Like 1

Share this post


Link to post
  • 0

Robert,

 

Great job on what you've accomplished here!

 

What's your timeframe - by when do you need these Excel table(s)? The reason I ask is that there will very soon be a really easy way to accomplish this task. Can't say more just now, but depending on your timing, I could likely help you set up a solution to meet your needs (no cost involved - you could be a great case study).

Share this post


Link to post
  • 0

Thanks, Phil.

 

This is an ongoing process and project.  We've only made it through about 10% of the data after 2 months of work.  This project is going to be around for a good while longer.   However, the client needs the Excel sheets periodically to meet demands on their end and as a sort of "progress report" from us.  So whatever help you can offer, believe me I am desperate for it.  Creating the Excel sheets through the intensely jury-rigged system I've come up with is a pain and very time consuming.

 

By the way, the client was pretty blown away when we showed them what we were doing with Evernote.  I sat them down in our conference room, They never imagined having such a robust database.  But they want us to keep the EN database and have the Excel sheets for their reference.

 

Also, I have to report that, aside from me, the attorneys who are working on this project had never touched Evernote before.  It was a learning curve for everyone.  There have been show-stopping glitches from time to time, but no one doubts that EB was the right way to go.  Everyone is pretty pleased with EB overall.

 

As for a case study, I can certainly provide some insight on this monster!  I feel like I've been flying by the seat of my pants since we got it.  I have Evernote to thank for even being remotely able to deal with this project, but I'm not aware of any rules or guides for how to deal with its scale.

 

So thanks in advance and in double for any help you can provide!

 

Robert Cathcart

Share this post


Link to post
  • 0

Hi everyone,

 

having a full exportation into Excel is a Evernote feature I am craving for and desperate about since quite a long time now. It would save me a lot of time.

Please let me know about any progress 

 

Hugs from Spain

  • Like 1

Share this post


Link to post
  • 0

Evernote will have a new SDK (software development kit) for the Windows/.NET environment available within a few days, for people to start testing and playing with.  This kit will make it MUCH easier to work with Evernote programmatically than was previously possible.

 

One of the features of this new kit is Microsoft Office VBA compatibility.  This means it will be possible to integrate Evernote into Word, Excel, etc. with literally just a few lines of VBA code inside of a Word or Excel file.  And there will be samples to help get you started.

 

So exporting to Excel, as is being discussed in this thread, will be a piece of cake!

  • Like 2

Share this post


Link to post
  • 0

Evernote will have a new SDK (software development kit) for the Windows/.NET environment available within a few days, for people to start testing and playing with.  This kit will make it MUCH easier to work with Evernote programmatically than was previously possible.

 

One of the features of this new kit is Microsoft Office VBA compatibility.  This means it will be possible to integrate Evernote into Word, Excel, etc. with literally just a few lines of VBA code inside of a Word or Excel file.  And there will be samples to help get you started.

 

So exporting to Excel, as is being discussed in this thread, will be a piece of cake!

 

:) :) :) :) :) :) :) :)  Is it Christmas already?  

  • Like 1

Share this post


Link to post
  • 0

So exporting to Excel, as is being discussed in this thread, will be a piece of cake!

 

Answer to my prayers!  I really look forward to this!

Share this post


Link to post
  • 0

...but where's the mistletoe and wine?  Nevermind: Evernoted to play with later...  thanks!

Share this post


Link to post
  • 0

Robert - I'm working on a spreadsheet that closely matches the sample you sent me - stay tuned, hope to have it ready today.

Share this post


Link to post
  • 0

Robert - I'm working on a spreadsheet that closely matches the sample you sent me - stay tuned, hope to have it ready today.

 

Much appreciated.  What you are doing will allow me to spend more time working on the project and less time fighting with Evernote/Excel.  

Share this post


Link to post
  • 0

 i think so  i can not Despite your detailed exposition I didn't understand exactly what you wanted to copy was it just the table of links.

Share this post


Link to post
  • 0

I've gone through the sample excel vba files, and figured out how to write out notes and search on different tags. I'm trying to scan through the notes, pull out ToDo items that are not checked, consolidate them, and then upload them as a new note with a "DailyToDo" tag. I can get to the point where I create an EDML string with everything I want. But I can't figure out how to create and upload the new note. 

 

Code snippet is:

    sNewNote = sEDMLPreamble & sNewNote & "</en-note>"
    Dim edNewNote As EdamNote
    Set edNewNote = noteStore.createnote
    Set edNewNote.Title = "Daily Task List from ToDo Notes"
    Set edNewNote.Content = sNewNote
But I get the error "Invalid procedure call or argument on the first Set edNewNote line. Can someone give me some help?

Share this post


Link to post
  • 0

Hi - was this issue ever resolved. I really need to export Evernote notes into one big excel file. 

 

Just like the user above I need to create a Work in progress table that has the name of the note and the text in the body of the note in a separate column in excel. 

 

Can anyone help me with this? 

Share this post


Link to post
  • 0

Hi.  Most of this discussion was about the conversion of a LOT of different data into spreadsheets.  If your needs are more modest,  you may find it possible to export your notes to HTML and simply open the file(s) in Excel.  Have you tried any experiments to export into different formats and import to Excel?

Share this post


Link to post
  • 0

After much research and trial and error, I came up with a fairly good solution. I'm not a coder. I know enough to be dangerous, and I stole much of this code from an old excel post (credited in the code).  I invite anyone who can help to clean this up or improve on it, but at least it works.  It creates 4 macros, but the one to run is ReadNotesXML, choose the .enex file you want to import and you'll get the note data in your Excel sheet.

 

One of the limitations it has is size.  If the .enex file is over 100 MB, the macro won't work. So, if you have some sizeable attachments to your notes, you could run into this problem.

 

Here's the VB macro code to import Evernote text into Excel:

 

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) '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, "&", "&")
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/

' This will read ENEX file (Evernote export file) into Excel worksheet
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, "'", "'")
StripTags = Replace(StripTags, " ", " ")
' Free
Set RE = Nothing
Set allMatches = Nothing
End Function

Sub r2i()
Dim lLastRow As Long
Dim lLastCol As Long
Dim rgLast As Range
Dim rgSrc As Range
Dim rgDst As Range
Dim i, j As Integer
Dim RE As Object, allMatches As Object
Set RE = CreateObject("vbscript.regexp")
Dim m As String

Set rgLast = Range("A1").SpecialCells(xlCellTypeLastCell)
lLastRow = rgLast.Row
lLastCol = rgLast.Column

Set rgSrc = Range(Cells(2, 2), Cells(lLastRow, 2))
Set rgDst = Range(Cells(2, 1), Cells(lLastRow, 1))
RE.Pattern = "\((.*?)\)"
RE.IgnoreCase = True
RE.Global = True

For i = 1 To rgSrc.Count
' Getting stuff in brackets
Set allMatches = RE.Execute(rgSrc.Cells(i, 1))
m = ""
If allMatches.Count > 0 Then
For j = 0 To allMatches.Count - 1
If allMatches.Count = 1 Then
m = allMatches(j).submatches(0)
Else
m = m & allMatches(j).submatches(0) & ";"
End If
Next
rgDst.Cells(i, 1) = m
Else
m = rgDst.Cells(i, 1)
rgDst.Cells(i, 1) = rgSrc.Cells(i, 1)
rgSrc.Cells(i, 1) = m
End If
Next
Set RE = Nothing
Set allMatches = Nothing
End Sub

Sub i2r()
Dim lLastRow As Long
Dim lLastCol As Long
Dim rgLast As Range
Dim rgSrc As Range
Dim rgDst As Range
Dim i As Integer
Dim RE As Object, allMatches As Object
Set RE = CreateObject("vbscript.regexp")
Dim m As String

Set rgLast = Range("A1").SpecialCells(xlCellTypeLastCell)
lLastRow = rgLast.Row
lLastCol = rgLast.Column

Set rgSrc = Range(Cells(2, 2), Cells(lLastRow, 2))
Set rgDst = Range(Cells(2, 1), Cells(lLastRow, 1))
RE.Pattern = "^(.*?)\s+\(.*"
RE.IgnoreCase = True
RE.Global = True

For i = 1 To rgSrc.Count
' Getting stuff in brackets
Set allMatches = RE.Execute(rgSrc.Cells(i, 1))
If allMatches.Count > 0 Then
rgDst.Cells(i, 1) = allMatches(0).submatches(0)
Else
m = rgDst.Cells(i, 1)
rgDst.Cells(i, 1) = rgSrc.Cells(i, 1)
rgSrc.Cells(i, 1) = m
End If
Next
Set RE = Nothing
Set allMatches = Nothing
End Sub

 

Share this post


Link to post
  • 0

Yeah, this is a dealbreaker for me. If Evernote can't get this fixed - have an ability to export to Excel very soon - then I'm going to move oo to a software that does, even though I really like Evernote!

Share this post


Link to post
  • 0

You read the whole - new SDK / export to HTML / VBA thread,  right?  There's no actual menu button,  but you should be able to get something working if you need it...

Share this post


Link to post
  • 0

Gazumped,

Thank you for responding to question on the EVERNOTE forums. Do you have to the new SDK / export to HTML / VBA Thead? I can't seem to find it after 30 minutes of searching.

Thank you in advance.

Scott

Share this post


Link to post
  • 0

Hi.  I was talking about this thread - have a look back over page 1 of the various comments!

  • Like 1

Share this post


Link to post
  • 0
On 8/26/2015 at 9:00 PM, R. Cathcart said:

After much research and trial and error, I came up with a fairly good solution. I'm not a coder. I know enough to be dangerous, and I stole much of this code from an old excel post (credited in the code).  I invite anyone who can help to clean this up or improve on it, but at least it works.  It creates 4 macros, but the one to run is ReadNotesXML, choose the .enex file you want to import and you'll get the note data in your Excel sheet.

 

One of the limitations it has is size.  If the .enex file is over 100 MB, the macro won't work. So, if you have some sizeable attachments to your notes, you could run into this problem.

 

Here's the VB macro code to import Evernote text into Excel:

 

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) & "https://playcanadacasino.com"& 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) '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 ? =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, "&", "&")
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/

' This will read ENEX file (Evernote export file) into Excel worksheet
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, "'", "'")
StripTags = Replace(StripTags, " ", " ")
' Free
Set RE = Nothing
Set allMatches = Nothing
End Function

Sub r2i()
Dim lLastRow As Long
Dim lLastCol As Long
Dim rgLast As Range
Dim rgSrc As Range
Dim rgDst As Range
Dim i, j As Integer
Dim RE As Object, allMatches As Object
Set RE = CreateObject("vbscript.regexp")
Dim m As String

Set rgLast = Range("A1").SpecialCells(xlCellTypeLastCell)
lLastRow = rgLast.Row
lLastCol = rgLast.Column

Set rgSrc = Range(Cells(2, 2), Cells(lLastRow, 2))
Set rgDst = Range(Cells(2, 1), Cells(lLastRow, 1))
RE.Pattern = "\((.*?)\)"
RE.IgnoreCase = True
RE.Global = True

For i = 1 To rgSrc.Count
' Getting stuff in brackets
Set allMatches = RE.Execute(rgSrc.Cells(i, 1))
m = ""
If allMatches.Count > 0 Then
For j = 0 To allMatches.Count - 1
If allMatches.Count = 1 Then
m = allMatches(j).submatches(0)
Else
m = m & allMatches(j).submatches(0) & ";"
End If
Next
rgDst.Cells(i, 1) = m
Else
m = rgDst.Cells(i, 1)
rgDst.Cells(i, 1) = rgSrc.Cells(i, 1)
rgSrc.Cells(i, 1) = m
End If
Next
Set RE = Nothing
Set allMatches = Nothing
End Sub

Sub i2r()
Dim lLastRow As Long
Dim lLastCol As Long
Dim rgLast As Range
Dim rgSrc As Range
Dim rgDst As Range
Dim i As Integer
Dim RE As Object, allMatches As Object
Set RE = CreateObject("vbscript.regexp")
Dim m As String

Set rgLast = Range("A1").SpecialCells(xlCellTypeLastCell)
lLastRow = rgLast.Row
lLastCol = rgLast.Column

Set rgSrc = Range(Cells(2, 2), Cells(lLastRow, 2))
Set rgDst = Range(Cells(2, 1), Cells(lLastRow, 1))
RE.Pattern = "^(.*?)\s+\(.*"
RE.IgnoreCase = True
RE.Global = True

For i = 1 To rgSrc.Count
' Getting stuff in brackets
Set allMatches = RE.Execute(rgSrc.Cells(i, 1))
If allMatches.Count > 0 Then
rgDst.Cells(i, 1) = allMatches(0).submatches(0)
Else
m = rgDst.Cells(i, 1)
rgDst.Cells(i, 1) = rgSrc.Cells(i, 1)
rgSrc.Cells(i, 1) = m
End If
Next
Set RE = Nothing
Set allMatches = Nothing
End Sub

Thanks I had some issues, Thanks to Evernote Forum, I found a solution to this form of support is simply wonderful.

Share this post


Link to post

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...