Run-time error '3265' - Item cannot be found in the collection corresponding to the requested name or ordinal.

[Cuervo]

Hi All,

I found a thread on this forum with the exact error I was getting and the person who started the thread in the next post just states "I got it." and never actually states what he got. So, I'm starting a new thread with the same issue. However I have some interesting twists, but first let's get the code up here.

Dim cnn As New ADODB.Connection
Dim rsXLS As New ADODB.Recordset

    Set TheWB = Workbooks("Cat_Data.xlsm")
    Set TheSheet = TheWB.Worksheets(1)

    xlsFile = "TheCatalog.xls"
    xlsPath = "C:\Catalog Files\"
    
    myConnString = "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=" & xlsPath & xlsFile & ";"
    myConnString = myConnString & "Extended Properties=" & Chr(34) & "Excel 12.0 Xml;HDR=YES;IMEX=1" & Chr(34) & ";"
    
    If cnn.State = adStateOpen Then
        cnn.Close
    End If
    cnn.ConnectionString = myConnString
    cnn.Open
    
    mySQL = "SELECT * FROM [Page 1$V:W]"
    RNG = "AF5"
    Set rsXLS = cnn.Execute(MySQL)
    TheSheet.Range(RNG).CopyFromRecordset rsXLS
    rsXLS.Close

    mySQL = "SELECT * from [Page 1$]"
    RNG = "AM"
    Set rsXLS = cnn.Execute(MySQL)
    rsXLS.MoveFirst
    While Not rsXLS.EOF
        amPrint = amPrint + 1
        rsValue = rsXLS(23)
        TheSheet.Range(RNG & amPrint).Value = rsValue
        rsXLS.MoveNext
     Wend
           
'Clean Up   
    If CBool(cnn.State And adStateOpen) Then cnn.Close
    Set connstring = Nothing
    Set rsXLS = Nothing

So, it seems simple, though I've never opened and Excel sheet for use as a DB before. All I want to do is open the "Catalog.xls" file read a couple of columns an put them in other columns in the "Cat_Data.xlsm" sheet. The first request works without issue. I can grab the V and W columns and paste the recordset to the rows I need them. The second request has been driving me crazy. It is the last column (column 'X') on the "Catalog.xls" sheet and Excel doesn't believe it exists.

If I run the code as is I get the error in the title 'Run-time error '3265' - Item cannot be found in the collection corresponding to the requested name or ordinal.' However, if I don't touch the code and I open the "Catalog.xls" sheet, it works. When I close the sheet, it stops working again. If I don't touch the code and add anything to column 'Y' on the "Catalog.xls" and close the sheet it works, but for some reason it doesn't work with the closed, unedited "Catalog.xls".

The SELECT * FROM [Page 1$] was because I couldn't get the VBA to give me just 'Column X' so for troubleshooting I grabbed the whole table and simply tried to get column X from the recordset by column number, but it doesn't work except in the situations I outlined above.

Any insight anyone can provide would be greatly appreciated. I'm sure it is something simple I'm missing but I just don't see it.

Thanks for your help.