Powerpoint vba - copying table from excel

Question

I have been trying to write a macro that updates a presentation through powerpoint vba using tables from excel.

Here is what I am doing

The code first deletes all pictures in the presentation,
Then opens the excel workbook, copys a named range and pastes into the correct slide.

This exact code was working fine two days ago and is now saying the object is out of range for copying the range "PL". Any help or tips would be great as this is my first time using powerpoint vba.

valnPath = "G:\valnpath\"
PriorPath = "G:\Priorpath\"

Dim xlApp As Object
Dim xlWorkBook As Object
Dim XL As Excel.Application
Dim PPSlide As PowerPoint.Slide

Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
xlApp.DisplayAlerts = False
xlApp.AskToUpdateLinks = False

Set xlWorkBook = xlApp.Workbooks.Open(valnPath & "Presentation Tables 1208.xlsx", True, False)
Set XL = GetObject(, "Excel.Application")
    XL.DisplayAlerts = False
    XL.AskToUpdateLinks = False
    XL.Range("PL").Copy

 ActivePresentation.Slides(3).Select
 Application.ActiveWindow.View.PasteSpecial DataType:=ppPasteEnhancedMetafile

Set XL = GetObject(, "Excel.Application")
    XL.DisplayAlerts = False
    XL.AskToUpdateLinks = False
    XL.Range("AvE").Copy

 ActivePresentation.Slides(5).Select
 Application.ActiveWindow.View.PasteSpecial DataType:=ppPasteEnhancedMetafile

 Set XL = GetObject(, "Excel.Application")
 XL.Quit

Am I doing anything wrong? Please help me with this,

Thank you

Is there a reason why you're using GetObject() to get a second reference to Excel after you already opened a new instance using CreateObject() ? If there are multiple instances of excel open then there's no guarantee as to which instance will be returned by GetObject() (and it could even return a 'hidden' instance), so you'd be better off sticking with your original xlApp reference. — Tim Williams
I dont have a particular reason why I am doing this, as mentioned, this is my first attempt at powerpoint vba - I thought that I would have to 'call' the excel spreadsheet as such to copy from it. So instead of XL.Range("PL").Copy, I would use xlApp.Range("PL").Copy Thanks for your input, Ill try this. — amymon87

Tim Williams Tim Williams · Accepted Answer · 2012-10-26T00:51:08

Try this:

valnPath = "G:\valnpath\"
PriorPath = "G:\Priorpath\"

Dim xlApp As Excel.Application
Dim xlWorkBook As Excel.Workbook
Dim PPSlide As PowerPoint.Slide

Set xlApp = CreateObject("Excel.Application")
With xlApp
   .Visible = True
   .DisplayAlerts = False
   .AskToUpdateLinks = False
End With

Set xlWorkBook = xlApp.Workbooks.Open(valnPath & _
                       "Presentation Tables 1208.xlsx", True, False)

xlApp.Range("PL").Copy
ActivePresentation.Slides(3).Select
Application.ActiveWindow.View.PasteSpecial DataType:=ppPasteEnhancedMetafile

xlApp.Range("AvE").Copy
ActivePresentation.Slides(5).Select
Application.ActiveWindow.View.PasteSpecial DataType:=ppPasteEnhancedMetafile

xlWorkBook.Close False
xlApp.Quit

Powerpoint vba - copying table from excel

1 Answers