将变量从python传递给vba

pip install pywin32

import win32com.client as win32

def pass_variable_to_vba(variable):
    # 连接到Excel应用程序
    excel = win32.gencache.EnsureDispatch('Excel.Application')
    
    # 打开要运行VBA代码的工作簿
    workbook = excel.Workbooks.Open(r'path\to\your\workbook.xlsm')
    
    # 获取工作簿中的VBA模块
    vba_module = workbook.VBProject.VBComponents('Module1')
    
    # 设置VBA模块中的变量
    vba_module.CodeModule.AddFromString(f"Public myVariable As String: myVariable = '{variable}'")
    
    # 运行VBA代码
    excel.Run('YourMacroName')
    
    # 关闭工作簿和Excel应用程序
    workbook.Close(SaveChanges=False)
    excel.Quit()

# 调用函数，将变量传递给VBA
pass_variable_to_vba('Hello VBA')

Sub YourMacroName()
    Dim myVariable As String
    myVariable = myVariable
    MsgBox myVariable
End Sub