Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
lookup values from tabdeliminated text file
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
lookup values from tabdeliminated text file
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 DataImport External DataNew Database Query, XL 2007/10 that would be InsertTableFrom 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. On Feb 15, 5:39*am, " wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
lookup values from tabdeliminated text file
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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
lookup values from tabdeliminated text file
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! -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
lookup values from tabdeliminated text file
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup (V or H) are for values, Anything for Text?! | Excel Discussion (Misc queries) | |||
Lookup returns hyperlinked values as normal text | Excel Discussion (Misc queries) | |||
Lookup Function Referencing cells, not text values | Excel Worksheet Functions | |||
How do I LOOKUP text values | Excel Worksheet Functions | |||
Lookup text values | Excel Worksheet Functions |