Excel VBA to PPT - one button lick

[zRc55]

I have a table monthly that I received. I have now the January and February data. I have the column now of January and February and the row is the Brand List. By March, I will receive again the data. Is their way that in a click of button the charts update automatically the data in the Powerpoint.. Multiple slide updated.

January

February

March

April

May

June

July

August

September

October

November

December

Brand 1

15

30

Brand 2

12

12

Brand 3

10

12

Brand 4

2

14

Brand 5

5

6

Thank you in advance.

 

[sleek12]

Yes, it is possible to create a PowerPoint presentation with charts that automatically update with new data. Here's how you can do it:

1. Create your charts in Excel using the data you have for January and February.
2. Copy the charts from Excel and paste them into your PowerPoint presentation.
3. With the charts selected in PowerPoint, click on the "Design" tab and select "Edit Data" in the "Data" group. This will open the Excel spreadsheet that is linked to the chart in PowerPoint.
4. In Excel, create a new sheet for the March data.
5. Add the March data to the new sheet and make sure it is formatted in the same way as the January and February data.
6. Go back to the sheet that contains your January and February data and update it with the new data for March.
7. Save the Excel file and close it.
8. Go back to PowerPoint and you should see a prompt asking if you want to update the links. Click "Update" to refresh the charts with the new data.
9. If you want to automate the process of updating the data and charts, you can use VBA code to create a macro that updates the data and refreshes the charts with the new information.
10. To run the macro, you can assign it to a button or a keyboard shortcut in PowerPoint.

With these steps, your charts should update automatically when you receive the new data for March.

 

[zRc55]

Hi, Sleek.

This is the setup I'm working already. When I add the march column, the charts did not update because the cell covered is until Feb only. I need to covered all the cell Jan to Dec.

Do you have an idea on how to do this in vba?


Thank you.

 

[sleek12]

1. Open your PowerPoint presentation and go to the slide with the chart you want to update.
2. Open the Visual Basic Editor by pressing Alt + F11.
3. In the editor, go to Insert > Module to create a new module.
4. In the new module, enter the following code:

Sub UpdateChartRange()

    Dim slide As slide
    Dim chart As chart
    Dim chartDataRange As Range
    Dim lastColumn As Long
    
    ' Set the slide and chart objects
    Set slide = ActivePresentation.Slides(1) ' Replace 1 with the slide index where the chart is located
    Set chart = slide.Shapes("Chart 1").Chart ' Replace "Chart 1" with the name of your chart shape
    
    ' Find the last column with data
    lastColumn = WorksheetFunction.Max(2, slide.Shapes("Chart 1").Chart.SeriesCollection(1).Values.Count)
    
    ' Set the new chart data range
    Set chartDataRange = slide.Shapes("Chart 1").Chart.SeriesCollection(1).Values.Worksheet.Range("B2", "B" & lastColumn)
    
    ' Update the chart data range
    chart.SetSourceData chartDataRange
    
End Sub

1. In the code, replace "Chart 1" with the name of your chart shape. You can find the name of your chart shape by selecting the chart in PowerPoint and looking at the name in the Selection Pane (press Alt + F10 to open it).
2. Save the VBA module and close the editor.
3. Now, whenever you add a new column to your Excel data, open the PowerPoint presentation and run the macro by going to Developer > Macros and selecting "UpdateChartRange". This will update the range of the chart data to include the new column.

Note: This code assumes that your chart is based on a table in Excel, and that the data is organized in columns with the first row containing the brand names. If your data is organized differently, you will need to modify the code accordingly.