Results 1 to 11 of 11
  1. #1
    Join Date
    Aug 2007
    Posts
    88

    Unanswered: access vba function to find a text in excel to return the column number

    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!

  2. #2
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    You could try something like this
    Code:
    Function ColumnNo(ByVal strText As String, ByVal FilePathName As String, ByVal ShtName As String) As Integer
        Dim FileName As String
        Dim sht As Worksheet
        Dim ShtFound As Boolean
        
        ShtFound = False
        
        If Dir(FilePathName) <> "" Then
            FileName = Mid(FilePathName, InStrRev(FilePathName, "\") + 1)
            Application.ScreenUpdating = False
            Application.Workbooks.Open FilePathName
            
            For Each sht In ActiveWorkbook.Sheets
                If sht.Name = ShtName Then
                    ShtFound = True
                    On Error Resume Next
                    ColumnNo = sht.Cells.Find(What:=strText, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
                                :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
                                False, SearchFormat:=False).Column
                    On Error GoTo 0
                    If ColumnNo = 0 Then MsgBox "Text '" & strText & "' not found in file " & FileName & " (" & ShtName & ")", vbExclamation, "Find Column"
                End If
            Next
            
            If Not ShtFound Then MsgBox "Sheet '" & ShtName & "' could not be found in file '" & FileName & "'!", vbExclamation, "Find Column"
            Workbooks(FileName).Close False
            Application.ScreenUpdating = True
        Else
            MsgBox "File " & FilePathName & " could not be found!", vbExclamation, "Find Column"
        End If
    End Function
    and used like this
    Code:
    Sub test()
        Dim FName As String
        Dim SName As String
        Dim ThisText As String
        
        FName = "C:\Test\TestText.xls"    
        SName = "Sheet1"  
        ThisText = "Test Text"    
        
        MsgBox ColumnNo(ThisText, FName, SName)
        
    End Sub
    HTH

    Any question please ask!

    MTB

  3. #3
    Join Date
    Aug 2007
    Posts
    88

    Thank you so much!

    Hi Mike,

    Thank you so much for helping. This is exact what I need. I will try it out tonight and let you know how it goes.

    If I need to return both row no. and column no., how to change the code?

    Thanks again! It's so nice of you!

    Quote Originally Posted by MikeTheBike View Post
    Hi

    You could try something like this
    Code:
    Function ColumnNo(ByVal strText As String, ByVal FilePathName As String, ByVal ShtName As String) As Integer
        Dim FileName As String
        Dim sht As Worksheet
        Dim ShtFound As Boolean
        
        ShtFound = False
        
        If Dir(FilePathName) <> "" Then
            FileName = Mid(FilePathName, InStrRev(FilePathName, "\") + 1)
            Application.ScreenUpdating = False
            Application.Workbooks.Open FilePathName
            
            For Each sht In ActiveWorkbook.Sheets
                If sht.Name = ShtName Then
                    ShtFound = True
                    On Error Resume Next
                    ColumnNo = sht.Cells.Find(What:=strText, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
                                :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
                                False, SearchFormat:=False).Column
                    On Error GoTo 0
                    If ColumnNo = 0 Then MsgBox "Text '" & strText & "' not found in file " & FileName & " (" & ShtName & ")", vbExclamation, "Find Column"
                End If
            Next
            
            If Not ShtFound Then MsgBox "Sheet '" & ShtName & "' could not be found in file '" & FileName & "'!", vbExclamation, "Find Column"
            Workbooks(FileName).Close False
            Application.ScreenUpdating = True
        Else
            MsgBox "File " & FilePathName & " could not be found!", vbExclamation, "Find Column"
        End If
    End Function
    and used like this
    Code:
    Sub test()
        Dim FName As String
        Dim SName As String
        Dim ThisText As String
        
        FName = "C:\Test\TestText.xls"    
        SName = "Sheet1"  
        ThisText = "Test Text"    
        
        MsgBox ColumnNo(ThisText, FName, SName)
        
    End Sub
    HTH

    Any question please ask!

    MTB

  4. #4
    Join Date
    Aug 2007
    Posts
    88
    Hi Mike,

    When I test the code, it shows the error on the line Application.ScreenUpdating = False

    Compile error: Method or data member not found

    if I comment this line, I got the same error on the line Application.Workbooks.Open FilePathName


    I comment out
    Application.ScreenUpdating = False
    Application.Workbooks.Open FilePathName

    and changed the code to

    Dim objXL As Object
    Set objXL = CreateObject("Excel.Application")
    objXL.ScreenUpdating = False
    objXL.Workbooks.Open FilePathName

    it pass but error out on the line and highlight on SearchFormat with the error: compile error: named argument not found.

    ColumnNo = sht.Cells.Find(What:=strText, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
    :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
    False, SearchFormat:=False).Column


    I do not write Access VBA often and I am not familiar with the syntax. Sorry for asking your help again.

    Forgot to tell you that I am using Access 2002.

    Thanks a lot!

  5. #5
    Join Date
    Aug 2007
    Posts
    88
    Hi Mike,

    I change the code to the following and it works. However, it pop up a message box only show the column number and an OK button. Is there any way to remove the pop up message box? I just want to use one function and within this function, assign a variable to the return value of this function. Thank you very much!

    Public Function ColumnNo(ByVal strText As String, ByVal FilePathName As String, ByVal ShtName As String) As Integer
    Dim FileName As String
    Dim sht As Worksheet
    Dim ShtFound As Boolean

    ShtFound = False

    Dim objXL As Object
    Set objXL = CreateObject("Excel.Application")

    If Dir(FilePathName) <> "" Then
    FileName = Mid(FilePathName, InStrRev(FilePathName, "\") + 1)

    objXL.ScreenUpdating = False
    objXL.Workbooks.Open FilePathName

    For Each sht In ActiveWorkbook.Sheets
    If sht.Name = ShtName Then
    ShtFound = True
    On Error Resume Next

    ColumnNo = sht.Cells.Find(What:=strText, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
    :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
    False).Column
    On Error GoTo 0
    If ColumnNo = 0 Then MsgBox "Text '" & strText & "' not found in file " & FileName & " (" & ShtName & ")", vbExclamation, "Find Column"
    End If
    Next

    If Not ShtFound Then MsgBox "Sheet '" & ShtName & "' could not be found in file '" & FileName & "'!", vbExclamation, "Find Column"

    objXL.Workbooks(FileName).Close False
    objXL.ScreenUpdating = True
    Else
    MsgBox "File " & FilePathName & " could not be found!", vbExclamation, "Find Column"
    End If
    End Function

  6. #6
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    If you do not want any (error) messages then remove all the line with msgbox in!!
    ie
    Code:
    Public Function ColumnNo(ByVal strText As String, ByVal FilePathName As String, ByVal ShtName As String) As Integer
        Dim FileName As String
        Dim sht As Worksheet
        Dim objXL As Object
        
        Set objXL = CreateObject("Excel.Application")
    
        If Dir(FilePathName) <> "" Then
            FileName = Mid(FilePathName, InStrRev(FilePathName, "\") + 1)
    
            objXL.Workbooks.Open FilePathName
            
            For Each sht In ActiveWorkbook.Sheets
                If sht.Name = ShtName Then
                    
                    On Error Resume Next
                    ColumnNo = sht.Cells.Find(What:=strText, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
                    :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
                    False).Column
                    On Error GoTo 0
                End If
            Next sht
    
            objXL.Workbooks(FileName).Close False
        End If
    End Function
    It is clear from the code you have set a reference the Excel Library (early binding). The one problem with this is that you CAN run in to problems if the DB is run on different versions of Office. So I tend to usr late binding (no reference to the Excel library).

    If you are interested this code below is for late binding, but you do need to specify the values (in tis case I have created variable) of any Excel constants used in the code.
    Code:
    Public Function ColumnNo(ByVal strText As String, ByVal FilePathName As String, ByVal ShtName As String) As Integer
        Dim FileName As String
        Dim sht As Object
        Dim objXL As Object
        
        Const xlFormulas As Integer = -4123
        Const xlPart As Integer = 2
        Const xlByRows As Integer = 1
        Const xlNext As Integer = 1
        
        Set objXL = CreateObject("Excel.Application")
    
        If Dir(FilePathName) <> "" Then
            FileName = Mid(FilePathName, InStrRev(FilePathName, "\") + 1)
            
            With objXL
                .Workbooks.Open FilePathName
            
                For Each sht In .ActiveWorkbook.Sheets
                    If sht.Name = ShtName Then
          
                        On Error Resume Next
                        ColumnNo = sht.Cells.Find(What:=strText, After:=objXL.Cells(1, 1), LookIn:=xlFormulas, LookAt _
                        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
                        False).Column
                        On Error GoTo 0
                    End If
                Next sht
        
                .Workbooks(FileName).Close False
            End With
        End If
    End Function
    MTB

  7. #7
    Join Date
    Aug 2007
    Posts
    88
    Thank you so much for helping and explaining to me. Early and Late binding is a new concept to me. I learned a lot from you. I will try the code tonight and let you know! Thanks again!

  8. #8
    Join Date
    Aug 2007
    Posts
    88

    Thumbs up

    MTB,

    I test the code and both of your codes (early and late binding) all work great!

    Thank you so much for all your helps. It's very nice of you! I am very grateful!

  9. #9
    Join Date
    Aug 2007
    Posts
    88

    return column letter rather than column number

    Hi MTB,

    Sorry to bother you again. How to change the code to return column letter rather than column number?

    How to return the cell address with the column letter and row number?

    Thank you very much!

  10. #10
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    To return the cell address use

    ColumnNo = sht.Cells.Find(What:=strText, After:=objXL.Cells(1, 1), LookIn:=xlFormulas, LookAt _
    :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
    False).Address

    You will need to change the fuction return type to String

    If you just want the coulmn letter(s) you need to parse the address returned above (either inside the function or after the function has returned the address).

    MTB

  11. #11
    Join Date
    Aug 2007
    Posts
    88
    Quote Originally Posted by MikeTheBike View Post
    Hi

    To return the cell address use

    ColumnNo = sht.Cells.Find(What:=strText, After:=objXL.Cells(1, 1), LookIn:=xlFormulas, LookAt _
    :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
    False).Address

    You will need to change the fuction return type to String

    If you just want the coulmn letter(s) you need to parse the address returned above (either inside the function or after the function has returned the address).

    MTB
    Thank you so much for help!
    It works and returns something like $I$2.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •