Selecting a folder from a VBA popup that was run from Python

Question

I am using Python and the win32com.client library to run a VBA macro. The macro requires a directory (which is a substring of the .xlsm path itself). How do I pass this directory/string to the VBA popup?

import os, os.path
import win32com.client

if os.path.exists('C:/test_folder/excel_file.xlsm'):
    xl=win32com.client.Dispatch("Excel.Application")
    xl.Workbooks.Open('C:/test_folder/excel_file.xlsm', ReadOnly=1)
    xl.Application.Run('excel_file.xlsm!Sheet1.Macro1')

After this code is run, the folder explorer window pops up asking for a directory. How would I pass the directory to Excel?

Please show the VBA macro. Do you have ability to change the macro? — Parfait, Mar 17 '20 at 16:47
Unfortunately I can't change the macro. It is an FTP feed from an external vendor that I receive daily, they own the macro. There are many other end users for the Excel file, who I believe perform this step manually. — Ross Shelton, Mar 17 '20 at 18:42

INGl0R1AM0R1 · Answer 1 · 2020-03-17T16:39:14.660

This line of code will help you save the exact directory you want making it non dependent to the python path, and associates it to a variable, after that you can just pass on that string variable onto your vba popup. You can try making it global or just reusing it on the continuation of the code, after that you can just reference so you can put it in excel. I hope this helps.

Sub Selectingadirectory()
Dim fd As FileDialog
Dim ActionClicked As Boolean
Dim SelectedFolderPath As String

Set fd = Application.FileDialog(msoFileDialogFolderPicker)
fd.title = "Chose your directory path"
fd.InitialFileName = Environ("UserProfile") & "\Desktop"
fd.AllowMultiSelect = False

ActionClicked = fd.Show

If ActionClicked Then
    SelectedFolderPath = fd.SelectedItems(1)
Else
    Msgbox "You didnt chose a folder"
    Exit Sub
End If
End sub

The macro is sent from an outside vendor, and I receive the file daily. I don't have the option to change anything within the VBA code itself unfortunately. — Ross Shelton, Mar 17 '20 at 17:07

Selecting a folder from a VBA popup that was run from Python

1 Answers1