This script demonstrates how to create a new Excel workbook, add data, insert a VBA macro, and execute it using the xlwings library in Python. The macro creates a 3D pie chart from the provided data.

create_3d_pie_chart.py

"""
Author: Sven Bosau
YouTube Channel: https://youtube.com/@codingisfun
Website: https://pythonandvba.com

Does this help you? Consider buying me a coffee here:
https://pythonandvba.com/coffee-donation

This script creates a new Excel workbook, adds data, inserts a VBA macro, and executes it using the xlwings library. The VBA macro generates a 3D pie chart from the data.
"""


import xlwings as xw

# Step 1: Create a new .xlsm workbook and add data to it
with xw.App(visible=False) as app:
    wb = app.books.add()
    wb.save('3D_Pie_Chart.xlsm')
    
    sheet = wb.sheets[0]
    data = [
        ['Category', 'Value'],
        ['A', 30],
        ['B', 20],
        ['C', 50]
    ]
    sheet.range('A1').value = data
    
    # Step 2: Add the VBA macro
    vba_code = """
    Sub Create3DPieChart()
        Dim ws As Worksheet
        Set ws = ThisWorkbook.Sheets(1)
        Dim chartObj As ChartObject
        Set chartObj = ws.ChartObjects.Add(Left:=100, Width:=375, Top:=50, Height:=225)
        With chartObj.Chart
            .SetSourceData Source:=ws.Range("A1:B4")
            .ChartType = xl3DPie
            .ApplyDataLabels
        End With
    End Sub
    """
    vba_module = wb.api.VBProject.VBComponents.Add(1)  # 1 stands for a VBA module
    vba_module.CodeModule.AddFromString(vba_code)
    
    # Step 3: Save and close the workbook
    wb.save()
    wb.close()

# Step 4: Reopen the workbook and run the macro
with xw.App(visible=False) as app:
    wb = app.books.open('3D_Pie_Chart.xlsm')
    macro = wb.macro('Create3DPieChart')
    macro()  # Execute the macro
    
    # Step 5: Save and close the workbook again
    wb.save()
    wb.close()