Python Forum
Com Error with macro within for loop due to creating new workbook in the loop
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Com Error with macro within for loop due to creating new workbook in the loop
#1
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.
 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() 
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  Returning data on button click by buttons created by a loop bradells 3 374 Apr-23-2025, 03:01 PM
Last Post: Pedroski55
  in c# create a loop counting from 0 to 5, consecutively Frankd 19 2,274 Apr-01-2025, 12:46 PM
Last Post: Frankd
  really new to python want to know how to do a loop pentopdmj 6 1,632 Mar-09-2025, 12:59 PM
Last Post: snippsat
  knowing for loop position in a list medic5678 4 718 Jan-31-2025, 04:19 PM
Last Post: perfringo
  Run this once instead of a loop, do I need the 'calibration' steps? duckredbeard 2 737 Jan-28-2025, 04:55 PM
Last Post: duckredbeard
  Error loop with Chatgpt sportak12 0 520 Jan-14-2025, 12:04 PM
Last Post: sportak12
  How to convert while loop to for loop in my code? tatahuft 4 846 Dec-21-2024, 07:59 AM
Last Post: snippsat
  How to get keep information in a loop ginod 4 911 Dec-11-2024, 01:32 AM
Last Post: deanhystad
  Regarding The For Loop Hudjefa 5 1,496 Nov-15-2024, 01:02 PM
Last Post: deanhystad
  For Loop beginner agoldav 2 738 Nov-05-2024, 12:51 AM
Last Post: agoldav

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020