How to Create an Excel Column Chart from Access VBA
Question details
The user wants to automatically generate a 2D Excel column chart using VBA immediately after exporting data from Microsoft Access.
- Product
- Microsoft Access / Microsoft Excel
- Device & OS
- not provided
- Scenario
- Exporting dataset from an Access database to an Excel workbook and visualizing the exported data with a column chart in the same process.
- Observed behavior
- Data is successfully exported using the built-in VBA export commands, but a chart is not automatically created without appending additional Excel automation code.
Ensure that the Microsoft Excel Object Library is enabled in your Access VBA editor's references (Tools > References) if you plan to use early binding for Excel automation.
Use Excel Application Automation in Access VBA
Create an instance of Excel via Access VBA to open the exported workbook and programmatically generate the column chart.
The DoCmd.TransferSpreadsheet command only handles the data export process and cannot create charts by itself. To add a chart, you must utilize Excel automation (COM object) from within your Access VBA code to manipulate the resulting Excel file.
Use DoCmd.TransferSpreadsheet in your Access VBA module to export the desired table or query to a designated Excel file path.
Declare and instantiate an Excel Application object using `Set xlApp = CreateObject("Excel.Application")` so Access can control Excel.
Use `Set xlBook = xlApp.Workbooks.Open("C:\Path\To\Your\ExportedFile.xlsx")` and reference the specific worksheet containing your data.
Define your data range, then insert a chart using `Set xlChart = xlSheet.ChartObjects.Add(Left, Top, Width, Height).Chart`.
Set the chart's source data and change its type using `xlChart.ChartType = 51` (which represents xlColumnClustered). Finally, save the workbook using `xlBook.Save` and gracefully close the Excel application.
Try WPS Office for Seamless Spreadsheet Charting and Macro Support
While Access relies heavily on Microsoft Excel automation, WPS Office provides a lightweight, highly compatible alternative for handling robust spreadsheet tasks, visualizing data, and running VBA macros.
- 1. Download and Install WPS Office: Get the free WPS Office suite from the official website and install it on your computer.
- 2. Open your exported Excel file: Launch WPS Spreadsheets and open the .xlsx file you exported from your database.
- 3. Create charts with ease: Highlight your data range, navigate to the Insert tab, and select Column Chart to visualize your data instantly.
Frequently Asked Questions
Can DoCmd.TransferSpreadsheet create a chart automatically?
No, the TransferSpreadsheet command is strictly designed for moving raw data between Access and Excel. It cannot generate visual elements like charts or pivot tables. You must use Excel VBA automation to accomplish that.
What reference library is needed for Excel automation in Access?
If you are using early binding (e.g., Dim xlApp As Excel.Application), you must enable the 'Microsoft Excel Object Library' in the Access VBA editor by navigating to Tools > References. For late binding, no extra reference is required.
How do I specify a 2D column chart in VBA?
When setting the ChartType property in your VBA code, use the constant xlColumnClustered. If you are using late binding and the constant is not recognized, use its numerical equivalent, which is 51.
