Run Python script via VBA and give name and path of active workbook?

Question

EDIT: This is the same question but I rewrote it, so it is more legible.

I already tried this post: How to call python script on excel vba?

And this post: Run and execute a python script from VBA

And this post: How can I call python program from VBA?

But none of the answers works for me and I have no idea what I'm doing wrong. Problem 1: I want to run a pythonscript from VBA excel. excel file doesn't have a home place (can be on any desktop). the code I (want to) use:

 Dim Ret_Val
 Ret_Val = Shell("C:\python27\python.exe \\10.31.13.22\SharedDocs\3 - Technical\13 - Reports & Templates\13 - Description\DescriptionToDatabase.py")

The pythonfile has always the same path on the server. I cannot see wat is wrong here? All I get is a black python screen.

In the python file I call the workbook and the correct sheet:

book = xlrd.open_workbook("//10.31.13.22/SharedDocs/3 - Technical/1 - Projects/0 - Internal/RPA 138 - Engineering software/testchipdescription/upload to database/testchipdescription-template-10-11.xltm")
sheet = book.sheet_by_name("Database")

At the moment the excel workbook path is hardcoded in python. This will bring me back to problem 2: Can I pass the name and path of the excel workbook somehow to my pythonscript?

EDIT:

I tried the shell()code in command prompt. The same as in VBA:

"C:\python27\python.exe \\10.31.13.22\SharedDocs\3 - Technical\13 - Reports & Templates\13 - Description\DescriptionToDatabase.py"

It doesn't work. 'The system cannot find the path specified'.

I tried this one:

C:\python27\python.exe "\\10.31.13.22\SharedDocs\3 - Technical\13 - Reports & Templates\13 - Description\DescriptionToDatabase.py"

And it works! So the cmd needs "" to handle the spaces in the path. But I can't add them in VBA because I cannot place 2 "" otherwise it errors.

Answer 1

Yes, I found a solution for problem 1:

Dim excelToPython As String
excelToPython = """C:\python27\python.exe"" ""\\10.31.13.22\SharedDocs\3 - Technical\13 - Reports & Templates\13 - Description\DescriptionToDatabase.py"""
Debug.Print excelToPython
Call Shell(excelToPython)

Thanks to 'Allan Browne' (https://bytes.com/topic/access/answers/520558-problem-shell-space-file-name)

EDIT:

Finally I have found a workaround for problem 2. I still don't have the real solution to give the name and the path of the active workbook to my python script with the shell command.

But I write the path and name of my active workbook in a txtfile in the same folder as my python script. And then I get this information with my pythonscript et voila.. It works! Well it does what I want, but it's not a clean solution. If someone knows the correct solution, please feel free to share it :o)

my workaround solution:

code in vba-excel:

'via pythonscript'

Dim excelToPython As String
Dim myFileTxt As String
Dim fileTxtPath As String

'first give the name and path of active workbook to a txtfile in the same folder as ThemeColor pythonscript'

fileTxtPath = "\\10.31.13.22\SharedDocs\3 - Technical\13 - Reports & Templates\13 - Description\actWBdb.txt"

myFile = FreeFile

Open fileTxtPath For Output As myFile

nameWB = ActiveWorkbook.name
pathWB = ActiveWorkbook.path

Print #myFile, nameWB
Print #myFile, pathWB

Close myFile

'run the python file'
 excelToPython = """C:\python27\python.exe"" ""\\10.31.13.22\SharedDocs\3 - Technical\13 - Reports & Templates\13 - Description\DescriptionToDatabase.py"""

Call Shell(excelToPython)

code in python:

filepath ='//10.31.13.22/SharedDocs/3 - Technical/13 - Reports & Templates/13 - Description/actWBdb.txt'
lines =  open(filepath).read().splitlines()
nameWorkbook = lines[0]
pathWorkbook = lines[1]

book = xlrd.open_workbook(pathWorkbook + '/' + nameWorkbook)
sheet = book.sheet_by_name("Database")

Answer 2

I was wondering if it is possible to give the name and the path of the active excel workbook to a python script I call from this active workbook?

Yes. You can pass argument(s) to SHELL function from VBA, and then you would need to modify your python script to accept the argument(s) and do something with it/them.

Python Read from Stdin with Arguments

Python read from command line arguments or stdin