Excel Mac OS libc.dylib popen returns no data

Question

I am using the Excel vba execShell script from here to pull data from a web server using curl in Excel for Mac. It works very well on my own Mac (and a number of other Macs that I have been testing on).

However, on a friends Mac (same (latest) version of Mac OS and Excel 365) it fails. It seems that on his Mac the execShell returns empty strings.

I simplified the script and only run an echo command and experience the same behavior. This is the test script I am running

Private Declare PtrSafe Function popen Lib "libc.dylib" (ByVal command As String, ByVal mode As String) As LongPtr
Private Declare PtrSafe Function pclose Lib "libc.dylib" (ByVal file As LongPtr) As Long
Private Declare PtrSafe Function fread Lib "libc.dylib" (ByVal outStr As String, ByVal size As LongPtr, ByVal items As LongPtr, ByVal stream As LongPtr) As Long
Private Declare PtrSafe Function feof Lib "libc.dylib" (ByVal file As LongPtr) As LongPtr

Function execShell(ByVal command As String, Optional ByRef exitCode As Long) As String
    Dim file As LongPtr
    file = popen(command, "r")

    If file = 0 Then
        Exit Function
    End If

    While feof(file) = 0
        Dim chunk As String
        Dim read As Long
        chunk = Space(50)
        read = fread(chunk, 1, Len(chunk) - 1, file)
        If read > 0 Then
            chunk = Left$(chunk, read)
            execShell = execShell & chunk
        End If
    Wend

    exitCode = pclose(file)
End Function

Sub test_command()
    Dim errorCode As Long
    result = execShell("echo Value_returned", errorCode)
    msgbox(result & " " & errorCode)
    
End Sub

On my Mac, test_command shows

Value_returned 
0

on his Mac it only shows

result = "" returns True.

Any ideas what may cause this behavior?

Any workaround that does not require updates on my friends OS?

Thanks a lot.

score 0 · Answer 1 · answered Oct 18 '21 at 01:52

The 2nd response to this post from dugsmith worked consistently for me on 64-bit mac and might work for you and your friend as well. Code repeated here with full credit to others!

Private Declare PtrSafe Function web_popen Lib "libc.dylib" Alias "popen" (ByVal command As String, ByVal mode As String) As LongPtr
Private Declare PtrSafe Function web_pclose Lib "libc.dylib" Alias "pclose" (ByVal file As LongPtr) As Long
Private Declare PtrSafe Function web_fread Lib "libc.dylib" Alias "fread" (ByVal outStr As String, ByVal size As LongPtr, ByVal items As LongPtr, ByVal stream As LongPtr) As Long
Private Declare PtrSafe Function web_feof Lib "libc.dylib" Alias "feof" (ByVal file As LongPtr) As LongPtr

Public Function executeInShell(web_Command As String) As String

    Dim web_File As LongPtr
    Dim web_Chunk As String
    Dim web_Read As Long

    On Error GoTo web_Cleanup

    web_File = web_popen(web_Command, "r")

    If web_File = 0 Then
        Exit Function
    End If

    Do While web_feof(web_File) = 0
        web_Chunk = VBA.Space$(50)
        web_Read = web_fread(web_Chunk, 1, Len(web_Chunk) - 1, web_File)
        If web_Read > 0 Then
            web_Chunk = VBA.Left$(web_Chunk, web_Read)
            executeInShell = executeInShell & web_Chunk
        End If
    Loop

web_Cleanup:

    web_pclose (web_File)

End Function


Function getHTTP(sUrl As String, sQuery As String) As String
    Dim sCmd As String
    Dim sResult As String
    Dim lExitCode As Long
    sCmd = "curl --get -d """ & sQuery & """" & " " & sUrl
    sResult = executeInShell(sCmd)
    getHTTP = sResult
End Function

Excel Mac OS libc.dylib popen returns no data

1 Answers1