VBA Interaction with Excel from PowerPoint

[MWE]

I am running Office2000 and WinXP on an IBM T40 laptop.

I am using VBA in PowerPoint to fetch data from an Excel
spreadsheet. I am using a pretty standard approach, i.e.,
creating an Excel object on the fly, opening a
spreadsheet, fetching data, closing the spreadsheet and
then equiting the Excel object. This has worked quite
well with Outlook and Word. a code fragment is reproduced
below:

'
' define Excel types and definitions
'
Dim xLApp As Excel.Application
Dim xLBook As Excel.Workbook
Dim xLSheet As Excel.Worksheet
Dim xLPath As String, xLFileName As String
'
' define location & filename of target spreadsheet
'
xLPath = ActivePresentation.Path
xLFileName = "SSU_Setup.xls"
'
' define application object
'
Set xLApp = CreateObject("Excel.Application")
' open target spreadsheet
' define target worksheet
'
xLApp.Workbooks.Open FileName:=xLPath + "\" +
xLFileName
Set xLSheet = xLApp.Workbooks
(xLFileName).Worksheets("SSU_CurrentProject")
'
' do stuff with the spreadsheet
'
stuff
stuff
'
' close spreadsheet, quit xL
' misc housekeeping
'
xLApp.Workbooks(xLFileName).Close
SaveChanges:=False
xLApp.Quit


This works fine. However if I alter the Close statement
to save the spreadsheet, i.e., SaveChanges:=True the
application hangs. If I go to "Task Manager" and end the
PPT session, I am then prompted with a "SAVE AS" window
for the spreadsheet. If I try to save the spreadsheet
over itself, I get the standard message asking me if I
really want to do that. Regardless of how I answer, the
SAVE AS window remains active until I cancel it. This
inability to save the spreadsheet using the above code is
unique to PowerPoint, i.e., it seems to work OK in Word
and Outlook

Any help would be appreciated.

 

[Brian Reilly, MS MVP]

Try changing the following code to what follows
'Yours
xLApp.Workbooks(xLFileName).Close
SaveChanges:=False
xLApp.Quit

'Try this
with xlApp
.workbooks(xlFileName).Save
xLApp.Workbooks(xLFileName).Close_
SaveChanges:=False
.Quit
end with

Not tested but should work. Let me know if it doesn't and I'll
certainly write something that will work after testing it.

Brian Reilly, PowerPoint MVP