data sheet

data sheet

data sheet is tied with below VB script
VB Code

Sheet1 sheet which has pivot table
Sheet1 sheet

txt_excel.txt
txt_excel.txt file

I’m trying to update the cell values in my data sheet using a python script. But the RefreshAll event is not getting fired and updating my pivot table in Sheet1 sheet.

Here is the Python script which reads the values from text file and updates data sheet

excel_filepath = "test_excel_file2.xlsm"
txt_filepath = "txt_excel.txt"
data_sheet = "data"
calc_sheet = "Sheet1"

df = pd.read_csv(txt_filepath, sep="|")
print(df)

wb = load_workbook(excel_filepath, keep_vba=True)
ws = wb[data_sheet]
ws.delete_rows(1,ws.max_row)
wb.save(excel_filepath)
ws.active = data_sheet
for r in dataframe_to_rows(df, index=False, header=True):
    ws.append(r)
wb.save(excel_filepath)

new_df = pd.read_excel(excel_filepath, sheet_name=None)
print(new_df[calc_sheet])

Here the new_df variable is not returning the updated value

How to resolve this issue?