Opening a Hyperlinked file in Excel

Questions & Suggestions
1

Hi, I'm very new to KM & don't understand most of the discussions here, but I'm still learning. I'm currently wanting to open a file whose name is pointed to by a VLOOKUP command in MS Excel, but am unsure how to do this. I currently use a Hyperlink to the file, so that when I click on the trigger cell containing the VLOOKUP instruction, the named file opens. I want my macro to move to that trigger cell, read its content, evaluate the Hyperlink and open the file. Any suggestions?

2

Yes. Use Excel VBA Macros, or Excel AppleScript. But I prefer VBA because it is native to Excel, and Excel provides an excellent VBA editor.

One way to quickly get started writing a VBA macro is to record a macro in Excel, going through the manual steps that you want to automate. Then edit the macro to cleanup/fine-tune.

However, opening a non-Excel file from Excel VBA may be a challenge. This is easily done in AppleScript, so, in this case, you may better off using AppleScript for your entire workflow. As an option, Excel VBA can execute an AppleScript, so you could use that method.

I just did a quick 'net search on "excel mac vba open non excel file", but did not find a good example with a quick review. You might have better luck, and it may take searching for other terms.

If you get stuck, post back here and I'll try to help.

3

Thank you for the suggestions; I had thought that VBA was no longer possible in Mac Excel, although it is so long since I've used it that I will need to study it somewhat. The file to be opened is an Excel file, so that should simplify the task.

4

Yes. that greatly simplifies the task.
Here's an Excel Mac 2011 VBA Sub to open an Excel file:


Sub Open_Excel_File(pFilePathStr)
'
' Open_Excel_File Macro
    
    Workbooks.Open pFilePathStr
    Windows("Workbook1").Activate
    
End Sub

As you see, very simple.

After a bit of research and testing, I finally figured out how to open a non-Excel file from Excel VBA:

Sub open_file()
    
    Dim scriptStr As String
    Dim hfsPath As String
    
    hfsPath = "~:Documents:Test File To Open From Excel.txt"
    
    '--- Create AppleScript to Open Non-Excel File ---
    '       (Note: You cannot use POSIX path or POSIX commands)
    '          So, I have allowed for the tilde in a HFS path
    '         to mean the same as in a POSIX path:  Users Home Folder
    
    scriptStr = "set hfsPath to """ & hfsPath & """" & vbNewLine & _
        "if (hfsPath starts with ""~"") then" & vbNewLine & _
        "   set homePath to (path to home folder) as text" & vbNewLine & _
        "   set hfsPath to homePath & (text 3 thru -1 of hfsPath)" & vbNewLine & _
        "end if" & vbNewLine & _
        "tell application ""Finder"" to open file hfsPath" & vbNewLine & _
        "return hfsPath"
        
     Debug.Print scriptStr
       
   '--- Execute AppleScript to Open Non-Excel File ---
   
   hfsPath = MacScript(scriptStr)
    Debug.Print hfsPath

End Sub

If you don't use the tilde with the HFS path, then you only need these two lines in the script:

    scriptStr = "set hfsPath to """ & hfsPath & """" & vbNewLine & _
        "tell application ""Finder"" to open file hfsPath"

Questions?

1 Like