0

I have a process that clicks an Export button with pyautogui, and then waits for the Excel file to automatically open. This can take anything up to 10 seconds, so I wrote a small function so it waits for the Excel file to open. The meat of the function was based on this little experiment:

from win32com.client import Dispatch

while True:
    xl = Dispatch('Excel.Application')
    print(len(xl.Workbooks))

When I first made this and ran it, it spat out a string of zeros until I opened an instance of Excel, and then it spat out a string of ones, and when I close Excel it spat out zeros again. It was exactly what I needed to build a function like this:

def export_report():
    """Click Export and wait 30 seconds for it to open."""
    xl = Dispatch('Excel.Application')
    open_workbooks_before_export = len(xl.Workbooks)
    click_image_on_screen('images/button_export_csv.png')
    wait = 0
    while len(xl.Workbooks) == open_workbooks_before_export: 
        xl = Dispatch('Excel.Application')
        if len(xl.Workbooks) != open_workbooks_before_export:
            break
        else:
            time.sleep(0.5)
            wait += 1
            if wait > 60:
                raise Exception('Report is not exporting')

Again, I'm sure when I first made this it worked perfectly. It just loops around waiting for the number of activate Excel workbooks to change, and then breaks out. But the next day it just stopped working. The previous snippet just spits out zeros, no matter how many instances of Excel I open. It's like the Dispatch('Excel.Application') part isn't working.

When I set xl.Visible=True a new, blank instance of Excel is launched, but that isn't what I want. It should access the running instance so I can access details of the currently open Excel files.

Any ideas why this has stopped working, and how I can get it to work again?

ElJuanito
  • 13
  • 2
  • Why are you doing this in the first place? Why not generate an Excel file using openpyxl or Pandas? If you want to save data as CSV you can use the `csv` package or, once again, Pandas. You don't need Excel to create Excel files and certainly not text files like CSVs – Panagiotis Kanavos May 17 '23 at 09:09
  • Clicking Export automatically opens an Excel instance that I then need to interact with. This is not a locally-saved file that can be opened with a Python package. – ElJuanito May 17 '23 at 09:43
  • If Excel can read it, so can Python. If it's stored on a file server, you can open it with the correct path. If you mean it's stored in OneDrive, you can retrieve the file by its URL. Or you can use the [OneDrive Python SDK](https://github.com/OneDrive/onedrive-sdk-python) to list folders, files etc. Same with Google Drive. – Panagiotis Kanavos May 17 '23 at 10:26
  • Use `win32com.client.GetObject()` to attach to a running Excel instance: http://timgolden.me.uk/python/win32_how_do_i/attach-to-a-com-instance.html .Also, you might try `xl.Workbooks.Count` rather than using `len`. An alternative to counting workbooks by polling is to hook into the `Application.WorkbookBeforeClose` Event, and wait on that to be signalled. – DS_London May 17 '23 at 12:22
  • @PanagiotisKanavos The OP's use case is unclear. It looks as if they are opening a sheet for the user to manipulate in some way. – DS_London May 17 '23 at 12:35
  • And I meant the `Application.WorkbookOpen` event ... – DS_London May 17 '23 at 12:38

0 Answers0