How to execute Python script through excel VBA

Question

I am trying to run a Python Script through Excel VBA

I have tried the following code, but nothing happened:

Here is the VBA code:

RetVal = Shell("C:\Program Files\Python37\python.exe C:\Users\kailung\Desktop\kai\VBA\june_Longchen\test\VBA.py")

My python script is just renaming files within a folder (I've tested it in Python IDLE, it is fine):

import os, sys

dir_path = os.path.dirname(os.path.realpath(__file__))

# Open a file
dirs = os.listdir(dir_path)

i=1

# This would print all the files and directories
for file in dirs:
   if file!='VBA.xlsm' and file!='VBA.py' and file!='~$VBA.xlsm':
        print(file) #print all the files
        i=str(i) #change to string for file name
        i=i + '.xls' #add extension
        os.rename(file,i) #rename the file
        size=len(i) 
        i=i[0:size-4] #remove the extension
        i=int(i) #change back to numerical
        i=i+1 #new numerical name

Typically you want quotes around any path with spaces (and it doesn't hurt to always quote paths) `RetVal = Shell("""C:\Program Files\Python37\python.exe"" ""C:\Users\kailung\Desktop\kai\VBA\june_Longchen\test\VBA.py""")` — Tim Williams, Jun 13 '19 at 01:30
Not a Python person but https://www.reddit.com/r/learnpython/comments/8cy9k9/running_via_cmd_yields_no_results_while_via/ looks relevant — Tim Williams, Jun 13 '19 at 04:07

score 0 · Accepted Answer · answered Jun 13 '19 at 01:16

0

Convert your .py file to .exe file using pyinstaller and run the .exe program directly from VBA code.

How to use pyinstaller?

answered Jun 13 '19 at 01:16

Agawane

173
1
8

I've used the following code, but it still gives me nothing, RetVal = Shell("C:\Users\kailung\Desktop\kai\VBA\june_Longchen\test\VBA.exe", 1) – Kai Jun 13 '19 at 02:25
The prompt says it's changing the files in C:\Users\kailung\Documents, which is not the right directory. :C – Kai Jun 13 '19 at 11:06
I found the issue, so basically I had to change my directory in VBA: ChDir "C:\Users\kailung\Desktop\kai\VBA\june_Longchen\test" RetVal = Shell("C:\Users\kailung\Desktop\kai\VBA\june_Longchen\test\VBA.exe", 1) – Kai Jun 13 '19 at 11:15

How to execute Python script through excel VBA

1 Answers1