recordset.find unable to find value from closed workbook: Item cannot be found in the collection corresponding to the requested name or ordinal.

[Alexandroid]

Hey everyone and thanks for all your help in the past!

I'm trying to search for a value in a closed workbook and if that value is found, I would know that that unique ID has already been used and move onto the next number. I'm choosing to use the ADODB method instead of having it open in the background and not be visible. I'm hoping that by using ADODB I can make the process much faster.

I'm having issues when I run the Recordset.find method and receive the error:

Run-time error '3265':

Item cannot be found in the collection corresponding to the requested name or ordinal.

The closed workbook is being used as a database that is storing series of numbers that have been used before when we are trying to assign unique IDs to some documents. Before I came here, They would just randomly generate numbers which lead to them being all over the place and now they receive errors that they have duplicate IDs. I've added all the ID's in the closed Spreadsheet (Database.xlsx), and I'm trying to search through it with a macro that can be run on many different workstations but that would all point to the Database that is on a network drive.

Sub QueryDB()

    Dim strMyPath As String, strDBName As String, strDB As String
    Dim adoRecSet As ADODB.Recordset
    Dim i As Long
    
    strDBName = "Database.xlsx"
    
    strMyPath = ThisWorkbook.Path
    
    strDB = strMyPath & "\" & strDBName
    
    Dim connDB As New ADODB.Connection
    
    connDB.Open ConnectionString:="Provider = Microsoft.ACE.OLEDB.12.0; Data Source=" & strDB & "; Extended Properties='Excel 12.0;HDR = YES'; Mode =" & adModeShareDenyNone
    
    Set adoRecSet = New ADODB.Recordset
    
    Dim strTable As String
    
    strTable = "TableName"
    
    adoRecSet.Open Source:=strTable, ActiveConnection:=connDB, CursorType:=adOpenKeyset, LockType:=adLockOptimistic
    
    adoRecSet.Find "TableName = '456010'"
    
    adoRecSet.Close
    connDB.Close
    
    Set adoRecSet = Nothing
    Set connDB = Nothing
    
End Sub

In the Database spreadsheet, I've named the area with all the numbers "TableName" just to make it simple. Each column of the database has numbers that start with a section number (Column A would be 1230001,1230002, 1230003 and Column B would be 456009, 456010, 456025... The numbers are not sequential either.) and the header row would contain the specific section (123 or 456 in this example).

I was unable to attach example files but that would make it much easier to understand I believe. If you think you can assist me with this and would like the files, please let me know and I will send them to you.

Thank you!

Alex

 

[RoryA]

Is the column header also TableName?

 

[Alexandroid]

Yes they are all part of the named range called TableName.

I just bunched it all in one name and included the HDR tag in the ConnectionString hoping that would make it easier.

 

[RoryA]

Each column of the database has numbers that start with a section number (Column A would be 1230001,1230002, 1230003 and Column B would be 456009, 456010, 456025... The numbers are not sequential either.) and the header row would contain the specific section (123 or 456 in this example).

Just saw this bit, which contradicts what you just said. The field names you have to use are those in row one of each column, not the name of the range, and you will have to search each column individually unless you can restructure your workbook.

 

[Alexandroid]

Oh! Yes I was doing that incorrectly. I completely forgot that it was the column name that should be in "adoRecSet.Find "TableName = '456010'"" and not the TableName again...

Ok so with that being said, I've changed it to show as

adoRecSet.Find "456 = '456010'"

Now I'm getting the following error:

Run-Time error '3001':

Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.

Does this mean that the Column names cannot be only numbers?

In my real code, it would look something like this:

Series = 456
sequentialNumber = 456010

adoRecSet.Find Series & " = '" & sequentialNumber & "'"

I guess I have to simply start the header with a letter and will get rid of the error?

Thanks for pointing me in the right direction!

 

[Alexandroid]

Oh! Yes I was doing that incorrectly. I completely forgot that it was the column name that should be in "adoRecSet.Find "TableName = '456010'"" and not the TableName again...

Ok so with that being said, I've changed it to show as

adoRecSet.Find "456 = '456010'"

Now I'm getting the following error:

Run-Time error '3001':

Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.

Does this mean that the Column names cannot be only numbers?

In my real code, it would look something like this:

Series = 456
sequentialNumber = 456010

adoRecSet.Find Series & " = '" & sequentialNumber & "'"

I guess I have to simply start the header with a letter and will get rid of the error?

Thanks for pointing me in the right direction!

 

[RoryA]

Try:

adoRecSet.Find "[456]  = '456010'"

 

[Alexandroid]

Try:

adoRecSet.Find "[456]  = '456010'"

That worked!

Thank you very much! I guess the title of this isn't accurate anymore to the actual issue I was having.