Excel VBA Can't Open URL on Windows and a 64-bit Mac

[btorrean]

Please note: This post is a cross-post. I posted this question on Feb. 23rd on Stack Overflow and have not gotten any responses. I am posting here in hopes that someone may have not seen that post and may be able to help me here. Here's the link to the cross-post on Stack Overflow: link.

I have the following code that works perfectly for opening a URL on Windows machines (Excel 2016, 2013, 2010). However, I'm trying to make it also usable on a 64-bit Mac (Excel for Mac v. 16.22, Office 365 install). I have tried a number of iterations for finding the Mac library "libc.dylib", and usually get the "Runtime Error '53'. File not found 'libc.dylib'" error. Once I got the error "Runtime Error '453'. File not found '/usr/lib/libc.dylib'". I have also tried changing the slashes in the file path to colons, as suggested in some other posts that I've researched.

Does anyone know what I'm doing wrong? Here's the code that produced the 453 error:

Option Explicit


Enum W32_Window_State
    Show_Normal = 1
    Show_Minimized = 2
    Show_Maximized = 3
    Show_Min_No_Active = 7
    Show_Default = 10
End Enum


[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=If]#If[/URL]  Mac Then
    [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=If]#If[/URL]  VBA7 Then
        Private Declare PtrSafe Function system Lib "/usr/lib/libc.dylib" (ByVal command As String) As LongPtr
    [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=Else]#Else[/URL] 
        Private Declare Function system Lib "/usr/lib/libc.dylib" (ByVal command As String) As Long
    [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=End]#End[/URL]  If
[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=Else]#Else[/URL] 
    [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=If]#If[/URL]  VBA7 Then
        Private Declare PtrSafe Function ShellExecute Lib "shell32.dll" _
            Alias "ShellExecuteA" (ByVal hwnd As LongPtr, _
            ByVal lpOperation As String, ByVal lpFile As String, _
            ByVal lpParameters As String, ByVal lpDirectory As String, _
            ByVal nShowCmd As Long) As LongPtr
    [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=Else]#Else[/URL] 
        Private Declare Function ShellExecute Lib "shell32.dll" _
            Alias "ShellExecuteA" (ByVal hwnd As Long, _
            ByVal lpOperation As String, ByVal lpFile As String, _
            ByVal lpParameters As String, ByVal lpDirectory As String, _
            ByVal nShowCmd As Long) As Long
    [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=End]#End[/URL]  If
[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=End]#End[/URL]  If


Public Function OpenURL(URL As String, WindowState As W32_Window_State) As Boolean


    'Opens passed URL with default application, or Error Code (<32) upon error


    [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=If]#If[/URL]  VBA7 Then
        Dim lngHWnd As LongPtr
        Dim lngReturn As LongPtr
    [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=Else]#Else[/URL] 
        Dim lngHWnd As Long
        Dim lngReturn As Long
    [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=End]#End[/URL]  If


    [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=If]#If[/URL]  Mac Then
        lngReturn = system("open -a Safari --args " & URL)
    [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=Else]#Else[/URL] 
        lngReturn = ShellExecute(lngHWnd, "open", URL, vbNullString, _
            vbNullString, WindowState)
    [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=End]#End[/URL]  If


    OpenURL = (lngReturn > 32)  ' With Mac, this may return a dummy variable, but we're going to do it anyways.


End Function

Any help would be greatly appreciated!

Brian

Brian J. Torreano

 

[RoryA]

What version of MacOS/OSX?

 

[btorrean]

Rory,

I don't know. I've texted the person testing on their Mac. I will post as soon as I get a reply from them.

Thanks!

I like the "ʅ_(ツ)_ʃ" in your signature line, by the way!

Brian

 

[btorrean]

Rory,

Running "macOS Mojave", Version 10.14.2

Brian

 

[RoryA]

OK, I should be able to test that at home when I have time then.

 

[btorrean]

Thanks much, Rory!

You rock!

Brian

 

[RoryA]

There seem to be some issues with that code on Mojave (I've read several reports of issues with resolving dylib paths like that since El Capitan). Perhaps you could use a simpler Macscript instead, like this:

#If Mac Then
        MacScript "do shell script ""open -a Safari '" & URL & "'"""
        lngReturn = True

Edit: after some more tinkering, I think the issue with the declare function code is down to the sandboxing stopping the code from running.

 

[btorrean]

Rory,

I ended up removing the code in the Mac version of the workbooks to open the hyperlinks. It was causing too many errors. My final resolution was to remove the hyperlinking code and just define the hyperlinks to open in the manner that Mac Excel does by default.

Thanks much for your help anyways! All the best to you!

Brian