Scrolling Excel chart in Powerpoint
While answering a question at stackoverflow.com, I came across an interesting question. “How do we create a scrollable Excel chart in PowerPoint”? In Excel it is very easy to make a scrollable chart using ScrollBar – Form Control. But how do we do it in PowerPoint?
I will cover this in 3 parts:
- Creating the Excel File
- Setting up your presentation
- he code
CREATING THE EXCEL FILE
Open a new Excel File and feed in some sample data as shown in the screenshot. I have filled 200 rows with sample data. Once the Data ready, create a line chart. Your Excel File should look like this:
Save the file to say, C: or any other location of your choice.
SETTING UP YOUR PRESENTATION
Open MS PowerPoint and go to slide 1. Click on the tab “Insert” → “Object“.
You will be presented with “Insert Object Dialog Box“. Select the “Create From File” Option and then click on the “Browse” button. Select the Excel file that we had created earlier and select an icon by clicking “Display as Icon” and click on “OK“.
Your presentation will now look like this:
Next navigate to the Developer Tab. (See Snapshot) In the developer tab, click on “Additional Controls” button and select “Microsoft Web Browser” and insert that control in your respective slide. Ensure that it hides the Excel Object that we inserted earlier. Size it accordingly. Also place a Command Button. Name it “Show Chart” or anything else what you feel is right.
And you are done with setting up your presentation.
Press ALT + F11 or Click on tab “Developer” → “Visual Basic” on the right hand side to open the Visual Basic editor.
Paste the code given below in the Slide1 code area:
Option Explicit Private Declare Function GetTempPath Lib "kernel32" Alias "GetTempPathA" _ (ByVal nBufferLength As Long, ByVal lpBuffer As String) As Long Private Const MAX_PATH As Long = 260 Dim ImageFile As String Private Sub CommandButton1_Click() ExtractToTemp WebBrowser1.Navigate ImageFile End Sub Sub ExtractToTemp() Dim oSl As PowerPoint.Slide Dim oSh As PowerPoint.Shape Dim oXLApp As Object, oXLWB As Object, oXLSht As Object Dim mychart As Object Set oSl = ActivePresentation.Slides(1) Set oSh = oSl.Shapes(1) With oSh.OLEFormat.Object.Sheets(1) .Shapes(1).Copy End With '~~> Establish an EXCEL application object On Error Resume Next Set oXLApp = GetObject(, "Excel.Application") If Err.Number <> 0 Then Set oXLApp = CreateObject("Excel.Application") End If Err.Clear On Error GoTo 0 oXLApp.Visible = False '~~> Open the relevant file Set oXLWB = oXLApp.Workbooks.Add Set oXLSht = oXLWB.Worksheets(1) oXLSht.Paste '~~> Save Picture Object ImageFile = TempPath & "Tester.jpg" If Len(Dir(ImageFile)) > 0 Then Kill ImageFile Set mychart = oXLSht.ChartObjects(1).Chart mychart.Export FileName:=ImageFile, FilterName:="jpg" '~~> Wait till the file is saved Do If FileExists(ImageFile) = True Then Exit Do DoEvents Loop oXLWB.Close SaveChanges:=False oXLApp.Quit Set oXLWb = Nothing Set oXLApp = Nothing End Sub '~~> Get User's TempPath Function TempPath() As String TempPath = String$(MAX_PATH, Chr$(0)) GetTempPath MAX_PATH, TempPath TempPath = Replace(TempPath, Chr$(0), "") End Function '~~> Function tot check if file exists Public Function FileExists(strFullPath As String) As Boolean On Error GoTo Whoa If Not Dir(strFullPath, vbDirectory) = vbNullString Then FileExists = True Whoa: On Error GoTo 0 End Function
And you are done.
Run the presentation by pressing F5 and click on the “Show Chart Button“. You screen show now look like this:
If you have made this presentation then this presentation can also be distributed easily.
Hope this helps.