Save Method Issue

[graviz]

I wrote this code in Access 2003 and now I'm in 2007 and it's giving me a 1004 error Method of Save of object failed (XlBook.Save). I checked the refs and everything seems to be fine. Any ideas?

Public Function Output_Overall()
Call Init_Paths
Dim cnn As ADODB.Connection
Dim MyRecordset As New ADODB.Recordset
Dim MySQL As String
Dim MySheetPath As String
Dim Xl As Excel.Application
Dim XlBook As Excel.Workbook
Dim XlSheet As Excel.Worksheet
Set cnn = CurrentProject.Connection
MyRecordset.ActiveConnection = cnn
MySQL = "SELECT * From "
MySQL = MySQL & "02_08_Overall_Latest_Week"
MyRecordset.Open MySQL
MySheetPath = Staging_Path
Set Xl = CreateObject("Excel.Application")
Set XlBook = GetObject(MySheetPath)
Xl.Visible = True
XlBook.Windows(1).Visible = True
Set XlSheet = XlBook.Worksheets("Overall")
    For iCol = 1 To MyRecordset.Fields.Count
        XlSheet.Cells(1, iCol).Value = MyRecordset.Fields(iCol - 1).Name
    Next
    XlSheet.Range("A2").CopyFromRecordset MyRecordset
MyRecordset.Close
Set cnn = Nothing
Set Xl = Nothing
XlBook.Save
XlBook.Close
Set XlBook = Nothing
Set XlSheet = Nothing
End Function

 

[TJPoorman]

My guess would be that you are closing the connection before you are running your save line. Try moving the save line up like this:

XlBook.Save
XlBook.Close
MyRecordset.Close
Set cnn = Nothing
Set Xl = Nothing
Set XlBook = Nothing
Set XlSheet = Nothing

 

[graviz]

My guess would be that you are closing the connection before you are running your save line. Try moving the save line up like this:

XlBook.Save
XlBook.Close
MyRecordset.Close
Set cnn = Nothing
Set Xl = Nothing
Set XlBook = Nothing
Set XlSheet = Nothing

I just tried it and I have the same error on the XlBook.Save line.