access vba function to find a text in excel to return the column number (1 Viewer)

[jydbman]

Hi Access VBA Excel automation gurus,

I have been searched the web the past 2 days to find a access vba function to find a text in a excel file and return the column number, but I can not find it. Please help!

The Access vba function will be allow to be reused by passing parameters. I would like to be able to pass the excel file name, sheet name, and the text string I want to find. It will return the column number.

Your help will be greatly appreciated!

 

[SpentGeezer]

Try this from the Spent Geezers Association (save tester.xlc to c:\):

 

[jydbman]

Try this from the Spent Geezers Association (save tester.xlc to c:\):

Thank you so much, SpentGeezer! It's very nice of you!

It works perfectly! This is exactly what I need!

Question: I want to use another function and within that function, assign a variable to the return value of your function. In case I want to get the 2 return values for column and row, how to change the function? My access VBA level is limited, I only know how to return one value. Thanks again!

 

[SpentGeezer]

Try this. I have changed a couple of things in the function to put the row and column as a delimited string. Then use the split function to parse it. ENJOY:

dim myString
dim myRow
dim myCol

mystring = getthecolumn1(thefile,sheet,thestring)

dim myArray() as string
if len mystring > 0 then
myarray = split(mystring, ",")
myrow = myarray(0)
mycol = myarray(1)
end if

Public Function GetTheColumn1(theFile As String, sheet As String, theString As String) As STRING '##############

Dim FSO
Set FSO = CreateObject("scripting.filesystemobject")

Dim objExcel
Dim strExcelPath
Dim objSheet
Dim CellVal
Dim theROW
Dim theCOL
Dim maxCOLS
maxCOLS = 0
theROW = 1
theCOL = 1

strExcelPath = theFile


' Open specified spreadsheet and select the first worksheet.
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = False
objExcel.Workbooks.Open strExcelPath
Set objSheet = objExcel.ActiveWorkbook.Worksheets(sheet)
MsgBox "Searching Sheet: " & objSheet.Name

'get the column

Do Until theCOL = 100 ' you should change this to accomodate your spreadsheets. There is probably a function to do this ???
    Do Until theROW = 100 ' you should change this to accomodate your spreadsheets
        CellVal = objSheet.Cells(theROW, theCOL).Value
        If CellVal = theString Then
            'MsgBox "The Column is: " & theCOL
            GetTheColumn1 = theRow & "," & theCOl '##############
            Set objSheet = Nothing
            objExcel.Quit
            Set objExcel = Nothing
            Exit Function
        End If
        theROW = theROW + 1
    Loop
    theCOL = theCOL + 1
    theROW = 1
Loop

MsgBox "String Not Found!"
Set objSheet = Nothing
objExcel.Quit
Set objExcel = Nothing
End Function

 

[jydbman]

Thank you so much for help!

I tried to test it in a command button, but get a "subscript out of range" error on the line "myCol = myArray(1)"

myString = GetTheColumn1(theFile, sheet, theString)

Dim myArray() As String
If Len(myString) > 0 Then
myArray = Split(myString, ",")
myRow = myArray(0)
myCol = myArray(1)
End If

 

[SpentGeezer]

It works for me, see updated code in this db:

 

[jydbman]

It works for me, see updated code in this db:

fantastic!! It works! Thank you so much for all your help!!