从Python调用VBA过程

Question

我的Python代码运行时没有出现错误，但没有运行VBA过程。

当从Excel文件内部运行时，该过程可以工作。宏位于标准模块中。

我的Python(v3.10.6(代码在PyCharm(v221.6008.17(上运行，调用VBA(v7.1.1126(过程：

from win32com.client.dynamic import Dispatch

# Get the Excel Application COM object
xl = Dispatch('Excel.Application')

xl.Application.Run("IDMB.xlsm!PythonModules.EpisodesSort")

我的VBA宏集排序：

Option Explicit
Public Sub EpisodesSort()

Dim sRange$

Call StartUp(Array(CEPISODES))

With Episodes.Sheet2
.Sort.SortFields.Clear
sRange = "A1:A" & Episodes.LastUsedRow
.Sort.SortFields.Add2 Key:=Episodes.Sheet2.Range(sRange), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With .Sort
sRange = "A2:" & Episodes.LastUsedCol.Alphabetic & Episodes.LastUsedRow
.SetRange Episodes.Sheet2.Range(sRange)
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End With

End Sub

上面调用的启动过程位于VBA模块CommonModules:中

Public Const CEPISODES = "Episodes"
' some public variables omitted
Public varOldValue As Variant
Public wbMain As Workbook
Public Action As cSheet, Actors As cSheet, Artists As cSheet, Build As cSheet, Code As cSheet, Code2 As cSheet, Code3 As cSheet, Delete2 As cSheet, Episodes As cSheet, Incomplete As cSheet, Link As cSheet, Lists2 As cSheet, Login As cSheet, LookUp As cSheet, LostActors As cSheet, Movie As cSheet, MusicTorrentDeletes As cSheet, Ratings2 As cSheet, Reasons2 As cSheet, ShowTitles As cSheet, TorrentTypes As cSheet, Tracks As cSheet, Temp As cSheet, User2 As cSheet, wsTemp As cSheet
Public Sys2 As cSys
Public user As cUser
Public Sub StartUpInitial()
On Error GoTo Err_Handler

Set Sys2 = New cSys
Set user = New cUser

Set Temp = New cSheet

Exit Sub
Exit_Label:
On Error Resume Next
Application.Cursor = xlDefault
Application.ScreenUpdating = True
Application.CutCopyMode = False
Application.Calculation = xlCalculationAutomatic
Exit Sub
Err_Handler:
MsgBox Err.Description, vbCritical, "StartUpInitial"
Resume Exit_Label
End Sub

Public Sub StartUp(arrTab As Variant, Optional ExternalWB As Workbook, Optional FindLinks As Boolean)
On Error GoTo Err_Handler

Dim i%, iTab%
Dim FindLinks2 As Boolean
Dim wb As Workbook

'disable excel vba feature to accelerate processing
Application.Cursor = xlDefault
Application.ScreenUpdating = False
Application.EnableEvents = True
Application.CutCopyMode = False
Application.Calculation = xlCalculationAutomatic

aCategory = Array("music", "tv", "xxx")

aMonth = Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec")

aFullMonth = Array("January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December")

aRemoveChar = Array(".", "–", "-", ",", ";", "'", "‘", """", "/", ":")

aPunctuation = Array(" ", "&")
aPunctuation2 = Array("", "AND")

iToday = Now()
sWaitFlag = ""
WaitedAfterPrevBrowser = False

If IsMissing(ExternalWB) Then
Set wbMain = ThisWorkbook
ElseIf ExternalWB Is Nothing Then
Set wbMain = ThisWorkbook
Else
Set wbMain = ExternalWB
End If

If IsMissing(FindLinks) Then
FindLinks2 = False
Else
FindLinks2 = FindLinks
End If

If Build Is Nothing And Code Is Nothing And Code2 Is Nothing And Code3 Is Nothing And LookUp Is Nothing Then
Call StartUpInitial
End If

If Not IsNull(arrTab) Then
For iTab = 0 To UBound(arrTab)
Select Case arrTab(iTab)
Case CEPISODES
Set Episodes = New cSheet

With Episodes
Set .Sheet2 = wbMain.Sheets(CEPISODES)

.SearchLine = Array(1)
.BuildHeaderDetails

.Heading = Array("Key", "Link")

.BlankLinesAllowed = 1
.ColumnNotRow = True

'identify columns in source data tab
.IdentifyHeading
End With
End Select
Next iTab
End If

Exit Sub
Exit_Label:
On Error Resume Next
Application.Cursor = xlDefault
Application.ScreenUpdating = True
Application.CutCopyMode = False
Application.Calculation = xlCalculationAutomatic
Exit Sub
Err_Handler:
MsgBox Err.Description, vbCritical, "StartUp"
Resume Exit_Label
End Sub

Answer 1

本质上，您的问题与其说是编程，不如说是处理问题。避免在同一个打开的工作簿上进行过多的交互，在该工作簿中，您可以在Python和Excel之间对进程进行分段，而不会出现冲突实例。

具体来说，让Python处理两个任务：

1. Pandas处理：导出数据，然后关闭所有对象，特别是ExcelFile。请注意：具有底层引擎openpyxl或xslxwriter的panda还不支持将数据保存到启用宏的工作簿(即xlsm(，因此请考虑遵循此解决方案，将VBA保存在代码.bin文件中，并在数据导出后重新导入代码。请参阅这张打开的熊猫票底部的其他解决方案。

2. Excel处理：启动COM连接，打开包含新数据的Excel工作簿，运行所需的宏，保存更改并关闭工作簿。或者，要关闭，请使后台Excel进程可见，然后释放COM对象。此外，考虑try/except以确保流程干净地发布：

   try: 
   xl = Dispatch('Excel.Application') 
   wb = xl.Workbooks.Open("IDMB.xlsm") 
   # RUN MACRO
   xl.Application.Run("IDMB.xlsm!PythonModules.EpisodesSort")        
   wb.Save()
   # MAKE BACKGROUND PROCESS VISIBLE
   xl.Visible = True
   # CLOSE WORKBOOK AND QUIT APP
   # wb.Close(True)
   # xl.Quit()
   except Exception as e: 
   print(e) 
   finally: 
   # RELEASE OBJECTS (COUNTERPART OF VBA: Set wb = Nothing)
   wb = None
   xl = None
   del wb
   del xl