Export Table Data from Multiple Excel Sheets to PowerPoint Using VBA

Previously, I shared a VBA code demonstrating how to export multiple charts from an Excel worksheet to PowerPoint. In this article, I'll guide you through exporting table data from multiple sheets in Excel to PowerPoint efficiently using VBA.

Learn how to automate your workflow and create professional presentations with ease, step-by-step. This VBA approach streamlines the process, allowing you to transfer data quickly across applications.

Export table data in Excel to PowerPoint using VBA

1) Prepare Your Excel Sheets

Organize your table data across multiple Excel sheets. Make sure your data is clean and structured for easy transfer. See the image. 👇

I have data spread across multiple sheets, with each sheet containing sales data for a specific year.

Table data in Excel

Note: Make sure you have save the Excel file in ".xlsx" format.

2) Add an ActiveX Button

As shown in the image above, I have placed an ActiveX Button Control beneath the table in the first sheet. Clicking this button triggers the Macro to execute.

3) Open VBA Editor

To open the VBA Editor in Excel, follow these steps:

Press Alt + F11 on your keyboard. This will directly open the VBA Editor. Alternatively, you can click on the Developer tab in Excel's Ribbon, then choose Visual Basic.

In the VBA Editor, you can view and write your VBA code.

4) Write the Code

In the VBA Editor, locate the Project Explorer window and double-click on 'Sheet1' to open it. Then, write your code directly into the editor or simply copy and paste the code provided below.

Option Explicit

Private Sub CommandButton1_Click()
    ExportDataToPpt
End Sub

Sub ExportDataToPpt()
    Dim objPPT As Object
    Dim objPptPre As Object
    Dim objPPTSlides As Object
    Dim objWS As Worksheet
    Dim iNdx As Integer
    Dim rng As Range
    Dim row As Range
    Dim xOffset As Single
    Dim yOffset As Single
    
    ' Create a PowerPoint application object.
    Set objPPT = CreateObject("PowerPoint.Application")
    objPPT.Visible = True
    Set objPptPre = objPPT.Presentations.Add

    iNdx = 1    ' Slide index.

    ' Loop through worksheets.
    For Each objWS In ActiveWorkbook.Worksheets
        Set rng = objWS.Range("A1:E10")     ' Define the range of data to copy.
        
        If Not IsEmpty(rng) Then
            ' Add a slide for each worksheet.
            Set objPPTSlides = objPptPre.Slides.Add(iNdx, ppLayoutBlank)    ' Create a blank slide.
            
            ' Initialize offsets for positioning textboxes.
            xOffset = 50
            yOffset = 50
            
            Dim rowData As String
            Dim cell
           
            ' Loop through each row in the range.
            For Each row In rng.Rows
                rowData = ""    ' Reset rowData for each row.
            
                ' Loop through each cell in the row.
                For Each cell In row.Cells
                    rowData = rowData & cell.Value & vbTab  ' Concatenate cell values with a tab separator.
                Next cell
            
                With objPPTSlides.Shapes.AddTextbox(msoTextOrientationHorizontal, _
                    xOffset, yOffset, 500, 30)  ' Adjust width and height as needed.
                    .TextFrame.TextRange.Text = rowData
                End With
                
                ' Adjust the position for the next row's textbox.
                yOffset = yOffset + 40  ' Increment yOffset to move down.
                If yOffset > 400 Then
                    yOffset = 50        ' Reset yOffset.
                    xOffset = xOffset + 520     ' Move to the right.
                End If
            Next row
            
            iNdx = iNdx + 1     ' Increment slide index.
        End If
    Next objWS

    MsgBox "Data exported to PowerPoint."
    
    ' Clean up objects.
    Set objPPT = Nothing
    Set objPptPre = Nothing
    Set objPPTSlides = Nothing
End Sub

What This Macro Does?

It automates the process of transferring data from multiple Excel worksheets to a PowerPoint presentation. Each worksheet gets its own slide, and the data from the specified range (A1:E10) is displayed as text on the slide.

← Previous