Hi all,
I have automated an excel workbook to copy various ranges and charts to a powerpoint template.
This works great on my pc using Excel 2007 & powerpoint 2003
Now a colleague is running this report. They also have Excel 2007 & powerpoint 2003. Although as they are on a thick client on a server, when they run the code it opens powerpoint 2010.
It would appear at somepoint the machine had 2010 installed, then uninstalled but there is a reference somewhere that opens ppt docs using the 2010 version installed on the server.
So my plan was to write some code to open 2003 PP if its available, otherwise use the default version.
Reason being is that when it opens the template on 2010 and copies the charts from excel as enhanced metafiles it changes the colours.
If the user opens PP 2003 before running the code it opens the template in 2003 and all the colours are fine.
Below is the code I have come up with.
It all works fine except when it opens the 2003 version using the shell command, it takes a while to get the object. So it sits open with a new presentation before updating the slide.
So I set the Shell command to VBHide but then need to unhide it when the slide is updated.
It has taken me ages to find this information to get it to work.
Has anyone got any better solutions?
Note: my code is currently opening 2007 version as I am using this on my home pc and don't have 2003 installed
Sub PowerpointTrial() 'This works well slight delay in updating specific version
Dim PPApp As Object
Dim PPPres As Object
Dim PPSlide As Object
Dim TaskID As Double
On Error GoTo ErrorHandler
Position = 0
'Check if specific Powerpoint version is available, if not error handler starts default version
TaskID = Shell("C:\Program Files (x86)\Microsoft Office\Office12\POWERPNT.EXE", vbHide) ' 11 is 2003, 12 is 2007, , vbNormalFocus)
Position = 1 'Version found
'It will error here until it finds the object by using the error handler to waitt or open default application
Set PPApp = GetObject(, "Powerpoint.application")
'now its found, unhide it
TaskID = Shell("C:\Program Files (x86)\Microsoft Office\Office12\POWERPNT.EXE", vbNormalFocus)
'Object found or created so now we can work with it
Fixed:
PPApp.Visible = True
Set PPPres = PPApp.Presentations.Add 'add a new presentation
Set PPSlide = PPPres.Slides.Add(1, 11) 'add a slide
PPSlide.Shapes.Title.TextFrame.TextRange.Text = "Sales Performance - 2012" 'change the title
'do stuff
'clear up before closing
Set PPApp = Nothing
Set PPPres = Nothing
Set PPSlide = Nothing
Exit Sub
ErrorHandler:
If Position = 1 Then 'Specific version found - wait for it to open
Tries = Tries + 1
If Tries < 20 Then
Sleep 500
Resume
Else 'cant locate it so clear error and revert to default
Position = 0
Err.Clear
End If
ElseIf Position = 0 Then 'open default version
Err.Clear
Position = 2
MsgBox "GetObject still failing. Opening default Powerpoint.", _
vbMsgBoxSetForeground
Set PPApp = CreateObject("Powerpoint.Application")
Position = 3
GoTo Fixed:
ElseIf intSection = 2 Then 'Unable to find any Powerpoint version - clear up & close
MsgBox "Unable to open Powerpoint. Closing.", _
vbMsgBoxSetForeground
Set PPApp = Nothing
End If
Set PPApp = Nothing
End Sub
Bookmarks