-
Feb 10th, 2012, 05:40 AM
#1
Thread Starter
New Member
[RESOLVED] Problem with code generating Powerpoint presentation from Excel 2007 workbook
Can anyone help with this problem?
I have used the code below to open and update a PowerPoint 2007 presentation from an Excel 2007 workbook. The presentation is saved as a pptx file with each slide being a linked copy of a range of Excel cells. When the user clicks on a button in Excel it opens the presentation and forces an update of all fields. This works well on my computer, but having sent the files to somebody else (also using Office 2007) it will not work on her computer.
When she clicks on the button in Excel it opens PowerPoint but fails to open the presentation, instead giving an error message: “Object variable or with block variable not set”. I have checked various obvious problems, but cannot get to the bottom of the problem.
1. The PowerPoint file is in the correct folder on her computer
2. She has macros enabled in Excel
3. She seems to have the appropriate resources checked in Tools…References.
I would be very grateful for any ideas
Code:
Sub SlideCapture()
Dim pApp As Object
Dim pPreso As Object
Dim pSlide As Object
Dim sPreso As String
sPreso = "C:\Outputs\template.pptx"
On Error Resume Next
Set pApp = GetObject(, "PowerPoint.Application")
If Err.Number <> 0 Then
Set pApp = CreateObject("PowerPoint.Application")
pApp.Visible = True
End If
On Error Resume Next
Set pPreso = pApp.Presentations(sPreso)
If Err.Number <> 0 Then
Set pPreso = pApp.Presentations.Open(Filename:=sPreso)
End If
On Error GoTo 0
pPreso.UpdateLinks
End Sub
-
Feb 10th, 2012, 05:52 AM
#2
Frenzied Member
Re: Problem with code generating Powerpoint presentation from Excel 2007 workbook
where exactly does the error say the problem arises?
have you tried putting stops in the code so that you can examin the contents in run time in the imediate window?
you could also add loads of msgboxes in a test version to expose the variables!
here to help
-
Feb 10th, 2012, 06:09 AM
#3
Thread Starter
New Member
Re: Problem with code generating Powerpoint presentation from Excel 2007 workbook
Thanks for the rapid response
Unfortunately the other user is in an office 100 miles away so I'm relying on her feedback. Also I am very much a VBA beginner - I adapted the code from another spreadsheet that someone else had written.
She says that when she tries to run the report, PowerPoint opens but then she gets the error message I described. I cannot reproduce the error on any computer in my office, so I'm rather working in the dark here!
How do I put stops in the code?
J
-
Feb 10th, 2012, 06:32 AM
#4
Frenzied Member
Re: Problem with code generating Powerpoint presentation from Excel 2007 workbook
like this
Code:
Sub SlideCapture()
Dim pApp As Object
Dim pPreso As Object
Dim pSlide As Object
Dim sPreso As String
sPreso = "C:\Outputs\template.pptx"
On Error Resume Next
Set pApp = GetObject(, "PowerPoint.Application")
stop 'this stop will allow you to look at the pApp properties in the immediate window
'because she is so far away you will need to see what you can access in the immediate window
'and add these properties to a message box so that she can report back to you!
' if for example you could print the property pApp.fish and get back a suitable value you would add a msgbox like this
msgbox "fish = "& pApp.fish
'your user can now report back to you about "fish"
'your other option is to open a file and logg everything - she would then send the log file back to you - that might involve a little more programming knowledge on your part
If Err.Number <> 0 Then
Set pApp = CreateObject("PowerPoint.Application")
pApp.Visible = True
End If
On Error Resume Next
Set pPreso = pApp.Presentations(sPreso)
If Err.Number <> 0 Then
Set pPreso = pApp.Presentations.Open(Filename:=sPreso)
End If
On Error GoTo 0
pPreso.UpdateLinks
End Sub
hope that's not too confusing - difficult to seewhat it will look like in the forum.
PM if you need more
here to help
-
Feb 10th, 2012, 06:39 AM
#5
Thread Starter
New Member
Re: Problem with code generating Powerpoint presentation from Excel 2007 workbook
Thanks very much for that
I will give it a try and see what it yields
-
Feb 10th, 2012, 06:47 AM
#6
Frenzied Member
Re: Problem with code generating Powerpoint presentation from Excel 2007 workbook
remember do not leave the stops in if you are sending it to some one unless they can work in the vba environment
just give them loads of messages and on reflection the logging would be a really good way to go because you could compare your log against their log for a set of pre-agreed actions!
help with logging if needed
-
Feb 15th, 2012, 04:48 AM
#7
Thread Starter
New Member
Re: Problem with code generating Powerpoint presentation from Excel 2007 workbook
Thank you so much for all your help with this one.
After sorting out a log file over the weekend and running through the process with the user, I finally got to the root of the problem: despite her protestations to the contrary, she had installed the programme on a network drive, rather than her C:\ drive! Unsurprisingly the subroutine could open PowerPoint but was then unable to find the ppt file so gave the error message.
-
Feb 15th, 2012, 06:05 PM
#8
Frenzied Member
Re: Problem with code generating Powerpoint presentation from Excel 2007 workbook
well done - i take it logging is going to be a big think in your computing arsenal from now on!
you can of course make all of your files relate to each other in a non drive specific manner.
if it was my post that helped please rate it and close your thread, using the tools at the top.
here to help ( and all we ask for is recognition of a job well done)
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|