Solved: Import data from text file

[Mandy]

hi
suppose i have two text box.
one for entering the file name
and other to specified a seprator may be (, or ; )

based on this information, i want to write a code behind the command button that can import data from the specified text file into excel .
can any ne help me.

Thanks.

[lucas]

Hi Mandy,
I'm not sure why you would need those text boxes but...this will show you how to import and decide on a delimiter...don't have time right now to go further into your problem but I'm pretty sure this will get you started...see attached.

[Mandy]

hello sir
Thanks
This is the excetly same example that i was looking on following web address
http://www.cpearson.com/excel/imptext.htm

can u give me some more time, by explaning the code after the if statement. up end if statement i can understand.
plz if you can give a bit more time that will be really helpful.

Thanks

[lucas]

This part Mandy?
[VBA]Sep = InputBox("Enter a single delimiter character.", _
"Import Text File")
ImportTextFile CStr(FName), Sep[/VBA]

[Mandy]

no sir
follwing one

[VBA]
ColNdx = SaveColNdx
Pos = 1
NextPos = InStr(Pos, WholeLine, Sep)
While NextPos >= 1
TempVal = Mid(WholeLine, Pos, NextPos - Pos)
Cells(RowNdx, ColNdx).Value = TempVal
Pos = NextPos + 1
ColNdx = ColNdx + 1
NextPos = InStr(Pos, WholeLine, Sep)
Wend
RowNdx = RowNdx + 1
[/VBA]

[lucas]

[VBA]ColNdx = SaveColNdx[/VBA]
derived from earlier in the code:
[VBA]SaveColNdx = ActiveCell.Column[/VBA]

[VBA]NextPos = InStr(Pos, WholeLine, Sep) [/VBA]
Instr defined as: Returns a Variant (Long) specifying the position of the first occurrence of one string within another
syntax for InStr is: InStr([start, ]string1, string2[, compare])

[Mandy]

Thanks sir for your time
really thanks

[lucas]

Your welcome Mandy. Most of the info is available in the vbe help files. Be sure to mark your thread solved if you got the solution. You can post followup questions after marking it solved.