Appending excel spreadsheet data to file using python

I need help with my python code for the following:

In this network path: “\folder\name\endfile.xlsm”, I have a file called "endfilexlsm" Within this file, I have a macro called “ProcessReports”, run this macro. Then, run the macro “DeleteRowsByCurrentMonthYear”

In this network folder path: "\folder2\name2\files" There is a file that gets generated for every business day. The file name is in the form concatenated_file_123_as_of_YYYYMMDD.csv Where YYYY represents a year, MM represents a month and DD represents the day for a date. The file that is generated in this folder is dated using the previous business day's date in the naming convention.

For example, if today is July 5, 2024 the file that would be generated would be called concatenated_file_123_as_of_20240704.csv which represents July 4, 2024. Using the date string in the file name, find the latest file. This is the output file.

Using the file “endfile.xlsm” copy all rows starting from the first column all the way to column 88 and paste the data into the output file underneath the existing data in that file.

Once all the pasting has been completed into the output file, in the file "endfile.xlsm" run the macro "deleteall" and then save the file. Then save the output file and close it.

Unfortunately, for the code I've written below when my new data is being appended, it deletes the first 88 columns of my existing dataset and shifts the existing data about 80 columns to the right. The new data is not appending directly underneath as it is expected to.

import os
import datetime
import win32com.client as win32
from openpyxl import load_workbook
import pandas as pd

# Paths to the files and directories
network_path_endfile = r"K:\folder\name\endfile.xlsm"
network_folder_path_output = r"C:\folder2\name2\files"

# Initialize Excel application
excel_app = win32.Dispatch('Excel.Application')
excel_app.Visible = False

# Function to run a macro
def run_macro(workbook_path, macro_name):
    workbook = excel_app.Workbooks.Open(workbook_path)
    excel_app.Application.Run(f"'{workbook.Name}'! {macro_name}")
    workbook.Save()
    workbook.Close()

# Run the initial macros in endfile.xlsm
run_macro(network_path_endfile, 'ProcessReports')
run_macro(network_path_endfile, 

'DeleteRowsByCurrentMonthYear')

# Get the previous business day's date
today = datetime.datetime.today()
previous_business_day = today - datetime.timedelta(days=1)
if today.weekday() == 0:  # If today is Monday
    previous_business_day -= datetime.timedelta(days=2)

previous_business_day_str = 
previous_business_day.strftime('%Y%m%d')

# Find the latest file in the network folder
latest_file_name = 
f"concatenated_file_123_as_of_{previous_business_day_str}.csv"
latest_file_path = os.path.join(network_folder_path_output, 
latest_file_name)

# Load the latest CSV file
latest_df = pd.read_csv(latest_file_path)

# Load the endfile.xlsm workbook
endfile_wb = load_workbook(network_path_endfile, 
data_only=True)
endfile_ws = endfile_wb.active

# Extract data from endfile.xlsm
data = []
for row in endfile_ws.iter_rows(min_row=1, max_col=88, 
values_only=True):
    data.append(row)

# Convert the data to a DataFrame
data_df = pd.DataFrame(data)

# Append the new data to the existing data in the latest CSV file
updated_df = pd.concat([latest_df, data_df], ignore_index=True)

# Save the updated DataFrame back to the CSV file
updated_df.to_csv(latest_file_path, index=False)

# Run the final macro in endfile.xlsm
run_macro(network_path_endfile, 'deleteall')

# Close Excel application
excel_app.Quit()

import os
import glob
import win32com.client as win32
from datetime import datetime, timedelta
import pythoncom
import time

network_path = r"C:\Users\name\foldername"
output_folder = r"\\files\foldername"

endfile = "endfile.xlsm"
macro_name1 = "Module3.ProcessReports"
macro_name2 = "DeleteRowsByCurrentMonthYear"

def run_macro_with_error_handling(excel, macro_name):
    try:
        excel.Application.Run(f"'{endfile}'!{macro_name}")
    except pythoncom.com_error as e:
        if e.excepinfo[5] == -2146828283:  # Overflow error code
            print(f"Overflow error in {macro_name}. Bypassing...")
            # Simulate pressing "End" button
            for _ in range(3):  # Press Escape key three times
                win32.SendKeys("{ESC}")
                time.sleep(0.5)
        else:
            print(f"Error in {macro_name}: {e}")

excel = win32.gencache.EnsureDispatch("Excel.Application")
excel.Visible = False  # Keep Excel hidden for better performance

try:
    # Step 1
    wb_endfile = excel.Workbooks.Open(os.path.join(network_path, endfile))
    
    #Module3.ProcessReports error handling
    run_macro_with_error_handling(excel, macro_name1)
    
    # DeleteRowsByCurrentMonthYear
    run_macro_with_error_handling(excel, macro_name2)
    
    # Step 2
    today = datetime.today()
    previous_business_day = today - timedelta(days=1)
    file_pattern = f"concatenated_*_as_of_{previous_business_day.strftime('%Y%m%d')}.csv"
    matching_files = glob.glob(os.path.join(output_folder, file_pattern))
    
    if not matching_files:
        raise FileNotFoundError(f"No matching output file found for {previous_business_day.strftime('%Y%m%d')}")
    
    latest_file = max(matching_files, key=os.path.getctime)
    
    # Step 3
    ws_endfile = wb_endfile.Worksheets(1)
    last_row_endfile = ws_endfile.Cells(ws_endfile.Rows.Count, "A").End(-4162).Row
    range_endfile = ws_endfile.Range(f"A1:CJ{last_row_endfile}")
    
    wb_output = excel.Workbooks.Open(latest_file)
    ws_output = wb_output.Worksheets(1)
    last_row_output = ws_output.Cells(ws_output.Rows.Count, "A").End(-4162).Row
    
    range_endfile.Copy()
    ws_output.Cells(last_row_output + 1, 1).PasteSpecial(Paste=-4163)  # xlPasteValues
    
    # Step 4
    run_macro_with_error_handling(excel, "deleteall")
    wb_endfile.Save()
    wb_output.Save()

except Exception as e:
    print(f"An error occurred: {e}")

finally:
    # Clean up: close workbooks and quit Excel
    excel.DisplayAlerts = False
    if 'wb_endfile' in locals():
        wb_endfile.Close(SaveChanges=False)
    if 'wb_output' in locals():
        wb_output.Close(SaveChanges=False)
    excel.Quit()