From a ms knowledge base article:
If you open a text file while recording a Visual Basic macro in Microsoft Excel
5.0, when you run the resulting subroutine, you may receive an "Out of
memory" message if the text file you opened contains more than 69 columns.
In Microsoft Excel 7.x, you will receive the error message if the text file
contains more than 55 columns. In Microsoft Excel 97, you will receive the
error message if the text file contains more than 52 columns.
One of their workarounds was to set up your own array and use that instead of
the explicit "array(array(" stuff.
Did you try numbers bigger than 50 in the example I sent?
> Thank you for your solution. I tested in xl97sr2, but there was error when
> the "array(array(" is as large as 50, so I set it to 45. Do you encounter
> the same problem? Also, do you know whether the upper limit is due to the
> size of memory, version of Excel or anything else? Thanks!
> > I'm using xl97sr2, but it might work with 95.
> > Excel seems to be very forgiving if you specify too many fields (more than
> > have in the input file), so I don't think that I'd worry about specifying
> > many columns. But excel has a limit on how many "array(array("'s you can
> > (varies between versions).
> > So maybe you could just do something like:
> > Option Explicit
> > Sub Macro1()
> > Dim i As Integer
> > Dim Columnarray(1 To 99, 1 To 2)
> > For i = 1 To 99
> > Columnarray(i, 1) = i
> > Columnarray(i, 2) = 2
> > Next i
> > Workbooks.OpenText FileName:=f_name, Origin:=xlWindows, _
> > StartRow:=1, DataType:=xlDelimited, _
> > TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
> Tab:=True, _
> > Semicolon:=False, Comma:=False, Space:=False, Other:=True, _
> > OtherChar:=";", FieldInfo:=Columnarray
> > End Sub
> > Watchout for the otherchar:=, I used ";" in my testing.
> > Adjust the 99 to as many fields as you think that you'll need (both
> > > Hi all,
> > > I am writing a macro to automatically convert a text file
> > > many columns separated by predefined delimiter. I want to tell EXCEL to
> > > all columns in text format, which I know can be done by the following
> > > Workbooks.OpenTextFileName:=f_name, ......
> > > fieldinfo:=Array(Array(1, 2), Array(2, 2), Array(3, 2), Array(4,
> > > Array(5, 2)..... Array(n, 2))
> > > The problem is: the number of columns contained in the text file
> > > from one to another, I don't exactly know the optimal value of N. It
> > > seems to be quite silly to define the exact number of columns in the
> > > but not using a more generic command to tell EXCEL to convert *ALL*
> > > into text. Anyone can tell me such a command (of course in a macro
> > > preferably suitable for EXCEL 5.0 and any higher version)? Many thanks!
> > > Best regards,
> > > Birdy
> > --
> > Dave Peterson