CODEWITHSUNDEEP
pythonmacosvbaexcel
Pyderman
Pyderman

Reputation: 16199

How to call Python script from VBA on Excel 2016 for MAC?

When I run the following and pass some parameters to my existing Python script from Excel for Mac 2011 it works.

Nothing happens on Excel for Mac 2016, i.e. no error and no result.

#If Mac Then
    Private Declare Function system Lib "libc.dylib" (ByVal command As String) As Long
    Const python3Dir As String = "/usr/local/bin/python3"
#End If
    
Sub btnScrape_Click()
    ' Uses system() to run external command
    ' system() only returns the exit code. If you want to get the output from the command,
    
    Dim result As String
    Dim command As String
    Dim myDir As String
    
    myDir = getPosixPath(ThisWorkbook.Path)
    
    command = "cd " & myDir & " && " & python3Dir & " myPythonScript.py ######## ####"
    Debug.Print command
    result = system(command)
    Debug.Print result
    ' 0 means OK.
    MsgBox "Check report.txt"
End Sub    
    
Function getPosixPath(macPath As String) As String
    Dim scriptToRun As String
    
    scriptToRun = "tell application ""Finder""" & Chr(13)
    scriptToRun = scriptToRun & "set appUserPath to POSIX path of " & """" & macPath & """" & Chr(13)
    scriptToRun = scriptToRun & "end tell" & Chr(13)
    getPosixPath = MacScript(scriptToRun)
End Function

Python setup:

which -a python3
/Library/Frameworks/Python.framework/Versions/3.5/bin/python3
/usr/local/bin/python3

which -a python
/usr/bin/python

echo $PATH
/Library/Frameworks/Python.framework/Versions/3.5/bin/python3

Is there something to be modified/added for Excel for Mac 2016?

Upvotes: 1

Views: 2655

Answers (2)

hangj
hangj

Reputation: 11

check this
you can call AppleScriptTask to run an AppleScript file which calls Python

Upvotes: 0

fralau
fralau

Reputation: 3829

I am myself looking for an answer on how to get Excel VBA on MacOS to run anything that is not VBA. Basically, how to create a function that will "jailbreak" out of Excel.

But here is how far I got: MacScript was a solution that allowed you, in Office 2011, to use a thin AppleScript wrapper to run anything. In Office 2016, MacScript has been deprecated, because of "sandboxing". Instead, we are supposed to use the AppleScriptTask directive.

The general idea is that you have to create a AppleScript script in ~/Library/Application Scripts/com.microsoft.Excel/, that will launch, somehow, your python program with the correct parameters. I couldn't find much indication on how we are supposed to do that, except some helpful hints by Ron de Bruin. So far, the state of the art seems to be: "just look over there and good luck".

Hence it's not sufficient for us to learn VBA and Python, now we are also supposed to learn AppleScript and the intricacies of MacOS. I am not sure what people at Microsoft have been thinking, when they expected us to go through all the loops of installing an AppleScript file in an obscure directory just to have the privilege of running... a python program. Is it that they just don't want us to do so ("defective by design")? Or it's an obscure side-effect of a Microsoft-Apple trade war? Or Microsoft's "corporate brain" is not aware at all that there is an issue? Whatever the case may be, they are not telling us.

If you need only to run a Python script from you application that will return nothing, you might have a solution from XLWings, which offers a directive RunPython. With a little patience, you can manage to give it variable arguments. And unfortunately, their splendid idea of Excel functions written in Python, will work on Windows but not on MacOS (no wonder).

But if, like me, you definitely want to use a VBA to get a value back from a Python script, it's unfortunately still an ongoing quest in a treacherous swamp.

Upvotes: 1

Related Questions