CODEWITHSUNDEEP
pythonwin32com
Prasanna K
Prasanna K

Reputation: 27

Using Python to refresh Excel - Getting error

I am trying to refresh the excel because i am using pivot data in it.but i am getting error

xlapp = win32com.client.DispatchEx("Excel.Application")

current_dir = Path.cwd()
out_file = current_dir.joinpath(destination_file)
print(f"The outfile path is {out_file}")

# Open the workbook in said instance of Excel
wb_7 = xlapp.workbooks.open(out_file)

# Refresh all data connections.
wb_7.RefreshAll()
wb_7.Save()

# Quit
xlapp.Quit()

Error is

 wb_7 = xlapp.workbooks.open(out_file)
 File "<COMObject <unknown>>", line 2, in open
 TypeError: must be real number, not WindowsPath

Any advise here

Upvotes: 0

Views: 4887

Answers (1)

Orhan Solak
Orhan Solak

Reputation: 809

Before starting, be sure that your path is correct. If you are using jupyter notebook, it will raise error. And if your excel file is in the same folder with your py script, you should use following code for assigning path:

pathname = os.path.dirname(sys.argv[0])
path = os.path.abspath(pathname) + '\\'
out_file = path + out_file

Also, you need to use the function appropriately. Some functions start with Uppercase of letter. Use like following:

xlapp.Workbooks.Open

And for healthy refreshing, I recommend you to open xlapp with specific properties, such as:

xlapp = win32com.client.gencache.EnsureDispatch("Excel.Application")
xlapp.Interactive = False
xlapp.Application.EnableEvents = False
xlapp.DisplayAlerts = False  # Suppress any Alert windows, which require User action
xlapp.AskToUpdateLinks = False # Disable automatic update linking
xlapp.Visible = False  # Run the Application in the background

Refreshing

wb_7 = xlapp.Workbooks.Open(out_file)
wb_7.RefreshAll()
xlapp.CalculateUntilAsyncQueriesDone()
wb_7.Save()
wb_7.Close(True)
wb_7 = None   # Unset wb_7 variable (This is to ensure it's closed)

And do not forget to restore original excel properties

xlapp.Interactive = True
xlapp.DisplayAlerts = True  # Suppress any Alert windows, which require User action
xlapp.AskToUpdateLinks = True  # Disable automatic update linking
xlapp.Application.EnableEvents = True
xlapp.Visible = True  # Run the Application in the background
xlapp.Quit()    # Close Excel

Upvotes: 1

Related Questions