PDA

View Full Version : [SOLVED] InStr exact match



white_flag
12-17-2013, 03:41 AM
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!

sassora
12-17-2013, 04:13 AM
Hi white_flag

Could you make use of the equal sign?

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

Aflatoon
12-17-2013, 04:40 AM
You could use Split:
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

white_flag
12-17-2013, 05:03 AM
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.

snb
12-17-2013, 10:29 AM
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

white_flag
12-17-2013, 11:55 AM
Hi snb,
This is a nice one!. Thanks!!!