Finding String in Excel Using VB6 ADO, Return Column & Row Problems

[Qpido]

Dear helpful forum members,

I am trying to use ADO via VB6 to find a specific input in row A of my excel sheet.

I then want it to return the Column and Row to me separately.

I'm having a huge problem finding any precise information on how to do this.

I have a code I already use for returning specific values from cells:

Function Look(ClosedWorkbookFullName As String, _
    SheetName As String, RangeAddress As String) As Variant
    
    Dim conn As Object, rs As Object, SQL As String
    Set conn = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")
    
    conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=" & ClosedWorkbookFullName & _
    ";Extended Properties=""Excel 8.0;HDR=NO;"""
	
    SQL = "Select * From [" & SheetName & "$" & RangeAddress & ":" & RangeAddress & "]"
    rs.Open SQL, conn, 1, 3
    Look = rs.Fields(0).Value
    rs.Close: conn.Close
    
    If IsNull(Look) Then Look = ""

End Function

Now I want something like this I can use as a function, but it obviously needs tweaks.

Could anyone assist me in this matter?

As always, thank you very much.

Jerome

 

[Qpido]

Still need a solution to this, if anyone could help that would be great.

I have managed to upgrade it to something like this to reflect what I'm trying to do exactly, I am trying to get the column of the field where my search string is located.

Function HorizontalFind(StrFind As String, ClosedWorkbookFullName As String, _
    SheetName As String, iStart As Integer) As Variant
    
    Dim conn As Object, rs As Object, SQL As String
    Dim i As Integer
    Set conn = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")
    
    conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=" & ClosedWorkbookFullName & _
    ";Extended Properties=""Excel 8.0;HDR=NO;"""
    
    SQL = "Select * From [" & SheetName & "$]"
    rs.Open SQL, conn, 1, 1
    
    i = 4
    
        Do While Not rs.EOF
            If StrFind = rs.Fields(i).Value Then
                HorizontalFind = rs.Fields(i).Name
                MsgBox HorizontalFind, vbOKOnly
                'HorizontalFind = rs.AbsolutePosition
                rs.Close: conn.Close
                Exit Function
            Else
                HorizontalFind = "Not Found"
                rs.MoveNext
            End If
            rs.movefirst
            i = i + 1
        Loop
    
    
    If IsNull(HorizontalFind) Then HorizontalFind = ""
End Function

I would call it with something like this:

sLine = modFunc.HorizontalFind("DE01", sFile, sSheet, 5)

Thanks