CODEWITHSUNDEEP
pythonexcelpython-3.xwin32comvba
shilohln
shilohln

Reputation: 81

Incessant pywintypes.com_error in Python when trying to run a macro

All I want to do is open a .xlsm file, run a macro, save the workbook and then quit. Ideally I could pass the macro as a variable because different situations would run a different macro located in the same workbook. Here is the code so far.

import os, sys
import win32com.client

location = input("AOC or LOC")

macroBook = 'C:/path/to/workbook/solar.xlsm'
macro = 'solar.xlsm!Module1.Tag' + location

try:

    if os.path.exists(macroBook):
        xl=win32com.client.Dispatch("Excel.Application")
        xl.Workbooks.Open(Filename=macroBook)
        xl.Application.Run(macro)                 #Getting the error at this line
        xl.Application.Save()
        xl.Application.Quit()
        del xl
except:
    print("Unexpected error:" sys.exc_info()[0])

I have finally gotten the macro to run, but I am still getting the same error once the macro is finished. This is a lot of headway for me since before I couldn't get the macro to run at all, but this program is useless to me if it just errors out after the macro finishes.

So I'm at a point where I don't even know what to troubleshoot. The macro runs but I am stuck at the same line that I was when the macro didn't run. I used to have the 64 bit version and that's what was causing problems but I've switched to 32 bit python.

Could it be getting an error because the macro takes 2-4 minutes to run? Maybe it isn't waiting for it to finish? I'm at a loss. Willing to post my macro as well if needed.

Edit: I deleted the Application.Save line. Code now looks like the following.

import os, sys
import win32com.client

location = input("AOC or LOC")
taggedData = 'C:/path/to/new/file.csv'
macroBook = 'C:/path/to/workbook/solar.xlsm'
macro = 'solar.xlsm!Module1.Tag' + location

try:

    if os.path.exists(macroBook):
        conn = win32com.client.Dispatch('ADODB.Connection')
        conn.CommandTimeout = 3600
        xl=win32com.client.Dispatch("Excel.Application")
        xl.Workbooks.Open(Filename=macroBook)
        xl.Application.Run(macro)                 #Getting the error at this line
        for sheet in xl1.Worksheets:
            if sheet.Name == "Sheet1":
                nwb = xl.WorkbookAdd()
                sheet.Copy(Before=nwb.Sheet(1))
                nwb.SaveAs(taggedData)
                nwb.Close(True)
        xl.Application.Quit()
        del xl
except:
    print("Unexpected error:" sys.exc_info()[0])

Now I am getting a Name Error at conn.Open(). Not sure how that is supposed to get implemented.

Upvotes: 1

Views: 2245

Answers (1)

grug.0
grug.0

Reputation: 355

I'm fairly sure your COM-interface is timing out. I have encountered similar errors manipulating databases from py / excel... try adding a command timeout like this:

conn = win32com.client.Dispatch('ADODB.Connection')
conn.CommandTimeout = 3600

Otherwise, if you are exclusively working with Excel / Python, all of your COM-interface and runtime junk is handled smoothly if you use xlwings lib. I leave you an example and suggest importing xlwings.

Assuming you have a macro set up like this:

Sub my_macro()
    RunPython ("import my_module; my_module.my_macro()")
End Sub

Cook this up in Python:

import os
from xlwings import Workbook, Range

def my_macro():
    wb = Workbook.caller()
    Range('A1').value = 1
    # Now basically add whatever you want right here

if __name__ == '__main__':
    # Expects the Excel file next to this source file, adjust accordingly.
    path = os.path.abspath(os.path.join(os.path.dirname(__file__), 'myfile.xlsm'))
    Workbook.set_mock_caller(path)
    my_macro()

Using the cource code structure above, put whatever you'd like into my_macro():

Otherwise, documentation is here, and it is easy: http://xlwings.org/ .

Upvotes: 2

Related Questions