[PowerPoint VBA] How to Open an Excel File and List All Charts

Background

When using VBA to automate tasks in PowerPoint, I encountered a requirement: “I want to open an external Excel file and retrieve a list of all charts contained within it.”

To achieve this, I wrote a code that controls Excel from PowerPoint VBA and collects charts from all worksheets.

VBA Code Implementation

The following code can be executed within PowerPoint VBA.

Sub GetChartListFromExcel()
    ' Define the file path (Please change this to your actual file path)
    Dim filePath As String
    filePath = "C:\Users\User\Documents\test_charts.xlsx"

    ' Create Excel application
    Dim xlApp As Object
    Dim xlWorkbook As Object
    Dim xlWorksheet As Object
    Dim xlChartObject As Object
    Set xlApp = CreateObject("Excel.Application")
    xlApp.Visible = False

    ' Open Excel file
    Set xlWorkbook = xlApp.Workbooks.Open(filePath)

    ' Create a list of charts
    Dim chartList As Collection
    Set chartList = New Collection
    
    ' Loop through all worksheets and all charts
    For Each xlWorksheet In xlWorkbook.Worksheets
        For Each xlChartObject In xlWorksheet.ChartObjects
            chartList.Add xlChartObject
        Next xlChartObject
    Next xlWorksheet

    ' Display the list of charts
    Dim chartNames As String
    chartNames = "List of charts in the selected Excel file:" & vbCrLf
    
    Dim i As Integer
    For i = 1 To chartList.Count
        chartNames = chartNames & "Chart " & i & ": " & chartList(i).Name & vbCrLf
    Next i

    MsgBox chartNames, vbInformation

    ' Close Excel
    xlWorkbook.Close False
    xlApp.Quit
    Set xlApp = Nothing
End Sub

Code Explanation

• filePath: Specify the path of the Excel file you want to check as a string.
• Loop Processing: The code loops through all Worksheets in the workbook and collects every ChartObject found.
• Collection: The collected charts are stored in a Collection object named chartList.
• MsgBox: Finally, the names of the charts are displayed in a message box for verification.

Use Cases

This code is useful in the following situations:

• When you want to check the structure of charts in a separate file directly from PowerPoint.
• To check if a chart exists or to get its name before running an automated paste process.
• To summarize chart configurations from multiple Excel files.

Summary

• You can open Excel from PowerPoint VBA and access chart objects in each sheet.
• Using ChartObjects allows you to easily list embedded charts.
• Storing them in a Collection and outputting names via MsgBox is a quick way to verify contents.

This function works well for simple chart listing. You can expand this code to perform further processing on the retrieved charts depending on your project needs.