Automating Excel Chart Creation with Python and VBA Using xlwings

Charts make raw numbers visual; using Python you can control basic Excel chart properties, while VBA can manage all attributes. This section shows how to combine Python code with VBA to automate Excel chart creation.

9.1 Automatic Chart Creation

Excel charts are either embedded in worksheets or exist as separate chart sheets. Using a sample data source, the tutorial explains how to create both types via Python and VBA.

9.1.1 Creating an Embedded Chart

The example creates an embedded clustered column chart, sets a title, and displays default data labels for all series. The code imports os , xlwings (as xw ), and the RowCol constant, determines the script directory, builds the path to a sample Excel file, opens the workbook, and accesses Sheet1 .

It then adds a chart at position (0, 50) with width 400 and height 300, sets the data source to range A1:E3, assigns a name, and chooses the clustered column type. Since xlwings' Chart object lacks full series control, the script retrieves the underlying VBA Chart object.

Using VBA, the script specifies that the series are plotted by columns, applies default data labels, and configures label options such as LegendKey , ShowSeriesName , ShowValue , and others. It also makes the chart title visible and sets its text.

Running the Python script generates an embedded chart in the sample workbook as shown in the accompanying screenshots.