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

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

:confused: Please help. :confused:

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.

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

Here's the code:

VB 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

VB Code:

(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.

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)

Code:

---

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

---

From your onload spreadsheet code this line

Quote:

---

Code:

---

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?

Code:

---

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.

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

VB Code:

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

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.