Tag Archives: Charts PowerPoint

Scrolling Excel chart in Powerpoint


EDIT

I have stopped updating this blog. This link can also be found in my Website.

http://www.siddharthrout.com/2012/04/21/scrolling-excel-chart-in-powerpoint/

 

While answering a question in 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

1) Creating the Excel File

2) Setting up your presentation

3) The code


CREATING THE EXCEL FILE


Open a new Excel File and feed in some sample data as shown in the screen shot. I have filled 200 rows with sample data. Once the Data ready, create a line chart. Your Excel File should look like this.

image

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

image

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.

image

Your presentation will now look like this

image

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.

image

And you are done with setting up your presentation


THE CODE


Press ALT + F11 or Click on tab DEVELOPER | VISUAL BASIC on the right hand side to open the visual basic editor.

image

Paste the code given below in the Slide1 code area.

image

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. Smile

Run the presentation by pressing F5 and click on the “Show Chart Button”. You screen show now look like this.

image

If you have made this presentation then this presentation can also be distributed easily.

Hope this helps…