Automatically changing Python directory after running python script from excel using VBA

Question

Thanks for looking at my problem. I have a python script which is writing a new csv file in my current directory. I have make it python.exe using pyinstaller. What I am doing is, running this exe file using VBA form excel on a button click. Problem is after running exe using VBA my csv is saving in default documents folder instead of folder where my python.exe is. But If I run the python.exe directly without VBA then it's saving csv in my current directory. So how can I save csv in current directory after running python.exe using VBA

Here is my python code which creating new csv file:

csvfile = open("Mycsv" + '.csv', 'w')
cr = csv.writer(csvfile, dialect='excel')

I have also tried using: csvfile = open(os.getcwd() + "\Mycsv" + '.csv', 'w') But didn't worked.

Here is my VBA code:

    Dim wsh As Object
    Set wsh = VBA.CreateObject("WScript.Shell")
    Dim waitOnReturn As Boolean: waitOnReturn = True
    Dim windowStyle As Integer: windowStyle = 1
    Dim errorCode As Integer
    wsh.Run Chr(34) & ThisWorkbook.path & "\python.exe" & Chr(34), windowStyle, waitOnReturn

I suspect you need to pass the working directory path from VBA to the python.exe as an argument. The python program has no way to know otherwise which directory your excel file was. It is an independent process. — kosnik
– kosnik, Commented Jul 4, 2018 at 12:44
you can try to change the working directory of WScript with wsh.CurrentDirectory = ... before the wsh.Run ... — Vincent G
– Vincent G, Commented Jul 4, 2018 at 12:54

PythonParka · Accepted Answer · 2018-07-04 12:50:21Z

0

Before saving the file you can use os.chdir(some_path_to_directory) to set the current working directory, and then save in this directory.

answered Jul 4, 2018 at 12:50

PythonParka

1543 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Atik Over a year ago

Thank you guys for all your help. None other than @Vincent's Answer worked perfectly. So I have used wsh.CurrentDirectory = Thisworkbook.path before wsh.Run... Thanks a lot...!

score 0 · Accepted Answer · 2018-07-04 20:45:53Z

0

try specifying the path for your csv file:

import os.path
save_path = 'C:/example/'
csvName = os.path.join(save_path, "Mycsv"+".csv") 
csvfile = open(csvName, "w")

edited Jul 4, 2018 at 20:45

answered Jul 4, 2018 at 12:58

user8608712

Collectives™ on Stack Overflow

Automatically changing Python directory after running python script from excel using VBA

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related