Jump to content

Evernote and Excel


Recommended Posts

  • Level 5*
9 minutes ago, required_username said:

Is it possible to open a EN notebook using Excel script? If so, how?

It's not possible to open a EN Notebook (or any Filter/Search)

If you can use a link, the classic link (evernote:///view/...) will open a Note

Link to comment
  • Level 5*
19 minutes ago, required_username said:

How about open a NEW note?

I'm not familiar with Excel Scripting
I could do it on my Mac with Applescript (including the Notebook Filter/Search)

Evernote has global keyboard shortcuts for New Notes (Mac/Windows)
Windows has command line arguments like    Evernote.exe /NewNote

 

Link to comment

Very good.  In my (very) limited programing history, I've learned about anything that can be done with keystrokes and/or mouse clicks can be done in a script.  I find it difficult to believe a script can't be written moving (copy/paste) an xls or csv record to an EN note as subject/body.

Link to comment
  • Level 5*
9 minutes ago, required_username said:

I find it difficult to believe a script can't be written moving (copy/paste) an xls or csv record to an EN note as subject/body.

You've gone from Notebook to Note to xls/csv file

Did you just want the file in a note, or do you want the data parsed and a note created for each row

On my Mac, I just drag files to Evernote, or my import folder and a note is created

I use Applescript to parse csv files; for example my bank transaction files

 

Link to comment

I have 8000+ notes in 7 Outlook folders.  95% consist of a subject and body.  The remainder are emails.  Cut and paste, if I understand this you correctly, is a one-at-a-time endeavor 8000+ times.  I can get the Outlook files to a CSV of two fields, each note in one row. (BTW, I'm using "note" as a single entry in a Notebook, not as an app.) In a perfect world, I want to create a new note for each row, with the note parsing the data to subject/body.  Unless I've missed something, EN currently has no ability to run a script, obtain one row of data, parse it, and input it into a new note - a routine I imagine is quite common in most (all?) scripts.  After all, it consists of, Create a notebook, create new note in the notebook, copy row, parse, copy/paste, save, repeat.  I've done such things in VBA and JS.

From what I understand by searches here and google, it is possible to import data as a file, but not as a stand-alone note.  I'm quite surprised, given the number of folders notebooks in Outlook and other DBs, no one at EN or no EN user has written such a script.  If the capability exists, and I could muddle through it given long enough (but I'm an old guy), someone who can actually DO this, could accomplish the task during lunch.  OK, maybe longer.

Link to comment
  • Level 5*
51 minutes ago, required_username said:

EN currently has no ability to run a script, obtain one row of data, parse it, and input it into a new discussion

Evernote on the Mac platform has extensive scripting via AppleScript. I actually wrote this script for you in another post

Evernote also has an extensive API documented at https://dev.evernote.com/doc/

How about working from the Excel side.  Can scripting parse your spreadsheet and generate a file for each row?  For example http://www.excel-easy.com/vba/examples/write-data-to-text-file.html

 

Link to comment
  • Level 5*
5 hours ago, required_username said:

Hope this is the proper forum since it deals with 3rd party app and scripting.  Not actually an "idea," although it is tangentially..

Is it possible to open a EN notebook using Excel script? If so, how?

You can link to a note in a notebook.

5 hours ago, required_username said:

How about open a NEW note?

You can use a text expander and the default hotkey of Ctrl-Alt-N to create a new note.  You can also use the expander to copy what you want the title to be and paste it into the title of the newly created note.  So with one hot key copy some text and create a new note with that text.  It would look like this using PhraseExpress, modifying the sleep values depending upon machine speed:

{#HOME}{#SHIFT {#END}}{#CTRL -chars c}{#CTRL {#ALT -chars n}}{#sleep 3000}{#F2}{#sleep 3000}{#CTRL -chars v}{#ALT -chars f}{#DOWN -count 6}{#ENTER}

Link to comment

Hi required_username, just wondering if you have tried the Save to Evernote add-in for Outlook? I selected a couple of demo notes in Outlook, right-clicked and selected Save to Evernote, selected the Evernote notebook and it transferred them straight away. It added some other content as shown in the attached screen clip, but otherwise seemed to work in the way you describe. The add-in does an even better job on emails, which is what I use it for mostly

Just for my information, did you want an Excel script because you have already created the CSV? Or would you prefer an Outlook VBA script to convert your Outlook notes to Evernote? Looking at the command line options for ENScript.exe for Windows at the developer articles pointed out by DTLow, I think it could be done. Another thing, 8000 notes is a lot of notes! Would you prefer them all in a single note, or organised some particular way and tagged?

(I'm using Outlook 2016 on a Windows 10 Pro VM on a MacBook Pro 2016)

ScreenClip.png

Link to comment

I've searched for a few months (irregularly) and don't remember seeing Save to Evernote add-in for Outlook.  I'll look at it.  Yes, I have seven CSV files and asked about the Excel script because I have some experience in Excel scripts.  Outlook VBA would be fine, if already written.  I may be able to get it done, but have little time to commit to the effort.  Again, I find it surprising no one connected to EN has written a script that could potentially acquire many more users.

No, I don't want them in a single note.  Separate notes or I'll keep them in Outlook.  But, I'd prefer ditching and uninstalling Outlook and moving exclusively to EN.  (I'm using EM Client for calendar/Email.  BTW, I've found a couple of web pages describing the add-in.  Looks like it requires Outlook 2010/2013.  I'm running 2007.  Bummer.

Link to comment

OK so Save to Evernote won't work for you. I was backtracking through your previous queries and found one of your CSV files on Importing notebooks through PDF. I converted that to an XLSM with a macro to move through the CSV entries and add them as new notes in a Notebook called Quotes. This works fine on my PC, I don't think there is anything that Excel 2007 can't handle so hopefully will work for you too.  The bulk of the code adds synchronous execution to the good old Shell command, it was developed by a guru on Stack Overflow, and I just added the ConvertToEvernote macro. Before you use it you will need to modify the following variables:

ENLocation = "C:\Users\tharvey\AppData\Local\Apps\Evernote\Evernote\ENScript.exe" (need the full path to your version of Evernote, can find in the shortcut to the application)
ENNotebook = "Quotes" (set up a notebook in Evernote with the preferred name and change this to match)
ENNoteFileLocation = "C:\EN\Note.txt" (this is just a temporary file so can be located anywhere preferably on your local drive)

Let me know how you go.

By the way do you have any plans to change from Office 2007?

forum.xlsm

Link to comment

forum.xlsm runs without error.  After execution, the Notebook remains empty.  I have added a msgbox to verify text is in the variables.  It appears the text gets to ENTitle, ENNoteText, but no new note is created.  FWIW, I've run this with EN open AND closed.

For Each c In ActiveSheet.UsedRange.Columns(1).Cells
    ENTitle = c.Value 'title of the new note
    ENNoteText = c.Offset(0, 1).Value 'text of the new note
    Set ENNoteFile = fso.CreateTextFile(ENNoteFileLocation, True, True) 'create the note text file
    ENNoteFile.Write ENNoteText 'write the note text

    MsgBox ENNoteText
    ENNoteFile.Close
    ' Use ENScript.exe to create note
    exitCode = SyncShell(ENLocation & " createNote /n " & Chr(34) & ENNotebook & Chr(34) & " /i " & Chr(34) & ENTitle & Chr(34) & " /s " & ENNoteFileLocation, vbHide)
Next

Link to comment

What I would do next is to build the entire command line to add a new note via ENScript, and run that from the command prompt. That might indicate where the problem is. I expect it is just a syntax issue. The command line looks like this:

C:\>C:\full_path_to_ENScript.exe createNote /n "Notebook name" /i "Note title" /s "C:\Note Text.txt"

 

Link to comment

Thank you DTLow! I've learned about Import Folders today. Much more efficient for this purpose. I've set the note folder in the attached macro as C:\EN, set that up in Explorer or just change the macro to suit, run the macro to create the separate note files, then set Evernote to watch that folder in Evernote > Tools > Import Folders..., add C:\EN, and set the required notebook. Evernote just adds the new or changed notes, which are organised as shown in the screen clip below.

I'm sure I can use Import Folders for some of my own requirements, thanks again DTLow

forum2.xlsm

QuoteFolder.png

Link to comment
  • Level 5*
48 minutes ago, terencino said:

Thank you DTLow! I've learned about Import Folders today.

You're welcome.  I actually deleted the post, deciding you had this this discussion covered.

I'm not experienced with the excel macros and can't load the macro on my Mac
Can you add the actual text to your post

Thanks in advance

Link to comment

OK I wondered what happened to your post DTLow. I'm always open to different or better ways to get results, and your suggestion was a much better way of doing this  I'm sure this macro won't work on the Mac, because there is no equivalent to the scripting library (for FileSystemObject). I'll see if I can modify to suit though. Here is the Excel for Windows version

Sub UpdateEvernoteImportFolder()
' Response to required_username on Evernote Forums
' https://discussion.evernote.com/topic/103802-importing-notebooks-through-pdf/#comment-453125
' using Evernote import folders idea from DTLow

Dim c As Range, fso As Object, ENNoteFile As Object
Set fso = CreateObject("Scripting.FileSystemObject")

For Each c In ActiveSheet.UsedRange.Columns(1).Cells
    i = i + 1
    ENFile = "C:\EN\" & "File_" & Format(i, "00000") & ".txt"
    Set ENNoteFile = fso.CreateTextFile(ENFile, True, True) 'create the note text file
    ENNoteFile.Write c.Value & vbCrLf & c.Offset(0, 1).Value 'write the note title & text
    ENNoteFile.Close
    ' Evernote should pick up the file via Import Folders now
Next
Set fso = Nothing

End Sub
 

Link to comment

Well I had a first pass at a version for Mac but taking a lot longer due to no native Import Folders functionality in Evernote for Mac. I am using Veritrope's Evernote import folder script, however it uses the text file name as the note title, not the first line in the text file like Evernote for Windows. Adding the files manually one by one seems to go OK, but loading them quickly by the macro or dropping a heap of files on the folder seems not to work, converting only one note or crashing Evernote. I've probably set it up incorrectly or it's having issues with my versions of OSX or Evernote. Otherwise the Excel VBA script below works OK in terms of selecting the import folder and creating the files.

Sub UpdateEvernoteImportFolderForMac()
' Response to required_username on Evernote Forums, converting CSV to notes in Evernote via Excel VBA
' https://discussion.evernote.com/topic/103802-importing-notebooks-through-pdf/#comment-453125 and https://discussion.evernote.com/topic/103961-evernote-and-excel/#comment-453675
' based on Evernote import folders concept from DTLow, converted from Windows to Mac
' using Evernote Import Folder from Justin Lancey at http://veritrope.com/evernote-desktop-folder/
' folder selection function adapted from Ron de Bruin article Select Folder with VBA/Macscript in Mac Office at http://www.rondebruin.nl/mac/mac017.htm
' adapted write-to-file code from CuberChase at http://stackoverflow.com/questions/15183416/vba-save-cell-contents-to-text-file-in-a-specific-location-on-mac

Dim c As Range
enImportFolder = macSelectFolder() 'select the Evernote import folder
If enImportFolder = "" Then Exit Sub
For Each c In ActiveSheet.UsedRange.Columns(1).Cells
    enNote = FreeFile
    i = i + 1
    Open enImportFolder & "Note_" & Format(i, "00000") & ".txt" For Output As #enNote
    Print #enNote, c.Value & vbCrLf & c.Offset(0, 1).Value 'write the note title & text
    Close #enNote
    ' Evernote should pick up the file via Import Folders now
Next

End Sub

Function macSelectFolder()
    Dim folderPath As String
    Dim RootFolder As String
    Dim scriptstr As String

    On Error Resume Next
    RootFolder = MacScript("return (path to desktop folder) as String")
    'Or use RootFolder = "Macintosh HD:Users:YourUserName:Desktop:TestMap:"
    'Note : for a fixed path use : as seperator in 2011 and 2016

    If Val(Application.Version) < 15 Then
        scriptstr = "(choose folder with prompt ""Select the folder""" & _
            " default location alias """ & RootFolder & """) as string"
    Else
        scriptstr = "return posix path of (choose folder with prompt ""Select the folder""" & _
            " default location alias """ & RootFolder & """) as string"
    End If

    folderPath = MacScript(scriptstr)
    On Error GoTo 0
    macSelectFolder = folderPath
End Function

 

Link to comment
  • Level 5*
2 minutes ago, terencino said:

Well I had a first pass at a version for Mac

The OP is actually using Windows so you don't really have to worry about the Mac platform

I actually wrote an import script that works directly from the .csv file and creates Notes

 

Link to comment
On 2/9/2017 at 5:57 PM, terencino said:

Great, can you let me know the entries you made for the variables below?

ENLocation
ENNotebook 
ENNoteFileLocation

 

Not necessary.  Works perfectly.  The trick is run the macro with EN closed.  Spent waaaayy too much time playing with the code before I tried the easy step.  One question.  3 of my notebooks sort notes by date created.  When imported from csv, the "created date" is apparently stripped.  Any way to bring the date along with the Subject/Body?

And thanks GREATLY for the code.  Now I can finally delete Outlook without using hours of my time.

Link to comment

I'm glad it works! Although it adds the notes to Evernote while I'm watching on my PC, maybe it is some difference in the versions...

One of the ENScript options is creation date as shown below, so it is possible to add that to your Evernote note.

/c date - Specifies the note creation date and time using the format "YYYY/MM/DD hh:mm:ss". If omitted, the current time is used.

While the CSV export from Outlook doesn't include the creation date, it is available via Outlook VBA. I've already ported the Excel macro to Outlook and it runs perfectly (for me anyway) on the notes in the default Outlook Notes folder, bringing in the creation date to the Evernote note as well.

Alternatively, if you wanted to stay with the Excel/CSV approach, you could create a new CSV file with the creation date, by connecting to Outlook using Access, import the notes to a table in Access, and exporting that to an Excel or CSV file. 

Link to comment

Let's take these one at a time.

>> /c date - Specifies the note creation date and time using the format "YYYY/MM/DD hh:mm:ss". If omitted, the current time is used. ///  This specifies WHICH creation date?  The creation date attached to the note in Outlook?  It's apparently not the import date (current time).  Where does it get "date?"

While the CSV export from Outlook doesn't include the creation date, it is available via Outlook VBA. I've already ported the Excel macro to Outlook and it runs perfectly (for me anyway) on the notes in the default Outlook Notes folder, bringing in the creation date to the Evernote note as well. ///  Default Notes folder?  Wasn't aware there was such a thing.  I'm guessing I can select which folder is default, right? 

Alternatively, if you wanted to stay with the Excel/CSV approach, you could create a new CSV file with the creation date, by connecting to Outlook using Access, import the notes to a table in Access, and exporting that to an Excel or CSV file.   ///  Don't have Access.

 

Link to comment

Your Outlook Notes have many properties, one of them is CreationTime (ie the date and time the Outlook Note was created). The VBA script converts it to text in the format required, then passes it to ENScript as /c date. This adds the original creation date of the note to the new note in Evernote.

The default folders are those associated with the default Outlook account in Account Settings. I recall you have the notes set up in a number of PST files? If so, just add them all to your account and select Set as Default in turn and run the macro for each PST. ie go to Outlook > Tools menu > Account Settings > Account Settings > Data Files tab and add them all there, select the first PST the Notes in which you want to convert to Evernote, then select Set as Default. Then OK through all of the dialogs, restart Outlook, then run the macro. 

The Access option was a fallback, so looks like that's out and the Outlook VBA macro is the way to go. Here is the code below, just open Outlook Alt+F11 to open VB Editor, right-click on VBAProject.OTM in the left panel and Insert a new module. Then paste this code, which as you can see is very much like the Excel macro except for the addition of some Outlook lines. Just replace variables enLocation, enNotebook, enNoteFileLocation with what you were using before, set up your first PST as described above, test it out and let me know how you go!

' Windows API function declarations.
Private Declare Function OpenProcess Lib "kernel32.dll" (ByVal dwDesiredAccessas As Long, ByVal bInheritHandle As Long, ByVal dwProcId As Long) As Long
Private Declare Function CloseHandle Lib "kernel32.dll" (ByVal hObject As Long) As Long
Private Declare Function WaitForSingleObject Lib "kernel32.dll" (ByVal hHandle As Long, ByVal dwMilliseconds As Long) As Long
Private Declare Function GetExitCodeProcess Lib "kernel32.dll" (ByVal hProcess As Long, ByRef lpExitCodeOut As Long) As Integer

' Synchronously executes the specified command and returns its exit code.
' Waits indefinitely for the command to finish, unless you pass a
' timeout value in seconds for `timeoutInSecs`.
' SyncShell developed by mklement0 from http://stackoverflow.com/questions/8902022/wait-for-shell-to-finish-then-format-cells-synchronously-execute-a-command
Private Function SyncShell(ByVal cmd As String, _
                           Optional ByVal windowStyle As VbAppWinStyle = vbMinimizedFocus, _
                           Optional ByVal timeoutInSecs As Double = -1) As Long

    Dim pid As Long ' PID (process ID) as returned by Shell().
    Dim h As Long ' Process handle
    Dim sts As Long ' WinAPI return value
    Dim timeoutMs As Long ' WINAPI timeout value
    Dim exitCode As Long

    ' Invoke the command (invariably asynchronously) and store the PID returned.
    ' Note that this invocation may raise an error.
    pid = Shell(cmd, windowStyle)

    ' Translate the PIP into a process *handle* with the
    ' SYNCHRONIZE and PROCESS_QUERY_LIMITED_INFORMATION access rights,
    ' so we can wait for the process to terminate and query its exit code.
    ' &H100000 == SYNCHRONIZE, &H1000 == PROCESS_QUERY_LIMITED_INFORMATION
    h = OpenProcess(&H100000 Or &H1000, 0, pid)
    If h = 0 Then
        Err.Raise vbObjectError + 1024, , _
          "Failed to obtain process handle for process with ID " & pid & "."
    End If

    ' Now wait for the process to terminate.
    If timeoutInSecs = -1 Then
        timeoutMs = &HFFFF ' INFINITE
    Else
        timeoutMs = timeoutInSecs * 1000
    End If
    sts = WaitForSingleObject(h, timeoutMs)
    If sts <> 0 Then
        Err.Raise vbObjectError + 1025, , _
         "Waiting for process with ID " & pid & _
         " to terminate timed out, or an unexpected error occurred."
    End If

    ' Obtain the process's exit code.
    sts = GetExitCodeProcess(h, exitCode) ' Return value is a BOOL: 1 for true, 0 for false
    If sts <> 1 Then
        Err.Raise vbObjectError + 1026, , _
          "Failed to obtain exit code for process ID " & pid & "."
    End If

    CloseHandle h

    ' Return the exit code.
    SyncShell = exitCode

End Function

Public Sub ConvertToEvernote()
' Response to required_username on Evernote Forums
' https://discussion.evernote.com/topic/103802-importing-notebooks-through-pdf/#comment-453125
' ENScript.exe commands and options from http://dev.evernote.com/doc/articles/enscript.php#enscript

Dim retval, c As Range, fso As Object, enNoteFile As Object
Dim outNotesFolder As MAPIFolder, outNote As NoteItem
Set outNotesFolder = Application.GetNamespace("MAPI").GetDefaultFolder(FolderType:=olFolderNotes)
Set fso = CreateObject("Scripting.FileSystemObject")

enLocation = "C:\Users\tharvey\AppData\Local\Apps\Evernote\Evernote\ENScript.exe"
enNotebook = "Quotes"
enNoteFileLocation = "C:\EN\Note.txt"
 
For Each outNote In outNotesFolder.Items
    enTitle = outNote.Subject 'title of the new note
    enNoteText = outNote.Body 'text of the new note
    enNoteCreated = Replace(Format(outNote.CreationTime, "yyyy/mm/dd hh:mm:ss"), "-", "/")
    Set enNoteFile = fso.CreateTextFile(enNoteFileLocation, True, True) 'create the note text file
    enNoteFile.Write enNoteText 'write the note text
    enNoteFile.Close
    ' Debug.Print enTitle & vbCrLf & enNoteText & vbCrLf & enNoteCreated
    ' Use ENScript.exe to create note
    exitCode = SyncShell(enLocation & " createNote /n " & Chr(34) & enNotebook & Chr(34) & " /i " & Chr(34) & enTitle & Chr(34) & " /s " & enNoteFileLocation & " /c " & Chr(34) & enNoteCreated & Chr(34), vbHide)
Next
 
Set outNotesFolder = Nothing
Set fso = Nothing
Set enNoteFile = Nothing

End Sub

 

Link to comment

Thanks for the response.  Script runs without error.  Also runs with no entries in EN.  Here is what I've found with the following two lines.

Set outNotesFolder = Application.GetNamespace("MAPI").GetDefaultFolder(FolderType:=olFolderNotes)
MsgBox outNotesFolder  ' returns "Notes" as folder name.  I have no Database named "Notes" and I can find no folder anywhere in the PST named "Notes" - in default pst or the 2nd pst.

Link to comment

It seems to have found the Notes folder though, maybe there are no notes in there? Did you move your Outlook Notes somewhere else? If you select Folders in the Navigation view, it should show all of the folders, including the Notes folder as shown in the pictures below?

doc-show-hide-folder-list-3.png

doc-show-hide-folder-list-4.png

Link to comment

Tools>Accounts>Data Files shows 2 psts.  Cked every folder/subfolder in both.  No "Notes" anywhere.  I run with 5 email accounts in the default pst, FWIW.

Seems I saw "Notes" at one time but haven't seen it for, well, I don't remember.  Does it matter?  I'm trying to transfer database folders, or are "Notes" folders and DataBase folders the same thing?   You have me so close to success I can almost touch it.

 

Link to comment

Earlier in this thread, you said:

Quote

 

I have 8000+ notes in 7 Outlook folders.  95% consist of a subject and body.  The remainder are emails.  

 

 

To help me visualise your setup, can you send me a picture of those notes, and the folders they are in? Have I incorrectly assumed the notes are Outlook Notes, in standard Outlook Notes folders? The location, format and type is important for coding...

Link to comment

Don't know how to get a picture.  Currently, in the Navigation Pane, I have two psts, I'll call (uncreatively) Default and Two.  Neither has a Notes folder or subfolder.  PSTs are as follows;

"Default" folders include Databases, Deleted Items, Drafts, Inbox, . . . Folders under Databases are Apps, Business, EMail, Genealogy, Quotes, Statistics, Vehicles.  Email contains (duh) emails.  Quotes consists of Subject/Body.  The remaining folders consist of Creation Date/Subject/Body.

The "Two" folder contains no Notes and No Databases.

Apologies for incorrect lingo.  I assumed (incorrectly) I stored 'notes' in my DB folders. Should have been more clear.

Link to comment

OK. Thanks for your patience, and sorry for all the questions but I need to determine what type of notes you have eg tasks, emails, notes, journal, posts etc. The macro is looking for a Note-type item in the default Notes folder, but it appears that it needs to be looking for something else in your Database folders. Can you recall how you created a new note in any of your Database folders?

It could be that the Database folder is a user-created folder, as are the sub-folders. When folders are created, Outlook asks what type of items will be in that folder (as shown in attached picture), and only allows that type of item. If I can find that out, I'll modify a couple of lines of the macro, and we'll go from there.

To get a picture, you might use Windows Snipping Tool provided with Windows 7 onwards. Type Snip in the Windows search box and it should show up. It will help you select an area and convert that into a JPG or other format that you can upload here.

 

Outlook_New_Folder.PNG

Link to comment

Best I can tell, all DB folders are of "Mail and Post Items."  Individual folders use the following columns

Android Apps - drag and drop from Inbox - Date received, Sender, Subject, Body

Business - Date Created, Subject, Body (create individual notes as needed by custom form)

Email - same as Android Apps

Genealogy - same as Android Apps

Quotes - Subject, Body (create individual notes as needed by custom form)

Statistics - Subject, Body (create individual notes as needed by custom form)

Vehicles - Date Created, Subject, Body (create individual notes as needed by custom form)

Capture.PNG

Link to comment

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...