Edit xlsm file containing macro and activex objects using python

Question

The final goal is to append data into an xlsm file which is containing vba script and a activex listbox. Using python/pandas is preferred. For starters i would like to just open the xlsm file, add an empty sheet and close it again without the VBA code giving me errors due to not having a listbox.

The activex listbox gives me troubles, since it is not "transfered" and the VBA script yields an error when trying to use the listbox. The VBA code looks just fine, after running the python script, but it crashes when setting the 'xLstBox' variable.

Possible fix i can see but not able to implement are:

1. having python edit the file without messing with the activex objects
2. if the VBA script could generate the listbox.

My python code:

from openpyxl import load_workbook 
xlsx_path = 'excel_file.xlsm'
wb = load_workbook(filename=xlsx_path, keep_vba=True)
wb.create_sheet('sheetname')
wb.save(xlsx_path)

My VBA code in the prefabricated xlsm file:

Public PreviousActiveCell As Range

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim xSelLst As Variant, I As Integer

Set xLstBox = ActiveSheet.ListBox1

Static pPrevious As Range
Set PreviousActiveCell = pPrevious
Set pPrevious = ActiveCell

If Not Intersect(Target, Range("A2:A999999")) Is Nothing Then
    If xLstBox.Visible = False Then
        xLstBox.Visible = True
        xLstBox.Top = ActiveCell.Row * 15
        xLstBox.Left = 0
    End If
    
Else
    If xLstBox.Visible = True Then
        xLstBox.Visible = False
           
            For I = xLstBox.ListCount - 1 To 0 Step -1
                If xLstBox.Selected(I) = True Then
                xSelLst = xLstBox.List(I) & "," & xSelLst
                End If
            Next I
        
            If xSelLst <> "" Then
                PreviousActiveCell = Mid(xSelLst, 1, Len(xSelLst) - 1)
            End If
            
        For I = xLstBox.ListCount - 1 To 0 Step -1
            ListBox1.Selected(I) = False
        Next I
            
    End If

End If

End Sub

Answer 1

Openpyxl isn't guaranteed to manage vba components so you may have issues with xlsm sheets that contain these objects.
If you are running on Windows (or perhaps Mac) and can use Xlwings this may help. Xlwings needs Excel installed locally.
Your first test for example, adding a sheet should not impact your listbox.

import xlwings as xw

xlsx_path = 'excel_file.xlsm'

with xw.App() as app:
    wb = xw.Book(xlsx_path)
    wb.sheets.add('sheetname', after='Sheet1')

    wb.save(xlsx_path)
    wb.close()