Python: hide Open File Dialog when running Excel macro

Question

I'm writing a Python script to automate a process that uses Excel macros to format files to input into a different program later on. I'm a bit new to Python and completely new to VBA, but I think I have the steps down.

So far I have this to run the macro itself (with help from other SO posts):

xl = win32com.client.DispatchEx('Excel.Application')
xlpath = os.path.expanduser(xlfile)
wb = xl.Workbooks.Open(Filename=xlpath, ReadOnly=1)
xl.Run("my_macro")

At this point the macro runs and calls Application.GetOpenFilename() to open a dialog for the user to choose the file to be formatted, which is going to be different for each macro.

Basically my user is going to have different initial data to format each time they run my script. At the beginning I want them to choose the files that they need formatted and then I'll save those file paths. Then I want to plug those file paths into the macro from Python instead of opening the dialog in Excel.

Is there a way to do this directly by changing the macro? If not, will I need to rewrite the macro in Python with one of the modules out there for driving Excel?

Community · Accepted Answer · 2017-05-23 10:26:49Z

0

It looks like I can call xl.Run("my_macro") with parameters so I just have to figure out how the macro reads in those parameters so once I pass in the paths it can just open the files without the dialog.

xl.Run("my_macro", path1, path2)

https://stackoverflow.com/a/16740500/3788802

edited May 23, 2017 at 10:26

CommunityBot

11 silver badge

answered Jun 19, 2015 at 15:44

shelby s.

1671 gold badge5 silver badges16 bronze badges

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

Collectives™ on Stack Overflow

Python: hide Open File Dialog when running Excel macro

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related