[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

---

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

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

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

Thanks very much for that

I will give it a try and see what it yields

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

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.

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)