import txt files with more than 256 col and other tips

I need to import a txt file, that contains a large number of columns
(separated by TAB) ,as follows:
1)jump the first two lines of *.txt
2)columns widths are [10 10 10 10 10 19 15....(from this
point =15 for every column)
3)import columns from 1 to 240 in sheet1
import columns from 241 to 276 in sheet2
...and so on

I search throgh google and NG reading a lot of suggestion, but still I can't
do this (important) thing...:-(

hoping this could help, here are the 3rd line of txt files (from whic I need
to do import)

0,000028 0,000032 0,000047 0,000095 0 0,00000838 0,00000052 0,00000319

If it is tab separated, then the column widths make no difference. Which is
it? fixed width, or tab delimited.

--
Regards,
Tom Ogilvy

"uriel78" <[email protected]> wrote in message
news:[email protected]...
> I need to import a txt file, that contains a large number of columns
> (separated by TAB) ,as follows:
> 1)jump the first two lines of *.txt
> 2)columns widths are [10 10 10 10 10 19 15....(from this
> point =15 for every column)
> 3)import columns from 1 to 240 in sheet1
> import columns from 241 to 276 in sheet2
> ...and so on
>
> I search throgh google and NG reading a lot of suggestion, but still I
can't
> do this (important) thing...:-(
>
> hoping this could help, here are the 3rd line of txt files (from whic I
need
> to do import)
>
> 0,000028 0,000032 0,000047 0,000095 0 0,00000838 0,00000052 0,00000319
>
>

Open the file using either excel's intrinsic file handling or use the
FileSystemObject if you prefer.

Reading the file one line at a time, split each line into an array
using Split()

dim v, x as integer, y as integer,s as integer, r as long

r=1

'open file

s=1
y=1
'read a line from the file
v=Split(theLine,vbTab)
for x=lbound(v) to ubound(v)
thisworkbook.sheets("Sheet" & s).cells(r,y).value=v(x)

if y>240 then
y=0
s=s+1
end if
y=y+1
next x

r=r+1

'loop and read next line


totally untested.

Tim


>
> "uriel78" <[email protected]> wrote in message
> news:[email protected]...
>> I need to import a txt file, that contains a large number of
>> columns
>> (separated by TAB) ,as follows:
>> 1)jump the first two lines of *.txt
>> 2)columns widths are [10 10 10 10 10 19
>> 15....(from this
>> point =15 for every column)
>> 3)import columns from 1 to 240 in sheet1
>> import columns from 241 to 276 in sheet2
>> ...and so on
>>
>> I search throgh google and NG reading a lot of suggestion, but
>> still I
> can't
>> do this (important) thing...:-(
>>
>> hoping this could help, here are the 3rd line of txt files (from
>> whic I
> need
>> to do import)
>>
>> 0,000028 0,000032 0,000047 0,000095 0 0,00000838 0,00000052
>> 0,00000319
>>
>>
>
>

It seems to be a very good idea...but I'm totally a newbie with VBA and
programming...and so I can't complete the routine with the import and the
looping...maybe I could send you a sample of my txt file....(about 3Mb...?)

uriel,

If you still need help you can send a file (make the obvious changes
to my e-mail address). If it's 3MB then you should zip it first.

Tim.

PS. It helps when posting if you include at least part of the text of
the message you're replying to.


"uriel78" <[email protected]> wrote in message
news:[email protected]...
> It seems to be a very good idea...but I'm totally a newbie with VBA
> and
> programming...and so I can't complete the routine with the import
> and the
> looping...maybe I could send you a sample of my txt file....(about
> 3Mb...?)
>
>

Thanks for your courtesy, I think I've found a little trick (See post below
in reply to Tom ) that solve my specific problem...:-))

If you want for your interest/research/curiosity I can send you the txt
file and my relative solution (a veryveryvery newbie solution)

Now I go and put my head into ice, 'cause it is burning...:-)

"Tim Williams" <saxifrax@pacbell*dot*net> ha scritto nel messaggio
news:%[email protected]...
> uriel,
>
> If you still need help you can send a file (make the obvious changes
> to my e-mail address). If it's 3MB then you should zip it first.
>
> Tim.
>
> PS. It helps when posting if you include at least part of the text of
> the message you're replying to.

sorry, fixed widht...

well, maybe I've found a way to bypass my trouble by using querytables.add
macro four times (each time for different intervals)
....but still have some questions about the possibility to use a dialog
window to browse for the input file instead of specifying it in the macro...
just open another 3ad for this question...

How does querytables solve the 256 column problem?

--
Regards,
Tom Ogilvy

"uriel78" <[email protected]> wrote in message
news:[email protected]...
> well, maybe I've found a way to bypass my trouble by using querytables.add
> macro four times (each time for different intervals)
> ...but still have some questions about the possibility to use a dialog
> window to browse for the input file instead of specifying it in the
macro...
> just open another 3ad for this question...
>
>

It doesn't solve the problem itself in general, but in my case it works
fine...
My file text is subidived into columns that I can consider forming 4 groups.
I turn on Macro REcorder and then import the text file (Data->import)
choosing the first group of columns.
Then just take a look to the macro recorded and coy it three times.

So I run 4 times the query everytime changing TextFileColumnDataTypes
property by defining which columns to take...


ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\Documents and Settings\elab1.txt _
, Destination:=Range("A1"))
......
....
....
..TextFileColumnDataTypes =Array(...)


I think it's rather a newbie trick, but if I consider I'm working with VBA
only for 3days in my life...it should be more than nothing....:-)

ehm...I hope you're an excel guru :-)....can you take a look to my other
post "Definition of a statistical function..."

I'm struggling with it...:-//