Error 3265 Item Not Found In Collection

[mistera]

I'm stumped and need some help. I am trying to transfer values from my Access 2007 database to an Excel file, but I am getting error 3265 - item not found in collection. Below is a snippet of my code.

strFileName is the fully qualified name of an Excel file. The error occurs at the red highlighted line. I have verified that the field names from the recordset are valid (i.e. Y1 Payroll). In fact, while in break mode, I used the immediate window to check the values of each of the source items and each item had a good value. I also know that there are already values in another column in the Excel file and was able to use the immediate window to show those values using ?xlWS.cells(9, 3) which also showed good values.

So, I know the file is opening fine and that the worksheet was found (and I even cover myself if it isn't found). What can the problem be??

        Set xlApp = CreateObject("Excel.Application")
        Set xlWB = xlApp.Workbooks.Open(strFileName)
 
        strSQL = CurrentDb.QueryDefs("qry_OpExExcelFileData").SQL
        Set rs = CurrentDb.OpenRecordset(strSQL, dbReadOnly)
 
        With rs
            Do Until .EOF
                strSheetName = rs![CC Category]
                On Error Resume Next
                Set xlWS = xlWB.Sheets(strSheetName)
                Err.Clear
                On Error GoTo Err_CreateOpExFile
                If xlWS Is Nothing Then
                    xlWB.Sheets("Blank Template Sheet").Copy Before:=xlWB.Sheets("Blank Template Sheet")
                    xlWB.Sheets("Blank Template Sheet (2)").Name = strSheetName
                    Set xlWS = xlWB.Sheets(strSheetName)
                End If
                For intCounter = 1 To 5
                    intColumn = intCounter + 3
[COLOR=red]                   xlWS.Cells(9, intColumn) = .Fields("Y" & intCounter & " Payroll")[/COLOR]
                    xlWS.Cells(10, intColumn) = .Fields("Y" & intCounter & " Cap Labor")
                    xlWS.Cells(16, intColumn) = .Fields("Y" & intCounter & " Advertising")
                    xlWS.Cells(17, intColumn) = .Fields("Y" & intCounter & " Cost of Credit")
                    xlWS.Cells(18, intColumn) = .Fields("Y" & intCounter & " Equipment Lease")
                    xlWS.Cells(19, intColumn) = .Fields("Y" & intCounter & " Outside Services")
                    xlWS.Cells(20, intColumn) = .Fields("Y" & intCounter & " Supplies")
                    xlWS.Cells(21, intColumn) = .Fields("Y" & intCounter & " Property")
                    xlWS.Cells(22, intColumn) = .Fields("Y" & intCounter & " Travel")
                    xlWS.Cells(23, intColumn) = .Fields("Y" & intCounter & " Associate Expense")
                    xlWS.Cells(24, intColumn) = .Fields("Y" & intCounter & " Telecommunications")
                    xlWS.Cells(25, intColumn) = .Fields("Y" & intCounter & " Repairs")
                    xlWS.Cells(26, intColumn) = .Fields("Y" & intCounter & " CSR Accretion")
                    xlWS.Cells(27, intColumn) = .Fields("Y" & intCounter & " Other")
                    xlWS.Cells(30, intColumn) = .Fields("Y" & intCounter & " Depreciation")
                Next intCounter
                .MoveNext
            Loop
            .Close
        End With
    End If
 
    xlWB.Close SaveChanges:=True
    xlApp.Application.Quit

 

[CJ_London]

The error is almost certainly referring to the rs collection

If you have spaces in field names then you need to surround with square brackets
Fields("[Y" & intCounter & " Payroll]")

 

[mistera]

Good thought, but that isn't it. I tried adding the brackets and the same error occurred.

When referencing a field using the Fields keyword and using the name rather than the index number, putting that name in quotes is usually enough and the brackets are not necessary. When I put a breakpoint in at the statement causing the problem and I use the immediate window and type ?rs.Fields("Y" & intCounter & " Payroll") the result shows a value and I do not get the error.

If I didn't use the Fields keyword, then I would agree that the brackets would be necessary, i.e. rs![Y1 Payroll]. I did not use this alternative because I needed to use the variable intCounter within the name.

Any other ideas of what the issue could be?? I really am struggling with this!

 

[mistera]

I've determined that the problem has something to do with the Excel worksheet cell reference, but I'm still not sure what the issue is.

In order to narrow down the issue to whether it was related to the rs recordset value or to the Excel worksheet cell, I tried changing the target (left side of equals) to a simple variable name and it worked. Then I tried changing the source (right side of equals) to a numeric value and got the error.

 

[mistera]

Yippee!! I figured out the issue. While the problem appeared to be with the line of code I had highlighted, it was actually due to the fact that the "Y2 Other" field was missing from my query that was used as the recordset. Once I added that field to the query, everything worked just as expected.