Reputation: 81
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
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