I am trying to automate a task in office, in which I have to send client statement to each client manually via email. I though I can automate this task with python and excel vba. So in Python I am taking an a file with all client details and for each client, creating a separate tab in a different file with those details where Macro is saved in xlsm file format. And after this, updating the client number in cell and running Marco to get those details based on client number. At this stage a statement is prepared in in the main tab. And after this I am trying to convert the main tab in pdf file format. Below is my entire Python code.
import pandas as pd
from openpyxl import load_workbook
import win32com.client
class Logic():
old_unique_ids = {} # stores clientid of old and new vales as dict
new_unique_ids = [] # query through excel and gets unique client ids
def __init__(self,input_file,output_file,pdf_path):
self.input_file = input_file
self.output_file = output_file
self.pdf_path = pdf_path
self.read_file = []
def replace_forward_slash_with_hyphen(self):
'''Replaces forward slash with hyphen and the before change client ids are saved in "old_unique_ids" '''
workbook = load_workbook(self.input_file)
for sheet_name in workbook.sheetnames:
sheet = workbook[sheet_name]
for row in sheet.iter_rows():
for cell in row:
if isinstance(cell.value, str) and "/" in cell.value: # checks if any cell value has /
old_value = cell.value
cell.value = cell.value.replace("/", "-")
Logic.old_unique_ids[cell.value] = old_value # append old and new id to dict
workbook.save(self.input_file)
def get_unique_values(self):
'''Gets unique value of new formated id and saves to new_unique_ids'''
self.read_file = pd.read_excel(self.input_file)
self.read_file['Date'] = self.read_file['Date'].dt.strftime('%d/%m/%y') # changes the data format in DF
Logic.new_unique_ids = self.read_file['ClientID'].unique().astype(str) # gets unique values and stores in str
def get_all_items(self):
"""loop through each id and get the detils from file and write the data to new excel file"""
for id in Logic.new_unique_ids:
data = self.read_file[(self.read_file['ClientID'] == id)] # loop and query through each id and get id details
with pd.ExcelWriter(self.output_file,
engine='openpyxl',
mode='a',
engine_kwargs={"keep_vba": True},
if_sheet_exists='replace') as writer:
data.to_excel(writer, # Write data to excel in seperate tab
sheet_name = id,
index=False,
startrow=0,
header=True)
def convertToPdf(self):
excel = win32com.client.Dispatch("Excel.Application")
excel.Visible = False
excel.DisplayAlerts = False
workbook_path = self.output_file
workbook = excel.Workbooks.Open(workbook_path)
ws = workbook.Sheets('Table') # Replace with with acutal sheet name
for n in Logic.new_unique_ids:
if len(Logic.old_unique_ids) != 0:
for i, j in Logic.old_unique_ids.items():
if n == i:
ws.Range('D4').Value = j
break # Exit the loop once a match is found
else:
ws.Range('D4').Value = n
#excel.Run("proFirst") # Replace with actual macro name
workbook.Save()
workbook.Close(SaveChanges=False)
excel.Quit()
o = win32com.client.Dispatch("Excel.Application")
o.Visible = False
o.DisplayAlerts = False
workbook_path = self.output_file
workbook = o.Workbooks.Open(workbook_path)
workbook.Worksheets('Table').Select()
pdf_file_path = f"{self.pdf_path}/{n}.pdf"
workbook.ActiveSheet.ExportAsFixedFormat(0, pdf_file_path)
input_file = 'input.xlsx'
output_file = r"C:\Users\madha\OneDrive\Desktop\Testing\output.xlsm"
pdf_path = "C:/Users/Testing/files"
app = Logic(input_file = input_file,output_file = output_file,pdf_path = pdf_path)
app.replace_forward_slash_with_hyphen()
app.get_unique_values()
app.get_all_items()
app.convertToPdf()
Everything is working fine till running the Macro and creating the statement with Marco in main tab, but when I am trying to convert that statement to PDF my code always throughs an error as below
pywintypes.com_error: (-2147352567, 'Exception occurred.', (0, 'Microsoft Excel', 'Document not saved. The document may be open, or an error may have been encountered when saving.', 'xlmain11.chm', 0, -2146827284), None)
I am not sure where I am going wrong, I have tried different ways to get this work but still not working.
is there a simpler way to make this work??
