Calling a VBA procedure from Python

Question

My Python code runs without an error but without running the VBA procedure.

The procedure works when run from inside the Excel file. The macro is in a standard module.

My Python (v3.10.6) code running on PyCharm (v221.6008.17) calling the VBA (v7.1.1126) procedure:

    from win32com.client.dynamic import Dispatch
    
    # Get the Excel Application COM object
    xl = Dispatch('Excel.Application')
    
    xl.Application.Run("IDMB.xlsm!PythonModules.EpisodesSort")

My VBA macro EpisodesSort:

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

The StartUp procedure called above is in the VBA module 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

Essentially, your question is more a processing one than a programming. Avoid too many interactions on the same open workbook where you segment processes between Python and Excel without conflicting instances.

Specifically, have Python handle two tasks:

1. Pandas processing: Export your data, then close all objects, particularly ExcelFile. Do note: pandas with underlying engines, openpyxl or xslxwriter, does not yet support saving data to a macro-enabled workbook (i.e., xlsm), so consider following this solution by saving your VBA in a code .bin file and re-importing the code after data export. See other workarounds at bottom of this open pandas ticket.

2. Excel processing: Launch a COM connection that opens Excel workbook of new data, runs the needed macro, saves changes, and closes workbook. Alternatively, to closing, make background Excel process visible and then release the COM object. Also, consider try/except to ensure processes cleanly release:

   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