我目前正在编写一个脚本,要求我更新Excel工作表中的数据并运行宏。在运行宏之后,我尝试使用win32保存文件,以便能够再次运行类似的过程。然而,我已经把一切都与win32和openpyxl的一个奇怪问题联系起来了。看起来,如果我使用win32运行宏,保存并打开文件,一切看起来都会像它应该的那样。但是,如果在运行宏之后使用openpyxl向该文件添加一些内容,那么宏的所有结果都将被删除。例如,宏使用正确的数据填充单元格A4:H23,但在使用win32保存并使用openpyxl重新打开和编辑后,如果我尝试在单元格A3中添加任何值并打开文件,则唯一显示的是A3中的值,而宏不应执行任何操作。我可以手动打开工作表,添加数字并重新保存,但当我使用openpyxl这样做时,它会清除所有宏结果。我已将我的代码包含在下面以供参考。任何帮助都将不胜感激
from openpyxl import load_workbook
import os
import win32com.client
import re
import time
Macro1 = r"C:\Users\...\Documents\Macro1Demo.xlsm"
Macro2 = r"C:\Users\...\Documents\Macro2Demo.xlsm"
MiddleManTest = r"C:\Users\...\Documents\MiddleManTest.xlsm"
MiddleMan2 = r"C:\Users\...\Documents\MiddleMan2.xlsm"
DistrictList = r"C:\Users\...\Documents\DistrictList.xlsx"
Macro1WB = load_workbook(filename = Macro1, keep_vba = True)
Macro2WB = load_workbook(filename = Macro2, keep_vba = True)
MM2WB = load_workbook(filename = MiddleMan2, keep_vba = True)
ListWB = load_workbook(filename = DistrictList, keep_vba = True)
ListSheet = ListWB['Sheet1']
M1Sheet = Macro1WB['Entry']
M2Sheet = Macro2WB['Raw']
for i in range(2,3):
Macro1WB['Entry']['A2'].value = ListWB['Sheet1']['A' + str(i)].value
Macro1WB['Entry']['B2'].value = ListWB['Sheet1']['B' + str(i)].value
OrgName = Macro1WB['Entry']['A2'].value
DistrictName = Macro1WB['Entry']['B2'].value
Macro1WB.save(Macro1)
SimpleOrgName = re.sub('[/\:*?<>|"]','',OrgName)
SimpleDistrictName = re.sub('[/\:*?<>|"]','',DistrictName)
NewFileName = SimpleOrgName + "(" + SimpleDistrictName + ")"
print(Macro1WB['Entry']['A2'].value,Macro1WB['Entry']['B2'].value)
print("Org/District Copied.")
if os.path.exists(r"C:\Users\...\Documents\Macro1Demo.xlsm"):
xl = win32com.client.Dispatch("Excel.application")
xl.visible = True
workbook = xl.Workbooks.Open(os.path.abspath(r"C:\Users\...\Documents\Macro1Demo.xlsm"))
xl.Application.Run("Macro1Demo.xlsm!Module1.AdvancedFilter")
print("Data filtered.")
workbook.SaveAs(MiddleMan2)
xl.Application.Quit()
MM2WB['Entry']['A3'] = i
MM2WB.save(MiddleMan2)
目前没有回答
相关问题 更多 >
编程相关推荐