PDA

View Full Version : Solved: powerpoint paste



jay raskol
07-31-2007, 08:33 PM
Hi,

I have posted this in the powerpoint section (http://www.vbaexpress.com/forum/showthread.php?t=14078) but I am actually trying this from an excel worksheet, so I figured it applies to both xections. I have been using some VBA code, from within Excel, to open a powerpoint template and edit graph object datasheets in Powerpoint by pasting in new data from an excel worksheet. This is working fine. I then save the presentation under a new name and close it. When I then go and open the new presentation, the graph has been altered as expected but if I reopen that datasheet the values from the original template now reappear. When I close the presentation I am asked if I would like to save changes. How can I make the changes I make to the presentation permanent?

:banghead:

Kind Regards,

Edited 1-Aug-07 by geekgirlau. Reason: provide link to post in Excel forum

geekgirlau
07-31-2007, 10:01 PM
Just a quick note on protocol Jay,

If you post a message somewhere else, whether it is on VBAX or another board, make sure your post includes a link to that other post. This is known as cross-posting. Whilst you may think you get a better response this way, what can happen is that people spend time trying to help you, only to find that a solution has already been posted somewhere else (and they tend to get a tad annoyed!).

There's an "Insert Link" button that you can use to create the link, or just paste in the URL for the other post. I've added a link to your post above.

jay raskol
08-01-2007, 12:05 AM
Hi geekgirlau,

thanks for bringing me up to speed, that makes perfect and reasonable sense. Thanks for sticking in the link already. And here is the code so far, which is based on some I found on the microsoft site predominantly:

Sub TestOpen()
' initial setup of objects and variables for looping through reports
' oPPTApp is Application Object
' oPPTFile is File instance
' sNameFile is variable for naming reports taken from column A and indexed with x
Dim oPPTApp As PowerPoint.Application
Dim oPPTFile As Presentation
Dim oPPTShape As PowerPoint.Shape
Dim rngNewRange As Excel.Range
Dim rngCompany As Excel.Range
Dim oGraph As Object
Dim sNameFile As String
Set oPPTApp = CreateObject("PowerPoint.Application")
oPPTApp.Visible = msoTrue
' Powerpoint should now be opened with no file opened

' This loop is to create a report for each Company listed in Column A up to Ax
For x = 2 To 4
sNameFile = Worksheets(1).Cells(x, 1).Value
Set oPPTFile = oPPTApp.Presentations.Open("H:\Jason McPherson\HCM\Test.ppt")
oPPTFile.SaveAs "H:\Jason McPherson\HCM\Reports\" & sNameFile & ".ppt"
' We should now have our template opened and saved using an individual name

''''''''' Now we can start working on the individual reports'''''''''
' First we must get the data we need and set it up as we need to paste into our ppt.
' This might be easiest to do in a different sheet each time

'Enter the row number where these figures are stored
Dim intMinRow As Integer
intMinRow = 6
Dim intMaxRow As Integer
intMaxRow = 7
Dim intBenchRow As Integer
intBenchRow = 8

'Slide 1 Graph 1
'Company
Worksheets(2).Cells(1, 2).Value = Worksheets(1).Cells(x, 1).Value
'Company Score
Worksheets(2).Cells(2, 2).Value = Worksheets(1).Cells(x, 2).Value
'Min.
Worksheets(2).Cells(1, 3).Value = "Minimum"
Worksheets(2).Cells(2, 3).Value = Worksheets(1).Cells(intMinRow, 2).Value
'Max.
Worksheets(2).Cells(1, 4).Value = "Maximum"
Worksheets(2).Cells(2, 4).Value = Worksheets(1).Cells(intMaxRow, 2).Value
'Benchmark
Worksheets(2).Cells(1, 5).Value = "Benchmark"
Worksheets(2).Cells(2, 5).Value = Worksheets(1).Cells(intBenchRow, 2).Value

'
' On slide one of Presentation opened, loop through each shape.
'
With oPPTApp.ActivePresentation.Slides(1)
For Each oPPTShape In .Shapes

' Set rngNewRange to this new collection of cells in Sheet 2, select and copy
Set rngNewRange = Worksheets(2).Range("B1:E2")
rngNewRange.Select
rngNewRange.Copy

'
' 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. The ProgID
' is case sensitive.
'
If oPPTShape.OLEFormat.progID = "MSGraph.Chart.8" Then
'
' Set oGraph to the Graph object on the slide.
'
Set oGraph = oPPTShape.OLEFormat.Object
'
' Paste the cell range into the upper leftmost cell of the graph
' datasheet.
'
oGraph.Application.DataSheet.Range("A0").Paste

End If
End If
'
' Select the next shape on the slide.
'
Next oPPTShape
End With

' Save our changes and close this file
oPPTFile.SaveAs "H:\Jason McPherson\HCM\Reports\" & sNameFile & ".ppt"
oPPTFile.SaveAs "H:\Jason McPherson\HCM\Reports\" & sNameFile & ".pps"
oPPTFile.Close

' Increment the cell index (x) ready to open the next report for editing
Next x
End Sub

Bob Phillips
08-01-2007, 12:11 AM
How about posting the two docs for us to see?

jay raskol
08-01-2007, 12:18 AM
Hi,

do you mean the excel and the powerpoint template?

Where do you upload these?

Regards

jay raskol
08-01-2007, 12:38 AM
OK. I found the post page allowing uploads. It allows xls files but not .ppt. What is the normal procedure for uploading ppt files?

Regards

:hi:

Andy Pope
08-01-2007, 01:50 AM
With MSGraph you need to update the object




oGraph.Application.DataSheet.Range("A0").Paste
oGraph.Application.Update
oGraph.Application.Quit

jay raskol
08-01-2007, 05:13 PM
Thanks Andy, that did the trick perfectly. I have had the same issue in other languages but did not know any VBA syntax as it is my first VBA project. Searching didn't find the right statements either. So thanks for the quick solution to this niggly problem. How do I tag your post as the answer?

geekgirlau
08-02-2007, 06:56 PM
Just click on "Solved" under Thread Tools - oh and when you post code, use the VBA tags (there's a VBA button if you like) - makes it easier to read.