Deal with Python & Excel Macro

Imagine a situation where we need to export our data to Excel, it’s easy, right? Of course, we can use Pandas with Openpyxl or Xlsxwriter to create a simple Excel table. What if we want to create a pivot table with all its features, such as sorting, grouping and with the ability to view the source data. To use the following method, you need to install Excel.

Dataframe transfotrmation to Excel pivot table.

First, we have to create file with VBA macro.

Sub CreatePivotTable()

'Call Worksheet_Activate'

'ActivateWorkbook.CreatePivotTable'


'1. Declare Variables
Dim PSheet As Worksheet
Dim DSheet As Worksheet
Dim PCache As PivotCache
Dim PTable As PivotTable
Dim PRange As Range
Dim LastRow As Long
Dim LastCol As Long
Dim strFilter As String

'2. Insert a New Blank Worksheet
On Error Resume Next
Application.DisplayAlerts = False
Worksheets("PivotTable").Delete
Sheets.Add Before:=ActiveSheet
ActiveSheet.Name = "PivotTable"
Application.DisplayAlerts = True
Set PSheet = Worksheets("PivotTable")
Set DSheet = Worksheets("Data")

'3. Define Data Range
LastRow = DSheet.Cells(Rows.Count, 1).End(xlUp).Row
LastCol = DSheet.Cells(1, Columns.Count).End(xlToLeft).Column
Set PRange = DSheet.Cells(1, 1).Resize(LastRow, LastCol)

'4. Define Pivot Cache
Set PCache = ActiveWorkbook.PivotCaches.Create _
(SourceType:=xlDatabase, SourceData:=PRange). _
CreatePivotTable(TableDestination:=PSheet.Cells(2, 2), _
TableName:="SalesPivotTable")

'5. Insert Blank Pivot Table
Set PTable = PCache.CreatePivotTable _
(TableDestination:=PSheet.Cells(1, 1), TableName:="SalesPivotTable")

'6. Create pivot table, by given fields and columns
With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Product")
.Orientation = xlRowField
.Position = 1
End With

With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Category")
.Orientation = xlRowField
.Position = 1
End With

With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Year")
.Orientation = xlColumnField
.Position = 2
End With
With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Sales")
.Orientation = xlDataField
.Function = xlSum
End With

ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Product").Orientation = xlPageField

ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Product").Position = 1

ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Product"). _
EnableMultiplePageItems = True

With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Product")
End With


'7. Format Pivot Table
ActiveSheet.PivotTables("SalesPivotTable").ShowTableStyleRowStripes = True
ActiveSheet.PivotTables("SalesPivotTable").TableStyle2 = "PivotStyleMedium9"

End Sub


'Macro inspired by ExcelChamps
'https://excelchamps.com/vba/pivot-table/

Without going into details, this macro is creating pivot table from data in sheet “Data”. In the sixth paragraph, the columns to be used in the pivot table are declared.

So that you can use the above macro in Python code, we have to add macro to Excel file(Developer -> view code) and save this Excel file as file with the extension “.xlsb”.

Save VBA code in Module1.

Then decompress this file, in “xl” folder there is “vbaProject.bin” file.

Content of “xl” folder in decompression file.

Now, we can create Python code, which add macro to file. Note the name of the sheet, “Data”, is the same as in the macro.

#df - dataframe with data

def add_vba(file_path: str, excel_file: str, vba_file: str):
    """
    :param file_path: path, where file will be saved
    :param excel_file: file name
    :param vba_file: file with vba macro, with .bin extension
    """
    with pd.ExcelWriter(f'{file_path}\{excel_file}.xlsx', engine='xlsxwriter') as writer:
        df.to_excel(writer, sheet_name='Data', index=False)
        workbook = writer.book
        workbook.add_vba_project(vba_file)
        sheet = writer.sheets['Data']
        sheet1 = workbook.add_worksheet('Info')
        sheet1.activate()
        sheet.hide()
        workbook.filename = f'{excel_file}.xlsx'

Then, we have to open Excel file and run macro.

def open_file_and_run_macro(file_path: str, name: str):
        """
        :param file_path: path to file, including file name
        :param name: file name, with .xlsm extension
        """
        xl = win32com.client.Dispatch('Excel.Application')
        wb = xl.Workbooks.Open(os.path.abspath(file_path))
        xl.Application.Run(f'{name}!Module1.CreatePivotTable')
        wb.Close(SaveChanges=1)
        xl.Quit()
        del xl

Pay attention to:

xl.Application.Run(f'{name}!Module1.CreatePivotTable')

Above line contains, Excel file name, module name and name of the function in macro.

Now, if we open file, in “PivotTable” sheet, the pivot table is created.

Requirements:

numpy==1.25.2
pandas==2.1.0
pywin32==306
XlsxWriter==3.1.3