CODEWITHSUNDEEP
pythonvbams-access
Peter Chappy
Peter Chappy

Reputation: 1179

Running a Python script from Access VBA

I'm having trouble finding many resources, but I'm trying to get vba to run a python script 

Const pyScript = "C:\Test\Weekend_Exceptions\Weekend_Exception.py"
Dim dblRetVal As Double
dblRetVal = Shell("C:\Python34\python.exe " & pyScript)

I know my python script works and should output a file, but its not. Additionally the vba is not tirggering a debug flag so I'm not sure where I am wrong. Any advice would be appreciated.

Upvotes: 0

Views: 10847

Answers (2)

Cairo Mendes
Cairo Mendes

Reputation: 1

Here is how I did:

  1. I created a File1.bat to open the File2.py. The code in File1.bat is:
@echo off
@h:
@cd H:\Path\Cronus\Rangers
@C:\Python3\python.exe File2.py
@echo on

Note that File2.py is inside H:\Path\Cronus\Rangers folder. That's why we need to open it before.

  1. I created a function in VBA to open a .bat file:
Option Compare Database

Function MacroPythonSARH()
On Error GoTo MacroPythonSARH_Err

Call Shell("H:\Path\Cronus\Rangers\File1.bat", 1)

MacroPythonSARH_Exit:
    Exit Function

MacroPythonSARH_Err:
    MsgBox Error$
    Resume MacroPythonSARH_Exit

End Function

Upvotes: 0

Victor
Victor

Reputation: 372

You don't give too much details so i'll make some assumptions

Probably your python script read some local file this will cause your script to raise a FileNotFoundError and exit

To make the test copy the entire arg string to Shell, in your case "C:\Python34\python.exe C:\Test\Weekend_Exceptions\Weekend_Exception.py", open a cmd with Win+r , paste and run, not being in the right directory should raise the same error.

If this is the problem, make a makestuff.bat file with the code

@echo off
@cd C:\Test\Weekend_Exceptions\
@C:\Python34\python.exe Weekend_Exception.py
@echo on

Then call the bat from Shell("C:\Place\of\your\bat\makestuff.bat")

Return with more details to we work on a solution

Upvotes: 4

Related Questions