2

I have an Excel file with query tables in it. I want python to refresh and save it. Did some research and though I had found the perfect solution!

import win32com.client

# Start an instance of Excel
xlapp = win32com.client.DispatchEx("Excel.Application")

# Open the workbook in said instance of Excel
wb = xlapp.workbooks.open(r'C:\Users\User Name\Company\Management - Documents\Technical\Daily Data.xlsx')

# Optional, e.g. if you want to debug
xlapp.Visible = True

# Refresh all data connections.
wb.RefreshAll()
xlapp.CalculateUntilAsyncQueriesDone()
wb.Save()

# Quit
xlapp.Quit()

When I run this the code, it says the file is open in another application. I have office 365. What am I missing? I also tried the XlWings method and got an error!

Chris Norris
  • 197
  • 1
  • 2
  • 13
  • Your code runs fine for me using a general Excel file. Sometimes with Office365 you could have an issue where either another user is in the file (if it's shared) in which case it can be locked for editing, or Excel is still running in the background. The first thing I would try is just quitting out of Excel entirely, and restarting your computer, that should kill any background Excel processes that might be causing the error. – Mako212 Apr 03 '20 at 06:12
  • I did exactly that, and it worked. I thought it was hanging to begin with, but actually once I closed the windows there was a message box saying that the file was open in another application. Restarting fixed that. Then if someone else was in the file, it prompted for me to push a button to save - fixed that by turning alerts off – Chris Norris Apr 03 '20 at 06:12
  • Only issue left that could arise, is if the file is open on the computer the script runs on. Then I could have the first error again. Unsure how to fix that currently. – Chris Norris Apr 03 '20 at 06:17
  • The best solution is probably to run the script in an environment (like Docker) where you don't have to worry about the file being open. Depending on your use case though, it might be easier to just address the error like you just did, if it comes up infrequently. My guess (correct me if I'm wrong) is that the error stemmed from a "phantom" Excel process, rather than the file actually being open. Assuming this is the case, you can't just run a command to close the file, and you risk nuking other workbooks by killing all Excel processes to force quit out. – Mako212 Apr 03 '20 at 06:29
  • 1
    I would recommend wrapping the entire code in a Try/Except and `xlapp.Quit()` on the Except line. If you have any error whatsoever, you will stack up phantom Excel applications in the background. This will close Excel no matter what happens. – tst Apr 07 '20 at 12:44

2 Answers2

0

I had this issue because the cache excel file is still open in the back end you can kill it in task manager but my workaround which has worked is adding this to end of your code

xlapp=none

Jason Lyn
  • 1
0

You need to close wb then after wb.Save() add wb.Close().

Ceres
  • 2,498
  • 1
  • 10
  • 28
José Junior
  • 11
  • 1