python调用excel vba函数

一、背景

　　存储数据为.xlsb类型的文件时，用python读取为dataframe数据格式时，非常麻烦，目前没有发现python友好的处理库。因此通过在excel上用vba对.xlsb文件读取转换为.xlsx后，再用python的pandas库读取.xlsx文件为dataframe数据格式。另外python也可以同时调用vba宏，然后直接运行，减少了再excel上手动运行操作，解决了跨软件运行。

二、excel上vba编写

• 将"D:\vba\data\拉美"路径下的.xlsb文件转换为.xlsx文件

Dim sTemp As String
Sub conv_chatfields()

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Application.Visible = False
    '文件需要转换的路径：D:\vba\data\拉美
    all_path = Array("D:\vba\data\拉美")
    country_name = Array("拉美")
    For i = LBound(all_path) To UBound(all_path)
        For j = LBound(country_name) To UBound(country_name)
            Path = all_path(i) & country_name(j) & "\"
            Filename = Dir(Path & "*.xlsb")
            Do While Filename <> "":
                If Filename = "" Then
                    Exit Do
                End If
                
                '判断后缀名分隔符.的位置
                sTemp = Filename
                iPos = Len(sTemp) - VBA.InStr(1, VBA.StrReverse(sTemp), ".")
                If iPos <> 0 Then
                    sTemp = Mid(sTemp, 1, iPos)
                    Workbooks.Open Filename:=Path & Filename
                    
                    ActiveWorkbook.SaveAs Filename:=Path & sTemp & ".xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
                    ActiveWindow.Close
                End If
                Filename = Dir
            Loop
        Next
    Next
    Application.ScreenUpdating = True
End Sub

三、python调用vba运行

• 调用vba程序。需要安装win32com库

import win32com.client 
print('转换开始'
#####调用vba程序。需要安装win32com库
xls = win32com.client.Dispatch("Excel.Application")
xls.workbooks.Open(r"D:\李耀瑞\vba\三大渠道自动化脚本\四大渠道运行\demo\文件格式转换.xlsm")  ##存储vba代码的文件
try:
    xls.Application.Run('conv_chatfield.conv_chatfield')  ##开始调用vba宏
except Exception as e:
    print(e)
xls.Application.Quit()

print('转换完成！')