pasting from excel to powerpoint datasheet

I wrote a macro that updates the data of a powerpoint graph. It basically selects a range in an excel table, copies it, and then goes to the powerpoint datasheet of the graph and then pastes it (see attached code). However, the bars of the graph disappear after the auto-paste by macro. I believe it is a formatting issue because when I do the same process manually (manual copy/paste) it works fine. Or after the macro-paste if I edit the cell in datasheet and simply press ENTER, it corrects the data and bar shows up. But ofcourse it defeats the purpose of automating the whole process.. What can I do to avoid this and make the auto-paste work correctly?.. Thanks in advance.

Please see attached illustration.gif. The two columns were pasted by macro, hence no bars showed up.. Then I manually edited the first column (just double clicked on it and then hit ENTER) and the bar showed.
Sub Update_PPT_Graphs()
 
    'declare variables
    Dim oPPTApp As PowerPoint.Application
    Dim oPPTShape As PowerPoint.Shape
    Dim rngNewRange As Excel.Range
    Dim oGraph As Object
 
    'get the open ppt file
    Set oPPTApp = GetObject(, "Powerpoint.Application")
    
    'Set PowerPoint to be Visible.
    oPPTApp.Visible = msoTrue
 
    'select the sheet
    Sheets("Sheet 1").Select
    
    'Set range, select and copy
    Set rngNewRange = ActiveSheet.Range("A1:Z99")
    rngNewRange.Select
    rngNewRange.Copy
 
 
    'work on the slide 12
    With oPPTApp.ActivePresentation.Slides(12)
 
        For Each oPPTShape In .Shapes
        
            'Check to see whether shape is an OLE object.
            If oPPTShape.Type = msoEmbeddedOLEObject Then
 
                'Check to see whether OLE object is a Graph 2000 object.
                If oPPTShape.OLEFormat.progID = "MSGraph.Chart.8" Then
 
                    'Set oGraph to the Graph object on the slide.
                    Set oGraph = oPPTShape.OLEFormat.Object
 
                    'clear datasheet
                    oGraph.Application.DataSheet.Range("00:Z99").Clear
                    
                    'Paste the cell range into the upper leftmost cell of the graph
                    oGraph.Application.DataSheet.Range("00").Paste
 
 
                End If
 
            End If
 
        Next oPPTShape
    End With
    
    
End Sub

Open in new window

gboybekAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

GlennaShawCommented:
Check out these links:
http://www.pptfaq.com/FAQ00496.htm

BTW, PowerPoint 2007 uses Excel as it's graphing engine:
http://www.pptfaq.com/FAQ00872.htm
0
StellanRosengrenCommented:
Hi gboybek,
I will try to help you. I have never worked with MS Graph before so I had to download the Visual Basic Reference first. You can find it here:
http://msdn.microsoft.com/en-us/library/aa171197(office.11).aspx

There I found a Refresh method of the Chart object. I have not tried it but it sounds like something that could help.
Please try
oGraph.Refresh
after you have pasted.

Kind regards,
Stellan
0
StellanRosengrenCommented:
Hi again gboybek,

Here is your code with my proposed amendment.

Kind regards,
Stellan
Sub Update_PPT_Graphs()
 
    'declare variables
    Dim oPPTApp As PowerPoint.Application
    Dim oPPTShape As PowerPoint.Shape
    Dim rngNewRange As Excel.Range
    Dim oGraph As Object
 
    'get the open ppt file
    Set oPPTApp = GetObject(, "Powerpoint.Application")
    
    'Set PowerPoint to be Visible.
    oPPTApp.Visible = msoTrue
 
    'select the sheet
    Sheets("Sheet 1").Select
    
    'Set range, select and copy
    Set rngNewRange = ActiveSheet.Range("A1:Z99")
    rngNewRange.Select
    rngNewRange.Copy
 
 
    'work on the slide 12
    With oPPTApp.ActivePresentation.Slides(12)
 
        For Each oPPTShape In .Shapes
        
            'Check to see whether shape is an OLE object.
            If oPPTShape.Type = msoEmbeddedOLEObject Then
 
                'Check to see whether OLE object is a Graph 2000 object.
                If oPPTShape.OLEFormat.progID = "MSGraph.Chart.8" Then
 
                    'Set oGraph to the Graph object on the slide.
                    Set oGraph = oPPTShape.OLEFormat.Object
 
                    'clear datasheet
                    oGraph.Application.DataSheet.Range("00:Z99").Clear
                    
                    'Paste the cell range into the upper leftmost cell of the graph
                    oGraph.Application.DataSheet.Range("00").Paste
 
                    'Refresh the graph
                    oGraph.Refresh
 
 
                End If
 
            End If
 
        Next oPPTShape
    End With
    
    
End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.