CODEWITHSUNDEEP
pythonexcelvba
Adam
Adam

Reputation: 1285

Open excel file to run macro from relative file path in python

I am running a code as below in python to open an excel and run a macro. Basically my python script is sitting in 

C:\Users\adrlee\Desktop\Python files\Automation

and my excel VBA file (Automation.xlsb) is sitting in 

C:\Users\adrlee\Desktop\Python files\Automation\Powerpoint

I am running this code

fileDir = os.path.dirname(os.path.realpath('__file__'));

filename = os.path.join(fileDir, '../Powerpoint/Automation.xlsb')
filename = os.path.abspath(os.path.realpath(filename))
print(filename);

if os.path.exists("Powerpoint/Automation.xlsb"):
    xl=win32com.client.Dispatch("Excel.Application")
    xl.Workbooks.Open(filename)
    xl.Application.Quit() # Comment this out if your excel script closes
    del xl

print("Powerpoint generated");

but i am getting error

pywintypes.com_error: (-2147352567, 'Exception occurred.', (0, 'Microsoft Excel', "Sorry, we couldn't find C:\\Users\\adrlee\\Desktop\\Python files\\Powerpoint\\Automation.xlsb. Is it possible it was moved, renamed or deleted?", 'xlmain11.chm', 0, -2146827284), None)

What am i doing wrong

Upvotes: 0

Views: 2328

Answers (2)

Adam
Adam

Reputation: 1285

Thanks for the comments and hints guys! I managed to finally get it right:

fileDir = os.path.dirname(os.path.realpath('__file__'));

filename = os.path.join(fileDir, './Powerpoint/Funnel Automation.xlsb')
print(filename);


xl=win32com.client.Dispatch('Excel.Application')
xl.Workbooks.Open(Filename = filename)
del xl

print("Powerpoint generated");

Upvotes: 1

YowE3K
YowE3K

Reputation: 23974

If fileDir contains

C:\Users\adrlee\Desktop\Python files\Automation\

then joining ..\Powerpoint\Automation.xlsb to it will give

C:\Users\adrlee\Desktop\Python files\Automation\..\Powerpoint\Automation.xlsb

which is equivalent to

C:\Users\adrlee\Desktop\Python files\Powerpoint\Automation.xlsb

because .. is equivalent to the parent directory, and the parent directory of ...\Python files\Automation is ...\Python files.

Your question states that the Excel file is actually 

C:\Users\adrlee\Desktop\Python files\Automation\Powerpoint\Automation.xlsb

so you should really be joining .\Powerpoint\Automation.xlsb to the fileDir variable. (While .. refers to the parent directory, . refers to the existing directory.)

i.e. use:

filename = os.path.join(fileDir, './Powerpoint/Automation.xlsb')

Upvotes: 0

Related Questions