Excel & ADO

  • Thread starter Thread starter DNKMCA
  • Start date Start date
D

DNKMCA

Hi,
Im using ADO and backend as MS Excel

if my search value is found in Sheet1's 25,000 Row
how do i extract that the search value is found in row 25,000

Thanks
 
DNKMCA said:
if my search value is found in Sheet1's 25,000 Row
how do i extract that the search value is found in row 25,000
Click to expand...

Sub test()
Debug.Print GetRowID(49304)
End Sub

Function GetRowID( _
ByVal searchValue As Double _
) As Long

Dim Cat As Object
Set Cat = CreateObject("ADOX.Catalog")

With Cat
.Create _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\newtemp.mdb"

With .ActiveConnection
.Execute _
"CREATE TABLE temp1 (" & _
" row_ID IDENTITY(1,1)," & _
" data_col MEMO" & _
");"
.Execute _
"CREATE PROCEDURE GetRowID " & _
" (:search_value FLOAT)" & _
" AS " & _
" SELECT row_ID" & _
" FROM temp1" & _
" WHERE data_col = :search_value"

.Execute _
"INSERT INTO temp1 (data_col)" & _
" SELECT F1 AS data_col" & _
" FROM" & _
" [Excel 8.0;HDR=NO;Database=C:\MyFile.xls;" & _
"].[MySheet$]; "

Dim rs As Object
Set rs = .Execute("EXECUTE GetRowID " & searchValue)

On Error Resume Next
GetRowID = rs(0)
End With
End With

End Function
 
Back
Top