What's wrong with my code? Can't figure it out.

[Myriad_Rocker]

I can't seem to figure out why this won't work. I have examined some other code in which this works and I can't find anything missing. I have all the references that are in the working code in the other database...

Option Compare Database

Public Sub CreateVANReport()
    Dim DB As DAO.Database, rss As DAO.Recordset, Rs As DAO.Recordset
    Dim rsADO As ADODB.Recordset
    Dim i As Integer, j As Integer
    Dim RsSql As String
    Dim CurrentValue As Variant
    Dim CurrentField As Variant
    Dim Workbook As Object
    Dim xlApp As Object
    Dim Sheet As Object
    Dim filename1 As String
    Dim irow As Integer
    Dim total As String
    DoCmd.Hourglass True
    DoCmd.SetWarnings False
    
    Set DB = DBEngine.Workspaces(0).Databases(0)
    
    'Getting information for total requested block in the VAN Spreadsheet
        RsSql = "SELECT * FROM [VAN Count By Request Month All_Crosstab]"
        Set Rs = DB.OpenRecordset(RsSql, dbOpenDynaset)
        
    'Assigning the file name
    filename1 = "S:\CorpSafety\Safety Reporting\reportrun\" & "VAN Log Correction Template.xls"
        
    Dim xls As New Excel.Application
    xls.Workbooks.Open filename1
        
    ' Loop through the Microsoft Access records and copy the records
    ' to the Microsoft Excel spreadsheet.
        
        j = 7
        
        Do Until Rs.EOF
                For i = 0 To Rs.Fields.Count - 1
                    CurrentField = Rs(i)
                    Sheet.Cells(j, i + 1).Value = CurrentField
                    Debug.Print j, i
                    Debug.Print CurrentField
                Next i

                    Rs.MoveNext
                    j = j + 1
        Loop
        
    'Where to send file
    LOCATION = "d:\" & "VAN Log Correction - " & Date & ".xls"
    Debug.Print LOCATION
    
    'Save and close file
    xls.ActiveWorkbook.SaveAs Filename:=LOCATION
    xls.ActiveWorkbook.Close True
    xls.Workbooks.Close
    xls.Quit
    
    'Need to set spreadsheet to nothing to terminate excel program
    Set xls = Nothing
    
    DoCmd.Hourglass False
    DoCmd.SetWarnings True
    MsgBox "VAN Report complete and saved to D:\"
End Sub

 

[Myriad_Rocker]

DUH...I found it. I thought I had set the sheet but I guess I didn't...

 

[vfgilber]

I would like to adapt the code above, however, I get the same error that the original poster says he/she had. The poster appears to have fixed it ("Duh - I forgot to set the sheet").

I would like "set the sheet", but I don't know what this means.

Can anyone help?

Thanks,

Vince