VBA Shell function in Office 2011 for Mac

by

The Shell() VBA function on Mac appears to require the full path as an HFS-style path (with colons instead of slashes). It also doesn’t appear to accept arguments as it does on Windows (reporting a ‘Path not found’ error if any arguments are added).

The MacScript() VBA function can also be used: MacScript("do shell script ""command"""). This is likely to be the simplest option and what I would suggest doing. The downside is that it has quite a lot of overhead (100-200ms per call).

Another alternative is the system() function from the standard C library:

Private Declare Function system Lib "libc.dylib" (ByVal command As String) As Long

Sub RunSafari()
    Dim result As Long
    result = system("open -a Safari --args http://www.google.com")
    Debug.Print Str(result)
End Sub

See http://pubs.opengroup.org/onlinepubs/009604499/functions/system.html for documentation.

system() only returns the exit code. If you want to get the output from the command, you could use popen().

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

Function execShell(command As String, Optional ByRef exitCode As Long) As String
    Dim file As Long
    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 RunTest()
    Dim result As String
    Dim exitCode As Long
    result = execShell("echo Hello World", exitCode)
    Debug.Print "Result: """ & result & """"
    Debug.Print "Exit Code: " & str(exitCode)
End Sub

Note that several of the Long arguments in the above example are pointers, so will have to be changed if a 64bit version of Mac Word is ever released.

More Related Contents:

  1. Wait for shell command to complete [duplicate]
  2. Dir() function not working in Mac Excel 2011 VBA
  3. How do you run a .exe with parameters using vba’s shell()?
  4. VBA (Excel) Dictionary on Mac?
  5. Create a folder and sub folder in Excel VBA
  6. Excel 2013 VBA Clear All Filters macro
  7. Checking if a worksheet-based checkbox is checked
  8. How do I issue an HTTP GET from Excel VBA for Mac
  9. How can I install/use “Scripting.FileSystemObject” in Excel 2010 for MAC?
  10. The MacScript function is not working well in Office for Mac 2016! Any ideas?
  11. Sequence of numbers based on start and end value of 2 cells [closed]
  12. Function to convert column number to letter?
  13. Better way to find last used row
  14. 2 Dimensional array from range [closed]
  15. Using Google Chrome in Selenium VBA (Installation Steps)
  16. VBA: Convert Text to Number
  17. Iterating through populated rows
  18. Using VBA to detect which decimal sign the computer is using
  19. Referencing Excel Userform Control Name Value From String (VBA)
  20. Loop through each row of a range in Excel
  21. Fill formula down till last row in column
  22. How to create a range from two ranges in VBA?
  23. If string contains word from list, return value adjacent to list value
  24. Refreshing all the pivot tables in my excel workbook with a macro
  25. Countif With Multiple OR Criteria
  26. excel vba freeze pane without select
  27. Converting a string representation of a constant into a constant?
  28. VBA WinHTTP to download file from password proteced https website
  29. Access files with long paths (over 260)
  30. How to highlight selected text within excel

Leave a Comment