Team,
I am developing a workflow, and as a final step, the data needs to be exported to Excel and then converted to PDF. While this code below functions smoothly on my system, my stakeholders have reported a "Com Error" when the code opens the Excel file to save it as a PDF. I am unable to recreate this issue in my system. Is there anything that needs to be updated on the stakeholder side?
(wb = excel.Workbooks.Open(WB_PATH))
"com_error: (-2147352567, 'exception occurred.', (0, 'microsoft excel', 'open method of workbooks class failed', 'xlmain11.chm', 0, -2146827284), none) "
Below is the python snippet
import win32com.client as win32
def dispatch(app_name: str):
try:
app = win32.gencache.EnsureDispatch(app_name)
except AttributeError:
# Corner case dependencies.
import os
import re
import sys
import shutil
# Remove cache and try again.
MODULE_LIST = [m.__name__ for m in sys.modules.values()]
for module in MODULE_LIST:
if re.match(r'win32com\.gen_py\..+', module):
del sys.modules[module]
shutil.rmtree(os.path.join(os.environ.get('LOCALAPPDATA'), 'Temp', 'gen_py'))
app = win32.gencache.EnsureDispatch(app_name)
return app
import ayx
from ayx import Package
from ayx import Alteryx
import openpyxl
from openpyxl.utils.dataframe import dataframe_to_rows
import win32com.client
from pywintypes import com_error
from datetime import datetime
import pandas as pd
import os
import re
import time
from os import path
#Load info
info_df = Alteryx.read("#1")
info_df = info_df.fillna('').astype({'HType':str})
#Datetime stamp creation for every run
date_stamp = info_df['Folder_Name'].iloc[0]
#path to save files and folder creation
save_path = r'C:\Users\mahato\Documents\Alteryx Package\RP Quotes\\' + date_stamp
spath= r'C:\Users\mahato\Documents\Alteryx Package\RP Quotes'
if not os.path.exists(save_path):
os.makedirs(save_path)
#path to save EXCEL files and folder creation
xl_path = save_path + r"\\"
if not os.path.exists(xl_path):
excel = dispatch("Excel.Application")
excel.Visible = False
excel.ScreenUpdating = False
excel.DisplayAlerts = False
for case_number in info_df.CaseNumber.unique():
#Load workbook
wb_template = openpyxl.load_workbook(template_path)
# template sheet
oneline_template = wb_template.active
# code for placing the values#
#Save files
xl_filename = xl_path + str(file_name) + ".xlsx"
pdf_filename = pdf_path + str(file_name) + ".pdf"
counter = 1
while path.exists(xl_filename):
# Modify the file name with a counter to make it unique
modified_file_name = file_name + "_" + str(counter)
xl_filename = xl_path + modified_file_name + ".xlsx"
pdf_filename = pdf_path + modified_file_name + ".pdf"
counter += 1
wb_template.save(xl_filename)
# Path to original excel file
WB_PATH = xl_filename
# PDF path when saving
PATH_TO_PDF = pdf_filename
# Open
wb = excel.Workbooks.Open(WB_PATH)
# Export to PDF
wb.ActiveSheet.ExportAsFixedFormat(0, PATH_TO_PDF)
excel.Workbooks.Close()
excel.Quit()
Regards,
Alwin
