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

Question

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

did you try running code by giving full path manually, just to check it its working? — Ankush Rathi
– Ankush Rathi, Commented Jul 19, 2017 at 7:41
it only worked when I did a forward slash of the absolute path. However, this cant be as this file maybe deployed onto other's computer. Hence I was looking at the relative path — Adam
– Adam, Commented Jul 19, 2017 at 8:12
hi @YowE3K can i ask what do u mean state full path in open statement? isnt the issue here that i cant pass the excel file into the workbooks.open ()? — Adam
– Adam, Commented Jul 19, 2017 at 9:04
If your current directory is C:\Users\adrlee\Desktop\Python files\Automation and the Excel file is C:\Users\adrlee\Desktop\Python files\Automation\Powerpoint\Automation.xlsb wouldn't you want './Powerpoint/Automation.xlsb' instead of '../Powerpoint/Automation.xlsb'? — YowE3K
– YowE3K, Commented Jul 19, 2017 at 9:06
Sorry about my earlier (deleted) comment - I thought you were passing the relative path to the Open, but you are actually passing the absolute path. I think you just need to specify the path relative to the current path instead of to the current path's parent - i.e. . instead of .. — YowE3K
– YowE3K, Commented Jul 19, 2017 at 9:06

Adam · Accepted Answer · 2017-07-19 13:37:51Z

1

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");

answered Jul 19, 2017 at 13:37

Adam

1,3157 gold badges20 silver badges43 bronze badges

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

Comments

YowE3K · Accepted Answer · 2017-07-19 09:33:15Z

0

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')

edited Jul 19, 2017 at 9:33

answered Jul 19, 2017 at 9:26

YowE3K

24k7 gold badges28 silver badges45 bronze badges

1 Comment

Adam Over a year ago

hihi , I edited my code such that 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); xl=win32.client.Dispatch('Excel.Application') xl.Workbooks.Open(Filename = filename) del xl but im still getting the same error: 'Exception occurred.', (0, 'Microsoft Excel', "Sorry, we couldn't find C:\\Users\\adrlee\\Desktop\\Python files\\ Automation\\Powerpoint\\Automation.xlsb.

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related