Results 1 to 8 of 8

Thread: [RESOLVED] Problem with code generating Powerpoint presentation from Excel 2007 workbook

  1. #1

    Thread Starter
    New Member
    Join Date
    Feb 2012
    Posts
    4

    Resolved [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

  2. #2
    Frenzied Member
    Join Date
    Nov 2010
    Posts
    1,470

    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

  3. #3

    Thread Starter
    New Member
    Join Date
    Feb 2012
    Posts
    4

    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

  4. #4
    Frenzied Member
    Join Date
    Nov 2010
    Posts
    1,470

    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

  5. #5

    Thread Starter
    New Member
    Join Date
    Feb 2012
    Posts
    4

    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

  6. #6
    Frenzied Member
    Join Date
    Nov 2010
    Posts
    1,470

    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

  7. #7

    Thread Starter
    New Member
    Join Date
    Feb 2012
    Posts
    4

    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.

  8. #8
    Frenzied Member
    Join Date
    Nov 2010
    Posts
    1,470

    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
  •  



Click Here to Expand Forum to Full Width