• Announcements

    • gbarry

      Reach out to Evernote support on Twitter @EvernoteHelps   09/20/2016

      We've been leveling up our support across all channels. If you're active on Twitter, try your hand with our @evernotehelps feed. It's helmed by a crack team out of Austin, TX who are here to answer your questions. They're generally active from 7am-4pm CST.  https://twitter.com/evernotehelps  

Idea

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?

Share this post


Link to post

34 replies to this idea

Recommended Posts

  • 0
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

Share this post


Link to post
  • 0
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

 

Share this post


Link to post
  • 0

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.

Share this post


Link to post
  • 0
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

 

Share this post


Link to post
  • 0

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.

Share this post


Link to post
  • 0
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

 

Share this post


Link to post
  • 0
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}

Share this post


Link to post
  • 0

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

  • Like 1

Share this post


Link to post
  • 0

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.

Share this post


Link to post
  • 0

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

  • Like 1

Share this post


Link to post
  • 0

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

Share this post


Link to post
  • 0

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"

 

Share this post


Link to post
  • 0

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

Share this post


Link to post
  • 0
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

  • Like 1

Share this post


Link to post
  • 0

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
 

Share this post


Link to post
  • 0

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

 

Share this post


Link to post
  • 0
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

 

Share this post


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

Share this post


Link to post
  • 0

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. 

Share this post


Link to post
  • 0

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.

 

Share this post


Link to post
  • 0

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

 

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