Question: Linking an "cell" in Excel to a note in Evernote

[Ktiger 2]

I've seen this asked a few times and couldn't find a clear answer.

 

I want a cell in Excel to link back to a note in Evernote.  They would be on the same computer. Both the Excel file and Evernote are on the same computer.  I am using Windows 7 Professional.

 

In other words, in an Excel Spreadsheet, I want there to be a link located in a cell that when I click on it, it takes me to a specific note in Evernote.

 

Is this possible?  If so, how so?

 

Thank you in advance!

[CalS 5,280]

Try getting the note link and then paste keep source formatting.  That puts a link in the spreadsheet for me.

[JMichaelTX 4,117]

In other words, in an Excel Spreadsheet, I want there to be a link located in a cell that when I click on it, it takes me to a specific note in Evernote.

 

Yes, this is possible.  Done it many times.

Create a hyperlink in Excel just like you would for a web page:

1. Select the Note
2. Copy the Note Link
   1. In EN Win, right-click, choose "Copy Note Link"

      (Not sure if there is an option for "Classic Link".  Cal?)

   2. In EN Mac, right-click, hold down "OPTION" key, choose "Copy Classic Note Link"

      In EN Mac, right-click, choose "Copy Note Link"  ^[1]

3. In Excel:
   1. Right-click on cell with text, and choose "Hyperlink..."

Tested using Excel 2011 Mac, but I'm pretty sure Excel Windows works the same, or similar, way.

 

---

 

EDIT:  Wed, Sep 2, 2015 at 12:05 PM  US CT

 

[1].  Looks like since I tested and posted this, Excel has been changed, and will no longer accept the Evernote Classic Note Link ("evernote:///view/. . .").  So I have updated the above steps.

[Ktiger 2]

Thank you very much...works great!

[CalS 5,280]

 

In other words, in an Excel Spreadsheet, I want there to be a link located in a cell that when I click on it, it takes me to a specific note in Evernote.

 

Yes, this is possible.  Done it many times.

Create a hyperlink in Excel just like you would for a web page:

1. Select the Note
2. Copy the Note Link
   1. In EN Win, right-click, choose "Copy Note Link"

      (Not sure if there is an option for "Classic Link".  Cal?)

   2. In EN Mac, right-click, hold down "OPTION" key, choose "Copy Classic Note Link"
3. In Excel:
   1. Right-click on cell with text, and choose "Hyperlink..."

Tested using Excel 2011 Mac, but I'm pretty sure Excel Windows works the same, or similar, way.

 

Only need to do a "normal" copy note link from the context menu to make it work.

[CalS 5,280]

Thank you very much...works great!

You are welcome.

[wrishel 3]

The solution described here works well, but when you click on the link in Excel you go to the browser-based Evernote client.

 

I am wondering if there is a way to create a link in an Excel spreadsheet that opens up that note in the Local Evernote client on my Mac?

 

I am using Evernote 6.1 on Yosemite

 

 

[JMichaelTX 4,117]

The solution described here works well, but when you click on the link in Excel you go to the browser-based Evernote client.

I am wondering if there is a way to create a link in an Excel spreadsheet that opens up that note in the Local Evernote client on my Mac?

 

See Step# 2.2 in my post above.

[wrishel 3]

 

The solution described here works well, but when you click on the link in Excel you go to the browser-based Evernote client.

I am wondering if there is a way to create a link in an Excel spreadsheet that opens up that note in the Local Evernote client on my Mac?

 

See Step# 2.2 in my post above.

 

Thanks. My bad for missing the option key. However I apparently am doing something else wrong as well.

 

I follow your instructions (including the option key) I get an entry on the clipboard that looks like: evernote:///view/ (with lots of slashes, dashes and hex digits following)

 

I then paste it into a cell in Excel and use command K to turn it into a hyperlink. It then looks like file://localhost/evernote/:::view: (with the same gobbledygook on the end except that colons replace forward slashes). The entry turns blue and the cursor changes to a finger when it hovers over the cell.

 

I click on the cell and get an alert box from Excel saying cannot open the specified file.

 

Does Evernote need to be the HTTP server for localhost? It is quite possible that somewhere along the line a different agent began serving localhost but I am way beyond my depth here.

 

Thanks in advance.

[JMichaelTX 4,117]

I then paste it into a cell in Excel and use command K to turn it into a hyperlink. It then looks like file://localhost/evernote/:::view: (with the same gobbledygook on the end except that colons replace forward slashes). The entry turns blue and the cursor changes to a finger when it hovers over the cell.

 

I click on the cell and get an alert box from Excel saying cannot open the specified file.

 

My apologies.  Looks like Excel will no longer accept the Evernote internal URL link.

I don't know if my memory is faulty, or Excel has changed, but in any event it doesn't work now.

 

I tried several different ways of pasting the link, and none worked.

 

To make matters worse, MS Word will also not accept the EN Internal link.

So, I'm really suspicious now that MIcrosoft has made some changes across the board in MS Office 2011.

 

I will update my above post to avoid any further confusion.

[wrishel 3]

JMichael -- thanks. No joy, but you saved me a lot of time to find out.

[wrishel 3]

 

I then paste it into a cell in Excel and use command K to turn it into a hyperlink. It then looks like file://localhost/evernote/:::view: (with the same gobbledygook on the end except that colons replace forward slashes). The entry turns blue and the cursor changes to a finger when it hovers over the cell.

 

I click on the cell and get an alert box from Excel saying cannot open the specified file.

 

My apologies.  Looks like Excel will no longer accept the Evernote internal URL link.

I don't know if my memory is faulty, or Excel has changed, but in any event it doesn't work now.

 

I tried several different ways of pasting the link, and none worked.

 

To make matters worse, MS Word will also not accept the EN Internal link.

So, I'm really suspicious now that MIcrosoft has made some changes across the board in MS Office 2011.

 

I will update my above post to avoid any further confusion.

 

Upon thinking about this, I really wonder if the issue is with Evernote or something about Yosemite. Links to arbitrary URIs still work in Excel and Localhost should be just another symbolic IP address as far as Excel is concerned.

[wrishel 3]

jmichael on looking into this I find that the link that Evernote creates works just fine in a browser, it just doesn't work when you click it in Excel. Looks like your assessment is correct.

[JMichaelTX 4,117]

@wrishel:

 

Thanks for the testing.  I'm glad it is not Yosemite -- would be impossible to get a change.  :-(

 

Knowing what we now know, I see two workarounds:

1. Create the link in Excel, malformed as it is.
   OR
2. Paste the link as text in a cell next to the original target cell

Then use either AppleScript or Keyboard Maestro to pull the EN link from the cell, and open the URL, which would be the EN Mac app.  I already have a KM macro that will almost work with #2.  I'll do some testing and post back here.

[JMichaelTX 4,117]

With very limited testing, this seems to work using a Keyboard Maestro macro, using method #2 above, then selecting the cell with the EN link, and running the macro:

CopyIf All Conditions Met   The clipboard text contains ‘evernote://’   Execute the Following Actions:   Open URL: %CurrentClipboard%

[yardbird 2]

Hi there,

Is there a way to use AppleScript or Keyboard Maestro to copy the link from evernote as a full link (not sure how to describe it) and then paste into Excel?

Something like what's below, except what's below won't work because it will paste the actual html text not the formatted link with title?

tell application "Evernote"

    set myNote to selection

    set urlTitle to title of myNote as string

    set urlLink to note link of myNote as string

end tell

 

set strHTML to "<a href=\"" & urlLink & "\" target=\"_blank\">" & urlTitle & "</a>"

 

set clipboard to strHTML

 

 

[JMichaelTX 4,117]

27 minutes ago, yardbird said:

Is there a way to use AppleScript or Keyboard Maestro to copy the link from evernote as a full link

The below script will create a Rich Text Hyperlink using the Note Title and classic link, and copy to clipboard.  You can then paste wherever you'd like:

Evernote - Copy EN Mac Note Link (Classic)

[yardbird 2]

12 minutes ago, JMichaelTX said:

The below script will create a Rich Text Hyperlink using the Note Title and classic link, and copy to clipboard.  You can then paste wherever you'd like:

Evernote - Copy EN Mac Note Link (Classic)

Wow thanks so much for the quick answer. I've tried to paste and save this in applescript and get a syntax error at "entities". I'm assuming I must need to install some other software? Satimage? What is that exactly?

       ## --- ENCODE THE NOTE TITLE --- ##    

       --            (requires the Satimage.osax)

       

       set strNoteTitle to encode entities strNewTitle

       log strNoteTitle

[JMichaelTX 4,117]

57 minutes ago, yardbird said:

I'm assuming I must need to install some other software? Satimage? What is that exactly?

Yes, as it says there, and in the Script header comments, the Satimage.osax scripting addition is required.
Satimage.osax is a very powerful and useful extension to AppleScript.  It is well-known to many AppleScript developers, and highly recommended.

Satimage.osax -- Free D/L at
http://www.satimage.fr/software/en/downloads/downloads_companion_osaxen.html

 

[yardbird 2]

Thanks again. Making good progress. I've dowloaded and installed the satimage.osax. A few things though:

1. the script grabs (as it rightly claims) the classic note link. How would I modify it to get the web link? (I'm sharing the spreadsheet with another user who has access to shared evernote files).

2. the script doesn't appear to run fully? I don't get the messages associated with the code:

        set strMsg to strNewTitle

        set strMTitle to "Evernote Internal Link Copied to Clipboard for"

        display notification strMsg with title strMTitle sound name "Hero.aiff"

 

I tried setting the bolDebug to true to see what happens.
The code seems to get to "set lstrCMD..." line below and stops after that. I see the "ENTER copyHTMLasRTFtoClipboard"message but don't see messages after that.

 

 

on copyHTMLasRTFtoClipboard(pstrHTML)

    

    if bolDebug then display dialog "ENTER copyHTMLasRTFtoClipboard"

    

    -- REWRITTEN AS RTF AND COPIED TO THE CLIPBOARD

    set lstrCMD to "echo " & quoted form of pstrHTML & " | textutil -format html -convert rtf -stdin -stdout | pbcopy -Prefer rtf"

    do shell script lstrCMD

    

    if bolDebug then

        display notification pstrHTML with title "Copy RTF to Clipboard"

    end if

    

end copyHTMLasRTFtoClipboard

 

3. The script works if I'm pasting into Microsoft Word (for example) but for some reason won't paste into Microsoft Excel. It gives a "Microsoft Excel cannot paste the data" error? Interestingly if I paste it into word and then copy that and paste it into excel it works fine, which seems a bit strange. Is this related to the script not completely running? or perhaps an excel thing?

Ideas? 

[JMichaelTX 4,117]

@yardbird:  Due to recent changes in security, neither Excel nor Word will accept paste of hyperlinks for non-web protocols.

So, here's a rough draft script that will create a hyperlink in Excel to an Evernote Note.  I've tested it some, but I'll leave it up to you to do more testing and add any additional UI to choose between internal (classic) link and web link for the EN note.

(*
===============================================================================
	[EN Excel] How to Create Hyperlink in Excel to Evernote Note
===============================================================================

VER: 	1.0		LAST UPDATE:   2016-01-11

PURPOSE:
	• Create Hyperlink in Excel to Evernote Note

AUTHOR:		JMichaelTX
					Find any bugs/issues or have suggestions for improvement?
					Contact me via PM or at blog.jmichaeltx.com/contact/

REQUIRED:
	1.	Mac OS X Yosemite 10.10.5+
	2.	Mac Applications
				• Evernote
				• Microsoft Excel
===============================================================================
   
*)
###–––––––––––––––––––––––––––––––––––––––###
###	BEFORE YOU RUN THIS SCRIPT
###		1. Select the Note in Evernote 
###		2. Select the Cell in Excel
###–––––––––––––––––––––––––––––––––––––––###


tell application "Evernote"
	
	activate
	
	--- GET THE INTERNAL CLASS LINK OF THE SELECTED NOTE ---
	
	set lstSel to selection
	set oNote to item 1 of lstSel
	set strURL to note link of oNote
	set strTitle to title of oNote
	log strURL
	
end tell -- Evernote

delay 1 -- make sure Evernote app is frontmost app/window

--- GET THE WEB LINK OF THE SELECTED NOTE ---
---    Can't Get Note Web Link Via Applescript, So Use Ui Events
---    Same as keyboard shortcut ⌘⌃⌥C ---

tell application "System Events" to keystroke "c" using {command down, control down, option down}

set strWebURL to the clipboard as text
log strWebURL

tell application "Microsoft Excel"
	activate
	delay 1
	
	set oCell to active cell
	
	tell worksheet active sheet of active workbook
		set value of oCell to strTitle
		
		--- CREATE HYPERLINK TO EN NOTE ---
		--    Use strWebURL for the Web URL
		--    Use strURL    for the internal classis URL
		make new hyperlink of oCell with properties {address:strWebURL}
	end tell
	
end tell -- Excel

 

[yardbird 2]

Thanks for your help JMichaelTX. Really appreciate it. I'd just started to go down this route but wasn't quite sure what I was doing, this should get me there.