How to locate a text in an excel file using vb?

[seraphicmortal]

Hi! I don't know if anyone can help me with this. I would like to find a text (which may vary for example: rev1 or rev 1) in an excel file provided the worksheet is specified using VB6 and show only the 1 (instead of rev1) in a textbox.

Example:

excel file = helpme.xls (with 3 worksheets available is help1, help2, help3)
specified worksheet = help1
text to find = rev1 (or rev 1 --> there may be times where there is a space between rev and 1.)
text to show in textbox = 1

Please help.

[Ecniv]

Have a look at .Find method on a range of cells.
I think you can use it on a worksheet, but usually its on a range of cells.

You can use wildcards and I think it would be "rev*1" or just "rev".
Once you have the cell, you need to us string manipulation, such as mid or left.

Post up if/when you have code and its not working quite right.

[seraphicmortal]

It still doesn't return the numeric (4) in rev 4 or rev4.

Here's the code:

(frm_loadfile)
Option Explicit
 
Dim xl0a As New Excel.Application
Dim xl0b As Excel.Workbook
Dim xl0s As Excel.Worksheet
Public var As Variant  ' variable for project
Public var1 As Variant 'variable for package code
Public var2 As Variant 'variable for parts list version
 
Private Sub loadwrksht()
Dim X As Integer
Dim xtotal As Integer
Dim counter As Integer
Dim project As String
Dim packagecode As String
Dim plversion As String
 
 
    Set xl0a = New Excel.Application
    Set xl0b = xl0a.Workbooks.Open(txt_filename.Text)
    
    xtotal = xl0b.Worksheets.Count
    lst_wrksht.Clear
    For counter = 1 To xtotal
        Set xl0s = xl0b.Worksheets(counter)
        lst_wrksht.AddItem xl0s.Name
    Next counter
 
    var = xl0s.Range("A1").Value
    var1 = xl0s.Range("A2").Value
    var2 = xl0s.Range("C4").Find("rev")
    txt_version.Text = var2
    
    
    project = var
    packagecode = var1
    plversion = var2
    
    xl0b.Close
    xl0a.Quit
    Set xl0s = Nothing
    Set xl0b = Nothing
    Set xl0a = Nothing
    
End Sub
 
Function GetVer(tmpStr As String, tmpSpc As String, Mode As String) As String
Dim tmpRetStr As String
Dim tmpLen As Integer
Dim tmpErr As Integer
  ' *****************************************************
  ' tmpString = The whole string
  ' tmpSpc = Space chr
  ' if mode = "F" then get the string in front of the div
  ' if mode = "B" then get the string in back of the div
  ' if mode = "C" then get the string in the middle
  '
  ' Return values:
  '  Errors
  '    Err1 = wrong mode ("F" & "B" ok)
  '    Err2 =  No Div, did not found a divider
  '    Err3 = No F, did not find any string in front of the div
  '    Err4 = No B, did not find any string in back of the div
  '    Err5 = No String
  '    Err6 = No C, did not find any string in the middle
  '  Normal
  '    String
  ' ******************************************************
  ' Example: GetVer("Red=Green","=","F") will retrun "Red"
  ' *** for string
  tmpErr = 0
  If Len(tmpStr) = 0 Then
    tmpErr = 5
    GoTo GetVerErr
    
 End If
  
  ' *** test for chr in tmpDiv
  If Len(tmpSpc) = 0 Then
    tmpErr = 2
    GoTo GetVerErr
'        MsgBox ("Error: Non-standard format in saving profile name.  No hyphen '-' found.")
'        frm_dir.txt_plprofile.Text = "*.plp"
  End If
 
  ' *** test for div in string
  If InStr(1, tmpStr, tmpSpc) = 0 Then
    tmpErr = 2
    GoTo GetVerErr
'    MsgBox ("Error: Non-standard format in saving profile name.  No hyphen '-' found.")
'    frm_dir.txt_plprofile.Text = "*.plp"
  End If
 
  ' *** process "F"
  If Mode = "F" Then
     tmpRetStr = Left(tmpStr, (InStr(1, tmpStr, tmpSpc) - 1))
     If Len(tmpRetStr) > 0 Then
       GetVer = tmpRetStr
       Exit Function
     Else
       tmpErr = 3
       GoTo GetVerErr
     End If
  End If
 
 
 
  
    tmpErr = 1
    frm_dir.txt_plprofile.Text = "*.plp"
    'GoTo GetVerErr
  Exit Function
GetVerErr:
GetVer = "Err" & tmpErr
End Function

(frm_summary)
Private Sub Form_Load()
Dim plver As String
Dim prof As String
    
    plver = frm_loadfile.GetVer(frm_field.txt_version, " ", "F")
    lbl_versiondata.Caption = plver
    
End Sub

Anyone, hope you can help.

[Ecniv]

Looks like the code works for the finding and returning the string.
Have you debugged it (stepped through to see what is happening) ?
Have you tested it?
I ran it for the following line and it worked fine... (immediates window)

?GetRev("Rev 1"," ","F")

From your onload spreadsheet code this line

txt_version.Text = var2

would return the first matching cell only. You didn't call splitting function you wrote.

Also note that .Find returns a cell or nothing. You may need to check that you have something.



In your original post you asked how to find a cell matching rev and then return the numeric part of that... Assuming cells contain Rev1, Rev 1, rev 2 etc..

Try this?

Public Function ReturnDaNum(ByVal strWhole As String, strSearch As String) As Long

    Dim lngPosition As Long
    
    On Error Resume Next
    
    ReturnDaNum = 0
     
    If Len(strWhole) = 0 Or Len(strSearch) = 0 Then Exit Function
    
    lngPosition = InStr(1, LCase(strWhole), LCase(strSearch))
    If lngPosition > 0 Then
        lngPosition = lngPosition + Len(strSearch) - 1
        ReturnDaNum = CLng(Trim(Right(strWhole, Len(strWhole) - lngPosition)))
    End If
    
    If Err.Number <> 0 Then
        MsgBox "Error : " & Err.Number & vbCrLf & Err.Description, vbOKOnly + vbExclamation, "Error"
    End If
    
End Function

This should (I hope) cover most things, but you may want to use this and tweak it more to your needs.

[VBAhack]

Not sure you are using the find method correctly. Should look something like:

Cells.Find(What:="rev1", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate

Suggest you open Excel, go to the sheet you want to find the text, select cell A1, turn on the macro recorder (menu Tools -> Macro -> Record new macro), click Edit -> Find, type in the the text you want to find, stop the recorder, and look at the resulting code. You can tweek it from there.

VBAhack

[Ecniv]

VbaHack:
Using rng.find("text") will return. I tried with an excel sheet and all those options done and it either crashed or refused to run (cannot remember which). However, just the search text did what I wanted.

It's only a starting point, and they should read the help file on it anyway.