May-18-2019, 09:29 PM
So I have three files right now, 1. Template file with the rater, which performs calculations, and two files with data that gets copied to the rater.
I wrote a code that loops through the files assigns them to a work book copies the data from the work book to the rater, runs the macro to perform the calculations, and copies the output column back to the work book/save/close.
It works fine if I run it in pieces but click on the worksheets. however when I try to run it within the loop it gives me a com_error.
This leads me to believe that when I create the new workbook it makes the new workbook the active workbook.
I wrote a code that loops through the files assigns them to a work book copies the data from the work book to the rater, runs the macro to perform the calculations, and copies the output column back to the work book/save/close.
It works fine if I run it in pieces but click on the worksheets. however when I try to run it within the loop it gives me a com_error.
This leads me to believe that when I create the new workbook it makes the new workbook the active workbook.
app=xw.apps.add()
rater=app.books.open(path)
#macros to run
clear_all=rater.macro('clear_batch')
batch_rater=rater.macro('batch_rater')
for file_name in filenames:
os.chdir(input_path)
wb=xw.Book(file_name)
comp_data=wb.sheets("Data for Rater").range("copy_range").value
rater.sheets("Batch Rater").range("b23").value=comp_data
batch_rater()
comp_premium=rater.sheets("Batch Rater").range("premium_copy_py").value
#wb.sheets("Batch Rater").range("AQ23").options(transpose=True).value=test
if file_name=="cien_cova_roofage_rooftype.xlsx":
wb.sheets("All Company Premium").range("AQ67202").options(transpose=True).value=comp_premium
elif file_name=='cien_cova_yearbuilt.xlsx':
wb.sheets("All Company Premium").range("AQ12002").options(transpose=True).value=comp_premium
clear_all()
os.chdir(output_path)
wb.save(str(file_name[:-4])+'_batch_output.xlsx')
wb.close()
