I am running into the following error in Python pywintypes.com_error: (-2147352567, ‘Exception occurred.’, (0, ‘Microsoft Excel’, ‘CopyPicture method of Range class failed’, ‘xlmain11.chm’, 0, -2146827284), None) when trying to copy charts from an excel file into an email. The error however only appears sometimes. I have read some other threads relating to this error and have added a time aspect time.sleep(8) so it waits a certain amount of seconds before copy the chart after opening the excel. I have also added the excel.CutCopyMode = False which was another suggestion but nothing seems to be working consistently. I am new to python so any help would be great!
import win32com.client
import pythoncom
import sys
import os
from PIL import ImageGrab
from datetime import datetime
import time
#Step 1 Refresh Haver Data in Average Hourly Earnings File
def initiate_excel():
pythoncom.CoInitialize()
excel = win32com.client.DispatchEx('Excel.Application')
excel.DisplayAlerts = True
excel.workbooks.Open(r'X:DLXDLX.XLAM')
excel.Visible = True
return excel
def close_excel(excel):
excel.Quit()
pythoncom.CoUninitialize()
def get_file_location(AHE_File):
if not os.path.exists(AHE_File):
print(f'{AHE_File} does not exist')
return None
if os.path.dirname(AHE_File) == "":
return f'{os.getcwd()}\{AHE_File}'
return AHE_File
def run_macro(AHE_File, sub):
errorLevel = 0
file = get_file_location(AHE_File)
try:
excel = initiate_excel()
macro = f"'{AHE_File}'!{sub}"
excel.Application.Run(macro)
except:
if __name__ == '__main__':
print('Warning - potential error in Excel Macro')
errorLevel = 1
else:
raise
finally:
close_excel(excel)
return errorLevel
#Step 2 Refresh Haver Data in Payrolls File
def initiate_excel():
pythoncom.CoInitialize()
excel = win32com.client.DispatchEx('Excel.Application')
excel.DisplayAlerts = True
excel.workbooks.Open(r'X:QuantDLXDLX.XLAM')
excel.Visible = True
return excel
def close_excel(excel):
excel.Quit()
pythoncom.CoUninitialize()
def get_file_location(Payrolls_File):
if not os.path.exists(Payrolls_File):
print(f'{Payrolls_File} does not exist')
return None
if os.path.dirname(Payrolls_File) == "":
return f'{os.getcwd()}\{Payrolls_File}'
return Payrolls_File
def run_macro(Payrolls_File, sub):
errorLevel = 0
file = get_file_location(Payrolls_File)
try:
excel = initiate_excel()
macro = f"'{Payrolls_File}'!{sub}"
excel.Application.Run(macro)
except:
if __name__ == '__main__':
print('Warning - potential error in Excel Macro')
errorLevel = 1
else:
raise
finally:
close_excel(excel)
return errorLevel
#Step 3 Refresh Haver Data in Payrolls Full File
def initiate_excel():
pythoncom.CoInitialize()
excel = win32com.client.DispatchEx('Excel.Application')
excel.DisplayAlerts = True
excel.workbooks.Open(r'X:QuantDLXDLX.XLAM')
excel.Visible = True
return excel
def close_excel(excel):
excel.Quit()
pythoncom.CoUninitialize()
def get_file_location(Full_File):
if not os.path.exists(Full_File):
print(f'{Full_File} does not exist')
return None
if os.path.dirname(Full_File) == "":
return f'{os.getcwd()}\{Full_File}'
return Full_File
def run_macro(Full_File, sub):
errorLevel = 0
file = get_file_location(Full_File)
try:
excel = initiate_excel()
macro = f"'{Full_File}'!{sub}"
excel.Application.Run(macro)
except:
if __name__ == '__main__':
print('Warning - potential error in Excel Macro')
errorLevel = 1
else:
raise
finally:
close_excel(excel)
return errorLevel
AHE_File = r'X:PythonPayrollsAverage_Hourly_Earnings.xlsm'
Payrolls_File = r'X:PythonPayrollsPayrolls.xlsm'
Full_File = r'X:PythonPayrollsPayrolls_Full_Table.xlsm'
payrolls_table = r'X:PythonPayrollsPayrolls_table.jpg'
AHE_table = r'X:PythonPayrollsAHE_table.jpg'
Full_table = r'X:PythonPayrollsFull_table.jpg'
sub = "Macro1.RefreshData"
run_macro(AHE_File, sub)
run_macro(Payrolls_File, sub)
run_macro(Full_File, sub)
# Step 4 Take Screenshot of Payrolls Table
pythoncom.CoInitialize()
excel = win32com.client.DispatchEx('Excel.Application')
wb = excel.Workbooks.Open(Payrolls_File)
sheet = wb.Sheets("Tables")
time.sleep(5)
copyrange = sheet.Range('B3:E27')
excel.CutCopyMode = False
copyrange.CopyPicture(Appearance=1, Format=2)
ImageGrab.grabclipboard().save(payrolls_table)
close_excel(excel)
#Step 5 Take a Screenshot of AHE Table
pythoncom.CoInitialize()
excel = win32com.client.DispatchEx('Excel.Application')
wb = excel.Workbooks.Open(AHE_File)
sheet1 = wb.Sheets("Tables")
time.sleep(5)
copychart = sheet1.Range('B3:E25')
excel.CutCopyMode = False
copychart.CopyPicture(Appearance=1, Format=2)
ImageGrab.grabclipboard().save(AHE_table)
close_excel(excel)
#Step 6 Take a Screenshot of Full Table
pythoncom.CoInitialize()
excel = win32com.client.DispatchEx('Excel.Application')
wb = excel.Workbooks.Open(Full_File)
sheet1 = wb.Sheets("Table")
time.sleep(8)
copychart = sheet1.Range('D4:G20')
excel.CutCopyMode = False
copychart.CopyPicture(Appearance=1, Format=2)
ImageGrab.grabclipboard().save(Full_table)
close_excel(excel)
#Step 7 Build Email
outlook = win32com.client.Dispatch('Outlook.Application')
message = outlook.CreateItem(0)
message.To = "[email protected]"
Today = datetime.today()
date = Today.strftime("%m/%d/%Y")
message.Subject = date + " Payrolls Print"
message.BodyFormat = 2
image_cid1= "Payrolls_table.jpg"
image_cid2= "AHE_table.jpg"
image_cid3= "Full_table.jpg"
body = """
<html>
<body>
<p>Hello - Please see below for a complete breakdown from todays Payroll Print. </p>
<meta http-equiv="Content-Type" content="text/html; charset=us-ascii">
<p>Below is a table of today's payroll levels. </p>
<p><img src="cid:Payrolls_table.jpg">
<p>Below is a table of MoM percent change in Average Hourly Earnings. </p>
<p><img src="cid:AHE_table.jpg">
<p>Below is a summary table of payrolls. </p>
<p><img src="cid:Full_table.jpg">
</body>
</html>
"""
attachment1 = message.Attachments.Add(r'X:PythonPayrollsPayrolls_table.jpg')
attachment2 = message.Attachments.Add(r'X:PythonPayrollsAHE_table.jpg')
attachment3 = message.Attachments.Add(r'X:PythonPayrollsFull_table.jpg')
attachment1.PropertyAccessor.SetProperty("http://schemas.microsoft.com/mapi/proptag/0x3712001F", "Payrolls_table.jpg")
attachment2.PropertyAccessor.SetProperty("http://schemas.microsoft.com/mapi/proptag/0x3712001F", "AHE_table.jpg")
attachment3.PropertyAccessor.SetProperty("http://schemas.microsoft.com/mapi/proptag/0x3712001F", "Full_table.jpg")
message.HTMLBody = body
message.Display()