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
>>
>>
>
>

sorry, fixed widht...

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...?)

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...:-//

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.

This works great.
What would I need to do to import one text file into the current workbook?
Or better yet, is there a way to import multiple long text files (500 columns) into the current workbook?

Joerg

Originally Posted by Hellboy

Sub Auto_open()
Dim szFile As String
Dim szLine As String
Dim tabl() As String
Dim szR As String
Dim iCols As Integer
Dim iA As Integer
Dim iFileNo As Integer
Dim iLines As Integer
Dim strInstring As String
Dim intInstring As Integer
' szDefaultDir = Cells(2, 3)
' ChDir szDefaultDir


vrtFiles = Application.GetOpenFilename("*.*, *.*", , "Fichier de Plus de 255 Column", , True)
Application.ScreenUpdating = False
For Each fileToOpen In vrtFiles
If fileToOpen <> False Then
bolStopAddSheet = True
szShortName = fileToOpen
szXLSfile = fileToOpen & ".XLS"
Workbooks.Add
Rem ActiveWorkbook.SaveAs szXLSfile
iFileNo = FreeFile
Open fileToOpen For Input As #iFileNo
iLines = 1
While Not EOF(iFileNo)
Line Input #iFileNo, szLine
szLine = Trim(szLine)
While Left(szLine, 1) = Chr(9) Or Left(szLine, 1) = ","
szLine = Mid(szLine, 2, Len(szLine))
Wend
While Right(szLine, 1) = Chr(9) Or Right(szLine, 1) = ","
szLine = Mid(szLine, 1, Len(szLine) - 1)
Wend
For intChar = 1 To 4
Select Case intChar
Case 1
intInstring = InStr(1, szLine, Chr(9)) 'Tabulation
Case 2
intInstring = InStr(1, szLine, Chr(32)) 'Space
Case 3
intInstring = InStr(1, szLine, ",") 'Comma
Case 4
intInstring = InStr(1, szLine, ";") '
End Select
If intInstring > 1 Then
strInstring = Mid(szLine, intInstring, 1)
Exit For
End If
Next intChar
szR = SplitFullCabane(tabl, szLine, strInstring, iLines)
iLines = iLines + 1
Wend
Close #iFileNo
End If
Sheets(1).Select
Next fileToOpen
End Sub

Function SplitFullCabane(tabstrTableau() As String, strLigne As String, strSeparateur As String, intLines As Integer)

Dim nLoop As Integer
ReDim tabstrTableau(0, 254)
iSheet = 1
nLoop = 0
While InStr(strLigne, strSeparateur) > 0
tabstrTableau(0, nLoop) = Trim(Left(strLigne, InStr(strLigne, strSeparateur) - 1))
strLigne = Mid(strLigne, InStr(strLigne, strSeparateur) + 1)

While Left(strLigne, 1) = strSeparateur
strLigne = Mid(strLigne, 2)
Wend
nLoop = nLoop + 1
If nLoop = 255 Then
Rem iSheet = iSheet + 1
Sheets(iSheet).Range(Sheets(iSheet).Cells(intLines, 1), Sheets(iSheet).Cells(intLines, 255)) = tabstrTableau
iSheet = iSheet + 1
If bolStopAddSheet = True Then
Sheets.Add after:=Sheets(iSheet - 1)
End If
ReDim tabstrTableau(0, 0)
ReDim tabstrTableau(0, 254)
nLoop = 0
End If
Wend
tabstrTableau(0, nLoop) = strLigne
Sheets(iSheet).Range(Sheets(iSheet).Cells(intLines, 1), Sheets(iSheet).Cells(intLines, 255)) = tabstrTableau
ReDim tabstrTableau(0, 0)
ReDim tabstrTableau(0, 254)
If iSheet > 1 Then bolStopAddSheet = False
End Function