Anthony's VBA Forum

[bshaen]

test.txt
1 A A Flute 1.00 1.00 1.00
2 A1 A1 FLUTE 1.00 0.00 1.00
3 AB AB Flute /Double Wall 1.00 2.50 1.00
4 ABC ABC Flute /Triple Wall 1.00 2.50 1.00
5 ABF ABF 0.00 0.00 1.00

test1.txt
1 A A Flute 1.00 1.00
2 A1 A1 FLUTE 1.00 0.00
3 AB AB Flute /Double Wall 1.00 2.50
4 ABC ABC Flute /Triple Wall 1.00 2.50
5 ABF ABF 0.00 0.00

i have write a program which is about vba-excel, read the text file and display the data into excel format.
that have successful to run the program, but the problem is whether that is possible to use the single program that able to know how many column of particular taxt file i want to import, and width of each col? the program is not read 2 text file concurrently, that is depend on user what text file they want to execute.

With xlsheet.QueryTables.Add(Connection:="TEXT;\\Admin1\C\test.txt", _
Destination:=Range("A1"))
.TextFileParseType = xlFixedWidth
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1)
.TextFileFixedColumnWidths = Array(5, 16, 34, 5)
.Refresh
End With
**that is the coding i done for the text file, we already know how many col that we need.

the problem now is we should assume we don't know how many column need for each text file's data to display on excel.
_________________
bshaen

[Data]

Don't think too complicated. If your text file in delimited (ex: by space), you can that your program count how many spaces you have in the line of string. The number of space + 1 is your columns number.
_________________
Data
Knowledge is Power

[bshaen]

how the coding i can apply???
casue i know
.TextFileConsecutiveDelimiter = True
but i don't know how to specific to 2 space??? can u give me some idea of it? thank
_________________
bshaen

[Data]

You can use the [b]instring[/b] function in VB that break the string into array. By counting how many arrays in generated in the string, you get the columns.
_________________
Data
Knowledge is Power

[bshaen]

sorry, can u futher explain of the instring function with example???
_________________
bshaen

[Djamanbe]

Thanks a lot Anthony for your clean parser example.

Jean-Marc