Python pandas write resulted dataframe to xlsm without losing the macro

I have a lot of excel files that I need to compile into a single excel file, and then copy the compiled one into an existing excel file (with macro / .xlsm) in a certain sheet.

I solved the first problem (compiling multiple excel files into a single excel file). The resulted dataframe is saved in .csv format. The resulted file looks like this.

Until here there is no issue. The next step that I am struggling to find out how to do it.

From the resulted dataframe I want to “copy-and-paste” the dataframe to the existing excel file with macro (.xlsm) in the sheet “Source” at the corresponding headers. The existing excel file looks like this.

As you may see from the picture above, I want to skip writing any data in the column A since the cells within this column is full of formula. I want to write the resulted dataframe in the column B to column Q within the existing excel file. However, before writing the data, I want delete all the existing data in all cells (except in the cells within column A).

So basically I want to do the following:

1. delete all the values in the cells in the column B to column Q in the existing xlsm file (in the sheet “Source”)
2. write the new values from the resulted dataframe to column B until column Q
3. save the excel file back with the same name without losing the macro

Any feedback will be much appreciated! Thanks!

Regards,

Arnold

Answer

Sorry a bit late to come back updating my question. Finally I have solved my problem with openpyxl package.

So here is my final code:

import openpyxl
import os
import string
import pandas as pd
import numpy as np

path = #folder directory
target_file = #excel filename
sheetname = #working sheet that you wish to work on with

filename = os.path.join(path, target_file)

wb = openpyxl.load_workbook(filename, keep_vba=True)
sheet = wb.get_sheet_by_name(sheetname)

# To Erase All Values within Selected Columns
d = dict()
for x, y in zip(range(1, 27), string.ascii_lowercase):
    d[x] = y.upper()

max_row = sheet.max_row
max_col = sheet.max_column

for row in range(max_row):
    row += 1
    if row == 1: continue
    for col in range(max_col):
        col += 1
        if col == 1: continue
        sheet['{}{}'.format(d[col], row)] = None

# To Write Values to the Blank Worksheet
path_dataframe = # folder directory to the csv file
target_compiled = # csv filename
filename_compiled = os.path.join(path_compiled, target_compiled)

compiled = pd.read_csv(filename_compiled, low_memory=False, encoding = "ISO-8859-1")

for row in range(len(compiled.index)):
    row += 1
    if row == 1: continue # I do not want to change the value in row 1 in excel file because they are headers
    for col in range(max_col): 
        col += 1
        if col == 1: continue # I do not want to change the values in column 1 in excel file since they contain formula
        value = compiled.iloc[row-2][col-2]
        if type(value) is str: value = value
        elif type(value) is np.float64: value = float(value)
        elif type(value) is np.int64: value = int(value)
        sheet['{}{}'.format(d[col], row)] = value

wb.save(filename)