Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Is there a way to call a Python code in Excel-VBA?

Tags:

python

excel

vba

I have an Excel file (Main.xlsm) containing macros. I have a Python file (python.py) to generate a subsidiary Excel file (sub.xlsx) which I would further call in the macros of Main.xlsm-file. This sub.xlsx-file which is generated by the run of python.py is saved in the same working directory.

Now I want to make this python.py to be executed during the run of the Main.xlsm macros and then use this xlsx-file. I basically want to reduce the step of executing python.py externally. Is there a command for that? I am new to VBA.

like image 463
Pratheek P Manangi Avatar asked Jul 31 '17 07:07

Pratheek P Manangi


People also ask

Can you call Python from VBA?

A python library called xlwings allows you to call Python scripts through VBA and pass data between the two.


3 Answers

The simplest way is to run the python interpreter with the Shell command

Shell ("python.exe " & yourScript & " " & arguments)
like image 147
Uri Goren Avatar answered Oct 03 '22 15:10

Uri Goren


Yes, there is. My preferred way of doing this is through xlwings (https://www.xlwings.org/), but there are several other options as well. XlWings is great because it's free, open source and easy to use, with great documentation. There are some feature limitations though, so you'd have to check if it fits your needs.

like image 20
Gabor Avatar answered Oct 03 '22 15:10

Gabor


There are multiple ways tu run a python script with VBA depending on whether you need to wait for the end of the execution and know if it went without error.

With Shell, asynchronous with console:

Public Sub RunPython(file As String, ParamArray args())
  Shell "python.exe """ & file & """ " & Join(args, " ")
End Sub

With Shell, synchronous without console:

Public Function RunPython(file As String, ParamArray args())
  Shell "pythonw.exe """ & file & """ " & Join(args, " ")
End Function

With WScript.Shell, synchronous without console and with exit code:

Public Function RunPython(file As String, ParamArray args()) As Long
  Dim obj As Object
  Set obj = CreateObject("WScript.Shell")
  RunPython = obj.Run("pythonw.exe """ & file & """ " & Join(args, " "), 0, True)
End Function
like image 34
Florent B. Avatar answered Oct 03 '22 16:10

Florent B.