Consulting

Results 1 to 6 of 6

Thread: InStr exact match

  1. #1
    VBAX Mentor
    Joined
    Dec 2009
    Posts
    416
    Location

    InStr exact match

    Hello
    I am trying to populate some controlers (Labels/OptionButtons etc) from an text file.
    The text file has inside something like this:

    Label1=Name
    Label2=e-mail
    Label11= Adress

    etc..

    See the code bellow:

    Function TxtUserForm(ByRef uForm As UserForm, ByRef NumeControl As String)
    
    Dim sTemp As String
    Dim nFile As Integer
    Dim fs, f
    Dim j As Integer
    Dim nEqualsSignPos As Integer
        nFile = FreeFile()
        Set fs = CreateObject("Scripting.FileSystemObject")
    
    
                    Set f = fs.GetFile(ThisWorkbook.Path & "\excel.1")
                        Open ThisWorkbook.Path & "\excel.1" For Input As #nFile
                            Do While Not EOF(nFile)
                                Input #nFile, sTemp
                                    If Len(sTemp) > 0 Then
                                        nEqualsSignPos = InStr(sTemp, "=")
                                        nEqualsSignPos = nEqualsSignPos + 1
                                            If InStr(sTemp, NumeControl) Then
                                                uForm.Controls(NumeControl).Caption = Mid(sTemp, nEqualsSignPos)
                                                'Exit Do
                                            End If
                                    End If
                            Loop
                    Close nFile
    
    
    End Function

    The problem is InStr doesn't find the exact match for example instead to put on
    Label1 Name is putting Address. It is confusing Label1 with Label11
    So can some how to "force" InStr to find the exact match?

    If I putted in the order like
    Label1=txt1
    Label2=txt2
    Label3=txt3

    Label11=txt11
    etc ...
    and I add 'Exit Do after the first mach, the result is ok.
    But the text file is not all the time in a correct order for example
    LAbel11=txt11
    Label12=txt12
    Label1=txt1

    Thank you!

  2. #2
    VBAX Tutor
    Joined
    Jan 2008
    Posts
    258
    Location
    Hi white_flag

    Could you make use of the equal sign?

    "Label1=" would only find Label1
    "Label11=" would give Label11
    sassora

  3. #3
    VBAX Master Aflatoon's Avatar
    Joined
    Sep 2009
    Location
    UK
    Posts
    1,597
    Location
    You could use Split:
    [vba]Function TxtUserForm(ByRef uForm As UserForm, ByRef NumeControl As String)

    Dim sTemp As String
    Dim nFile As Integer
    Dim fs, f
    Dim vParts
    nFile = FreeFile()
    Set fs = CreateObject("Scripting.FileSystemObject")


    Set f = fs.GetFile(ThisWorkbook.Path & "\excel.1")
    Open ThisWorkbook.Path & "\excel.1" For Input As #nFile
    Do While Not EOF(nFile)
    Input #nFile, sTemp
    If Len(sTemp) > 0 Then
    vParts = Split(sTemp, "=")
    If LCase$(vParts(0)) = LCase$(NumeControl) Then
    uForm.Controls(NumeControl).Caption = vParts(1)
    'Exit Do
    End If
    End If
    Loop
    Close nFile

    End Function

    [/vba]
    Be as you wish to seem

  4. #4
    VBAX Mentor
    Joined
    Dec 2009
    Posts
    416
    Location
    If I add "" on a text string, the result text on controls is empty.
    But I like solution provided by Aflatoon. And I say tank you! for that.

  5. #5
    Knowledge Base Approver VBAX Guru
    Joined
    Apr 2012
    Posts
    4,718
    sub M_snb()
        sn=filter(split(replace(createobject("scripting.filesystemobject").opentextfile(thisworkbook.Path & "\exel.1").readall,"=","=" & vbcrlf),vbcrlf),"=",false)    
    
        for j=0 to ubound(sn)
            controls(j+1).caption=sn(j)
        next
    End Sub

  6. #6
    VBAX Mentor
    Joined
    Dec 2009
    Posts
    416
    Location
    Hi snb,
    This is a nice one!. Thanks!!!

Posting Permissions

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