Object Variable Not Set Error when closing Excel workbook from PowerPoint

[John Wilson]

If you have referenced Excel why declare xlApp and xlWorkbook as Objects? Personally I would use late binding and not reference Excel. It may be a little slower but can be safer.

The problem though is using ActiveWorkbook.Save use xlWorkbook.Save as in my previous post and see if that works. This is both if you use early or late binding

UPDATE

I just checked and ActiveWorkBook did work if Excel is properly referenced but I would still use xlWorkbook

Sub RetrieveExcelValue()'Excel is referenced
Dim xlApp As Excel.Application
Dim xlWorkBook As Excel.Workbook
Dim ExcelValue As Double


Set xlApp = CreateObject("Excel.Application")


xlApp.Visible = True
Set xlWorkBook = xlApp.Workbooks.Open("C:/users/johns/desktop/test.xlsx")' my path of course


ExcelValue = xlWorkBook.Worksheets("PPT Transfer").Range("C5").Value
xlWorkBook.Save
xlApp.Quit


MsgBox ("The value of this range in Excel is " & ExcelValue)
End Sub