Extract text file and import to excel in VB6

[new2computer]

Hi All,

I have a text file with name, address, phone and some of unuseful line
of text. I would like to extract only name, address, and phone and put them into 3 column in excel.

My general approach is
1-Open text file and read start a loop to read in one line at a time.
[syntax??]
2-Search for key words(Name, Address, Phone) the imply the desired
string to extract [how do I "if line contains "Name" then...??
Put them into a variable string or array [syntax??] Leading blank space
may cause the error. [syntax?]
3-When all desired data is collected, write them in an excel column.

As you can tell I'm new to VB. Any and all help appreciated.

[new2computer]

Here is a sample text file:

Teststring
Teststing dfdsfdsfdsafdsfdsfdsfsdfsdfI
Teststimg
Resrerere
Erewrewrwerwer
Rewrewrewr
Rwerewr
Fdsfsdf
Ewrewrwer


printer-friendly page


Meetings


rewrewrewrewrewr


NameTonyLee
Address12345maintstree
Phone123456789


NameTonyLee
Address12345maintstree
Phone123456789

[cssriraman]

can you upload a sample text file?

[new2computer]

Pls see attached sample text file. Thk!

[bushmobile]

1) Check out these threads:
http://www.vbforums.com/showthread.php?t=342619
http://www.vbforums.com/showthread.php?t=405051

2) Check out:
InStr
Trim
http://www.vbforums.com/showthread.php?t=372504

3)Check out:
http://www.vbforums.com/showthread.php?t=391665

[new2computer]

I'm able to search for Name. Pls point me to direction to grab string after Name which is the string that I'm intersted in import to Excel. Ex: NameTony Lee, capture Tony Lee and import it later into excel. Thk!

[bushmobile]

when you find Name using InStr it will return a value of the position.

Assuming you're reading line by line, you can then do something like this:

Line Input #1, strLine
lngPos = InStr(strLine, "Name")
' If "Name" is found then lngPos will be > 0 hence
If lngPos Then strName = Mid$(strLine, lngPos + 4)
' Then do what you want with strName

[new2computer]

Hi,

myFoundString = InStr(1, StringLine, SearchStringName, 1)
If myFoundString Then strName = Mid$(StringLine, myFoundString + 6)
TextBox2.Text = myFoundString

[bushmobile]

is that question?

you want TextBox2.Text = strName

[new2computer]

Hi,

I tried your code with different variable name, it returns 0 means nothing found. Wonder what I miss?

Line Input #fileno, StringLine
myFoundString = InStr(1, StringLine, SearchStringName, 1)
If myFoundString Then strName = Mid$(StringLine, myFoundString + 6)
TextBox2.Text = myFoundString

On the other hand my approach slightly different encounter bug at SearchStringName +4. I'm stuck? Pls help!


SearchStringName = "Name"
fileno = FreeFile

'open the file for reading
Open myFileName For Input As #fileno
While Not EOF(fileno)
Line Input #fileno, StringLine
If InStr(1, StringLine, SearchStringName, 1) Then
myFoundString = Mid(StringLine, SearchStringName +4)
TextBox2.Text = myFoundString
End If
Wend

[cssriraman]

do you have any question on post #8?

myfoundstring will return integer value. are you trying to display it in textbox2??????

[new2computer]

Pls ignore Post#8 just accidently hit enter. I meant to submit post#10

[cssriraman]

what type of error you are getting?

[bushmobile]

well your trying to add numbers on to string values.

you might be better off using Trim instead:

Open myFileName For Input As #fileno
    Do Until EOF(fileno)
        Line Input #fileno, StringLine
        If InStr(1, StringLine, SearchStringName, vbTextCompare) Then
            myFoundString = Mid$(Trim$(StringLine), Len(SearchStringName) + 1)
        End If
    Loop
Close #fileno

[new2computer]

Received " Run time error 13, Type Mismatch"

[new2computer]

I understand the general ideas of your code and the error I got. Thanks for the input.

Well, the text box displays the following "FoundString = Mid(StringLine, SearchStringName +4)". I quite not sure the use of Mid, Trim and Len all in one.