Evernote with Excel tables - vbs script

[Delascorpion 3]

Hi All! 

Some time ago I found out that it's really pitty that I cannot import Excel spreadsheet in Evernote page as a spreadsheet(not as a file). 

And I've written vbs script for Excel. (Sorry if it is not too good - i'm not a vbs programmer)

Main function: 

- Export all workbooks with its names to Evernote .enex file

- Support text size, bold, italic, Color

- Support different encoding (from ansii to utf-8 e.t.c) Default Excel character set is set to utf-8.

 

Please write me if you have any problems, using it. 

 

Hope it helps. 

 

Upd. Added video instruction for this script HERE

Excel_to_Evernote.txt

[Delascorpion 3]

UPD. Just checked synchronization with imported documents. 

Fixed one problem. Updated file. 

Synchronization works fine now

[cdissler 15]

This is a great idea, I've needed this recently. I've added the script and run it, but when I import the enex to evernote the sheet with the data is blank. I don't have a lot of experience with this, but what could I be doing wrong?

[Delascorpion 3]

I had same problem when I uploaded file with images. 

That should be plain spreadsheets only (without images and graphs) 

Or may be problem with charsets. I used ansii to utf-8 encoding may be in your way you shouldn't do that. 

Try change this line in script

Excel_charset = "Windows-1251" 

to

Excel_charset = "utf-8"

 

Or send me examle of excel file which is not working

 

This is my first try, so probably we will find there some bugs. 

 

Upd. Changed default character-set in script to "utf-8". So you may just download new file from 1-st post

[cdissler 15]

Hmm, I guess the problem I'm having is with the data. I copied data, text only, from 97' access into an excel sheet and it won't pull when I run the macro. However, I did a test in a second sheet and it pulled fine. This is great! Keep up the good work!

[Delascorpion 3]

May be. but i cannot check it to fix. If you have an example - send me. 

[cdissler 15]

Sorry, the sheet that doesn't import data is client contact information, can't send it.

[gazumped 11,664]

Thanks for sharing - I use spreadsheets a lot,  and I use Evernote even more; but the two have always been separate..  except that I keep the some of the Excel files in notes and edit them from there.  Not sure what difference your script will make,  but I'll give it a try...

[cdissler 15]

Problem solved: There were too many columns in the spreadsheet I was importing. Looks like Evernote caps out somewhere under 100 and over 50.

[Delascorpion 3]

Just tried with 200 columns - imports fine. 

So i think problem is with overall size(rows and columns count).

[cdissler 15]

Just tried with 200 columns - imports fine. 

So i think problem is with overall size(rows and columns count).

Dang dude, I'm out of ideas.

 

Still very useful.

[Delascorpion 3]

Upd. 

Had a little problem with & character - if you had this character in your excel spreadsheet, you recieved blank page in Evernote.

Now it's fixed in 1-st message

[cdissler 15]

Upd. 

Had a little problem with & character - if you had this character in your excel spreadsheet, you recieved blank page in Evernote.

Now it's fixed in 1-st message

That did the trick for me. Thanks for this it's great!

[Delascorpion 3]

Upd. 

Updated version in 1-st message. 

Fixed some bugs with special characters. Now even if you have images and other graphs in xls, it wil export OK, without it. 

(Fixed bugs with symbols: "&","<",">".)

[saud89 0]

Hello,

         Im not an expert in porgraming, kindly can someone tell me hot to apply the script. BTW i use a mac air.

Thnx!

[Iscromain 1]

Hello,

 

I too would like instructions on how to use the VBA.  I am able to copy and paste the text file into a test spreadsheet I try to run the macro and I get an "invalid outside procedure" compile error at the "Close #1" line of the code.

 

Any help would be great.  I use excel often and evernote everyday.

 

Romain

[taraloca 0]

I am just starting to use Evernote, switching from OneNote and I miss the ability to edit spreadsheets in a notebook.  How do I use this script?  I have searched on line and am coming up blank.  Does anyone have a document that will walk me through using this?

 

Thanks....Taraloca

[gazumped 11,664]

There is no support unless the original poster is still around to help you.  What device are you working with here?  Can you use Excel via Office or another app?

[taraloca 0]

I switched to Evernote, trying to digitize my files, receipts, statements, etc...I started with OneNote, but I work on a Mac and started on a PC. OneNote for Mac is horrible.  Anyway, I just want to be able to edit a spreadsheet inside Evernote like I could with OneNote.  I have Office Excel.

[Analyst444 182]

taraloca - Have you tried "attaching" your Excel document to a Note in Evernote and opening/editing it there? If not, give that a try and see if that meets your needs. 

[Delascorpion 3]

Oh, sorry, I haven't monitored this thread for a long.

This script usage is quite easy.

You just take your excel spreadsheet, executes this script inside MS Excell, and import. *enex files, , which you receive after that to Evernote.

If you need I may write detailed instructions with screenshots or video.

[Delascorpion 3]

I've added video HowTo - instruction of using this VBS script HERE 

[JMichaelTX 4,117]

@Delascorpion:  Do I understand your process/script correctly that it results in a HTML table in the EN Note, but it is just a table, not a real spreadsheet that can do calculations inside of Evernote?

 

If all you want is a nicely formatted table from Excel to Evernote, try

1. selecting the cells in Excel,
2. copy to MS Word (which creates a Word table)
3. Make any adjustments you like to the table format, like Table & cells autofit to text.
4. Select the Word table, copy, and paste into an EN Note.

I have used this process for years and it works well.

[Delascorpion 3]

JMichael, thank you a lot for your comment.

Your are absolutely right, just html-table. Without real spreadsheet. But Evernote is not Excel, there is no any.

I didn't tried twith WORD, and I had plenty of tables in excel, so I needed more or less automated way to export tables with formatting.

Your way is either good if you need to export one table and do it with hands)

[lob 5]

Is it possible to format the excel data in such a way it is imported in Evernote not as a table, but as an editable check list? Eg:

<> To do item 1

<> To do item 2

<> Etc

[La Caja de Fotos 0]

 

I can import an excel table without losing design and keep working on it ?. Thank you.

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

José Ramón, técnico informático

[Delascorpion 3]

4 hours ago, La Caja de Fotos said:

 

I can import an excel table without losing design and keep working on it ?. Thank you.

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

José Ramón, técnico informático

Yes, exactly. script supports Color of cell and font, and font-type i.e. italian or bold.

[Delascorpion 3]

On 11.03.2016 at 11:54 PM, lob said:

Is it possible to format the excel data in such a way it is imported in Evernote not as a table, but as an editable check list? Eg:

<> To do item 1

<> To do item 2

<> Etc

I believe it does, if evernote supports checkboxes, but for that you need other script. 

[lob 5]

Yeah, no prob. I think I have it sorted out.

[JMichaelTX 4,117]

5 hours ago, lob said:

Yeah, no prob. I think I have it sorted out.

If you are able to modify the Excel VBA macro to export as a checkbox list, instead of a table, please share with us.
I'm sure it would be of interest to a lot of users.

Thanks.

[lob 5]

Well, I haven't yet. But I think I know one way to do it. I looked at the code generated around each item when exporting a checkbox list, and simply wrote a formula to generate this code inside a single cell. My thinking is that the VBA script would simply grab this code from Excel as it executes. Anyways, assuming you have a column where the number of results could vary, you would have a formula that follows this structure, but is a lot longer:

=IF(COUNTIF(B1,"?*"),"<en-note><div><en-todo checked=^^false^^/>"&B1,"")&IF(COUNTIF(B2,"?*"),"<en-note><div><en-todo checked=^^false^^/>"&B2,"")&IF(COUNTIF(B3,"?*"),"<en-note><div><en-todo checked=^^false^^/>"&B3,"")&IF(COUNTIF(B4,"?*"),"<en-note><div><en-todo checked=^^false^^/>"&B4,"")&IF(COUNTIF(B1,"?*"),"&nbsp;<br/></div></en-note>]]></content><created>"&TEXT(NOW(),"yyyymmddThhmmssZ")&"</created><updated>"&TEXT(NOW(),"yyyymmddThhmmssZ")&"</updated><note-attributes><author>"&PLACE a CELL REF CONTAINING YOUR USERNAME HERE&"</author></note-attributes></note></en-export>","")

Then in another cell, use the formula =SUBSTITUTE(CELL WITH ABOVE FORMULA IN IT,"^^","""")  to replace the ^^ with ""

Assuming you had Cat is cell B1 and Dog in cell B2, this would be the result (a perfect match for what Evernote generates): 

<en-note><div><en-todo checked="false"/>Cat<en-note><div><en-todo checked="false"/>Dog&nbsp;<br/></div></en-note>]]></content><created>20160318T070813Z</created><updated>20160318T070813Z</updated><note-attributes><author>leonard.xxxxxx@gmail.com</author></note-attributes></note></en-export>