Object Variable Not Set Error when closing Excel workbook from PowerPoint

[ScottyBee]

I have code in PowerPoint that opens Excel, retrieves a value from a cell, displays a message box with the cell's value and finally closes the workbook followed by the application.

The first time I run the code in PowerPoint, everything works just fine. When I run the same code again, I get a "Run-time error 91" "Object variable or With block variable not set. Any ideas on what is causing this? Below is my code:

PS: technically I don't need to run the same code again but am curious. As this project gets more involved, would be nice to know the cause of this error so I can avoid it in other coding projects--Thanks.

Private Sub RetrieveExcelValue()
Dim xlApp As Object
Dim xlWorkBook As Object
Dim ExcelValue As Double

Set xlApp = CreateObject("Excel.Application")

xlApp.Visible = True
Set xlWorkBook = xlApp.Workbooks.Open("E:\Special Projects\PPT Project for Matthew Orenchuk\Cross Flow Skid EER.XLSM", True, False)

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

MsgBox ("The value of this range in Excel is " & ExcelValue)
ActiveWorkbook.Save
xlApp.Quit

End Sub

[John Wilson]

ActiveWorkbook means nothing to PowerPoint (unless you set an excel reference.) Also I would close Excel before the message box.

Private Sub RetrieveExcelValue()
Dim xlApp As Object
Dim xlWorkBook As Object
Dim ExcelValue As Double


Set xlApp = CreateObject("Excel.Application")


xlApp.Visible = True
Set xlWorkBook = xlApp.Workbooks.Open("E:\Special Projects\PPT Project for Matthew Orenchuk\Cross Flow Skid EER.XLSM", True, False)


ExcelValue = xlWorkBook.Worksheets("PPT Transfer").Range("C5").Value
xlWorkBook.Save ' probably not needed
xlWorkBook.Close
xlApp.Quit
MsgBox ("The value of this range in Excel is " & ExcelValue)


End Sub

[ScottyBee]

Hello Jon,

I do have the Excel Object Library referenced in PowerPoint and moved the MsgBox statement to the end of the code as you suggested. (Please see below). Unfortunately, I am still getting the same error "Run-time error 91 Object variable or With block variable not set."
The code runs just fine the first time. When I run the same code again, Excel does not close and get the error above.

When clicking Debug, ActiveWorkbook.Save is highlighted in yellow. This might seem like trivial code but it is "proof of concept" for a much larger project. I have a team of co-workers who have a map of the warehouse where rooms are manually color coded based on values from Excel. I want to get this code working first before I create the code for the dozens of rooms on the PowerPoint map.

Any ideas why the code works just fine upon first execution but fails to close Excel and generates the error above on the second execution? Thanks

Private Sub RetrieveExcelValue()
Dim xlApp As Object
Dim xlWorkBook As Object
Dim ExcelValue As Double

Set xlApp = CreateObject("Excel.Application")

xlApp.Visible = True
Set xlWorkBook = xlApp.Workbooks.Open("E:\Special Projects\PPT Project for Matthew Orenchuk\Cross Flow Skid EER.XLSM", True, False)

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

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

[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

[ScottyBee]

Thanks John,

Your instructions worked perfectly! I appreciate the help