lookup values from tabdeliminated text file

[baha17]

Hi All,
I have read through all post on net in this weekend about my problem,
however I could not get right answer. I have a text file which is
named as stafflist with user id numbers, name and positions. Let's say
as below
1039 Tony Adwards SUP-V
1277 John Philips DRGSM-V
1326 Ken Through DRGSM-V
.......

That is the file with 5000 users. I do not want to keep those user
details in excel file, because it takes a lot of space.Is there any
way to write a code to lookup value from that text file. Let's say
once enter in the id number( 1039 ) textbox1 textbox2 to display the
name and textbox3 display the position.
Thank you for the help
Baha

 

[AB]

From the top of my head you have 2 options:
(1) connect tothe text file via ado/dao connection object and upen it
up as a recordset. Then do the things you want.
(2) insert a querytable (XL2002/03 that would be Data>Import External
Data>New Database Query, XL 2007/10 that would be Insert>Table>From
External Data Sourse (or something like that)). Then you can have your
ordinary vlookups pull the data from the query/table. With this one
pretty much no coding involved.

A.

 

[GS]

(e-mail address removed) presented the following explanation :

Hi All,
I have read through all post on net in this weekend about my problem,
however I could not get right answer. I have a text file which is
named as stafflist with user id numbers, name and positions. Let's say
as below
1039 Tony Adwards SUP-V
1277 John Philips DRGSM-V
1326 Ken Through DRGSM-V
......

That is the file with 5000 users. I do not want to keep those user
details in excel file, because it takes a lot of space.Is there any
way to write a code to lookup value from that text file. Let's say
once enter in the id number( 1039 ) textbox1 textbox2 to display the
name and textbox3 display the position.
Thank you for the help
Baha

I'm thinking you could read the file into an array, or into a
recordset, or onto a hidden sheet. Then load the ID#s into a ComboBox
that is programed to auto-complete as you type. Optionally, the ID#
could be selected from its dropdown, OR continue keying the first
character so the control cycles through all entries beginning with that
character.

When the ID# you want is found the 2 textboxes update with the
appropriate info for the displayed ID#.

 

[baha17]

Guys Thanks for the answer but that is not quite what I need. I just
want to read them directly from the text file.Is that possible?

 

[GS]

(e-mail address removed) presented the following explanation :

Guys Thanks for the answer but that is not quite what I need. I just
want to read them directly from the text file.Is that possible?

Well AB's suggestion #1 AND all mine DO READ the data DIRECTLY FROM THE
text FILE. So your saying that's not what you need but IT IS what you
want. Now I'm confused!

 

[Rick Rothstein]

Here is a subroutine that should do what you want...

Sub GetPersonInfo(ID As String, TheName As String, Position As String)
Dim FileNum As Long, TotalFile As String, Info() As String, Data As String
Const PathAndFileName As String = "C:\Temp\TestFile.txt"
FileNum = FreeFile
Open PathAndFileName For Binary As #FileNum
TotalFile = Space(LOF(FileNum))
Get #FileNum, , TotalFile
Close #FileNum
Info = Split(vbNewLine & TotalFile, vbNewLine & ID & " ", 2)
If UBound(Info) > 0 Then
Data = Trim(Split(Info(1), vbNewLine)(0))
Position = Mid(Data, InStrRev(Data, " ") + 1)
TheName = Left(Data, InStrRev(Data, " ") - 1)
Else
Position = "<<Invalid ID specified>>"
TheName = "<<Invalide ID specified>>"
End If
End Sub

Note... this is a **subroutine** not a macro; hence it must be called from
your own code (which could be a macro, another subroutine or even a
function). You supply the ID argument (which you have indicated would come
from TextBox1) and provide two String arguments to receive the information
you seek back from the subroutine. Here is an example of it being used...

Private Sub CommandButton1_Click()
Dim ID As String, Person As String, Job As String
ID = 1039
GetPersonInfo ID, Person, Job
MsgBox "ID: " & ID & vbLf & "Person: " & Person & vbLf & "Job: " & Job
End Sub

This example simply shows a MessageBox displaying the information for ID
#1039... in the program you suggested you needed this for, you would assign
the Person variable's contents to TextBox2 and the Job variable's contents
to TextBox3 and perform any other necessary code after that. Note that if an
invalid ID number is passed into the subroutine, the TheName and Position
arguments will be set to the text String "<<Invalid ID specified>>"... you
can, of course, handle that situation any way that you want inside the Else
block reserved for that code.

Rick Rothstein (MVP - Excel)




wrote in message

Hi All,
I have read through all post on net in this weekend about my problem,
however I could not get right answer. I have a text file which is
named as stafflist with user id numbers, name and positions. Let's say
as below
1039 Tony Adwards SUP-V
1277 John Philips DRGSM-V
1326 Ken Through DRGSM-V
.......

That is the file with 5000 users. I do not want to keep those user
details in excel file, because it takes a lot of space.Is there any
way to write a code to lookup value from that text file. Let's say
once enter in the id number( 1039 ) textbox1 textbox2 to display the
name and textbox3 display the position.
Thank you for the help
Baha