0

I'm trying to run my first python script (newbie) to open an excel book, run and macro, and close the book. The macro is running just fine, but I'm getting a few traceback errors that I'm really struggling to understand. The other issue appears to be that excel remains open once the script has run (although the worksheet itself has closed).

Here's the script (some parts are omitted with '...'):

import os, os.path
import win32com.client

if os.path.exists("S:\...\Warehouse Inventory Template for Customer.xlsm"):
    xl=win32com.client.Dispatch("Excel.Application")
    wb = xl.Workbooks.Open(os.path.abspath("S:\...\Warehouse Inventory Template for Customer.xlsm"), ReadOnly=1)
    xl.Application.Run("'Warehouse Inventory Template for Customer.xlsm'!CustomerInventoryUpdate")
    wb.Close(False)
    #xl.Application.Quit()
    del wb
    del xl

The macro runs fine here, but then:

> Traceback (most recent call last):   
> File "T:\...\Python\Scripts\customerstockfeed.py", line 7, in <module>
>     xl.Application.Run("'Warehouse Inventory Template for Customer.xlsm'!Main.CustomerInventoryUpdate")   File "<COMObject
> <unknown>>", line 8, in Run   File
> "C:\Users\...\AppData\Local\Programs\Python\Python311\Lib\site-packages\win32com\client\dynamic.py",
> line 368, in _ApplyTypes_
>     result = self._oleobj_.InvokeTypes( pywintypes.com_error: (-2147352567, 'Exception occurred.', (0, None, None, None, 0,
> -2146788248), None)
buran
  • 13,682
  • 10
  • 36
  • 61
Durin
  • 1
  • 1
    " excel remains open once the script has run" because the `Quit` line is commented out. – Tim Williams Mar 13 '23 at 16:58
  • Is it essentially a choice of close all open workbooks vs. close on workbook but leave excel open? – Durin Mar 13 '23 at 17:03
  • Are all the variable types in the macro declared with Dim's ? – CDP1802 Mar 13 '23 at 17:07
  • Well you can close the workbook and leave Excel open, as you're seeing... You are the one who decides which workbooks get closed, and whether you also exit Excel. – Tim Williams Mar 13 '23 at 17:11
  • All variables are declared with Dim (no public variables if that's what you're after?) – Durin Mar 13 '23 at 17:51
  • Apologies, I meant to write "one workbook". I mean to say if I only have the workbook open that I've accessed through python, and I close that workbook, unless I allow application.Quit to run then excel will remain open with no sheet. However, if I have another workbook already open, and I run application.Quit, this will close the other workbook as excel is being shut down entirely. There's no way to have excel shut down completely in the first case, but only close the workbook accessed by python in the second? – Durin Mar 13 '23 at 17:55
  • Not a Python user but this seem relevant here: https://stackoverflow.com/a/52572968/478884 – Tim Williams Mar 13 '23 at 19:58
  • Why not check `xl.Workbooks.Count()` to see if any other workbooks are open in order to decide whether to `Quit` Excel? – DS_London Mar 13 '23 at 21:02

0 Answers0