Jump to content
Delascorpion

ANSWERED Evernote with Excel tables - vbs script

Recommended Posts

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

  • Like 1

Share this post


Link to post

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?

Share this post


Link to post

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

Share this post


Link to post

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!

Share this post


Link to post

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

Share this post


Link to post

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

Share this post


Link to post

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.

Share this post


Link to post

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

Share this post


Link to post

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!

Share this post


Link to post

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: "&","<",">".)

Share this post


Link to post

Hello,

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

Thnx!

Share this post


Link to post

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

Share this post


Link to post

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

Share this post


Link to post

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?

Share this post


Link to post

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.

Share this post


Link to post

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. 

Share this post


Link to post

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.

Share this post


Link to post

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

  • Like 2

Share this post


Link to post

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)

  • Like 1

Share this post


Link to post

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

Share this post


Link to post

 

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

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

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

Share this post


Link to post
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.

Share this post


Link to post
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. 

Share this post


Link to post

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

Share this post


Link to post
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.

Share this post


Link to post

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>

 

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