OpenText Method of Workbook Object Question
 

I have a large ASCII file I want to import into excel.

The problem is that this file has over 1,800 columns of data and Excel only
has 256. So after I import the file I get a nice message that says, "Not All
Data Imported" or something to that effect. Great.

I noticed that there is a nifty Option group on the third step of the Text
Import Wizard that allows you to skip columns. This is semi-good because now
I can import all the data 256 columns at a time... but that sucks because
there are 1,800 columns in the file. And it would be tedious if not
impossible to actually try and count 256 columns in that little preview
screen Excel provides.

I thought I would write a quick function to do this by modifying the
FieldInfo Argument of the OpenText Method to reflect a new group of 256
columns. However, this requires me to create an array that has 256
dimensions... which alas is not allowed.

Any thoughts? I really don't want to have to import 256 columns at a
time... This wouldn't be too bad if it were one file... but there are
hundreds...

John

OpenText Method of Workbook Object Question
 

is the file fixed width (columns determined by position) or is it delimited
(if so, what is the delimiter).

Are you asking how you can get 256 columns at a time so you want 1800/256 =
8 worksheets of data.

If you want to figure it out on your own, you might look at this article:

http://support.microsoft.com/default...b;en-us;134826
"Out of Memory" Message Using the OpenText Method



--
Regards,
Tom Ogilvy

"John" wrote in message
...
I have a large ASCII file I want to import into excel.

The problem is that this file has over 1,800 columns of data and Excel
only
has 256. So after I import the file I get a nice message that says, "Not
All
Data Imported" or something to that effect. Great.

I noticed that there is a nifty Option group on the third step of the Text
Import Wizard that allows you to skip columns. This is semi-good because
now
I can import all the data 256 columns at a time... but that sucks because
there are 1,800 columns in the file. And it would be tedious if not
impossible to actually try and count 256 columns in that little preview
screen Excel provides.

I thought I would write a quick function to do this by modifying the
FieldInfo Argument of the OpenText Method to reflect a new group of 256
columns. However, this requires me to create an array that has 256
dimensions... which alas is not allowed.

Any thoughts? I really don't want to have to import 256 columns at a
time... This wouldn't be too bad if it were one file... but there are
hundreds...

John

OpenText Method of Workbook Object Question
 

Hi Tom,

is the file fixed width (columns determined by position) or is it delimited
(if so, what is the delimiter).

The file is Tab Delimited

Are you asking how you can get 256 columns at a time so you want 1800/256 =
8 worksheets of data.

Exactly

If you want to figure it out on your own, you might look at this article:

http://support.microsoft.com/default...b;en-us;134826
"Out of Memory" Message Using the OpenText Method

I will check it out. Thanks Tom.
John

PS - a coworker stopped by my desk and this is what we came up with. It
seems to work okay.
----------------------------------------------------------------------
Sub ImportSections()

Const NUMBER_SECTIONS As Integer = 8

Dim column As Integer
Dim columnsToSkip As Integer
Dim columnDataType As XlColumnDataType

columnDataType = xlSkipColumn
fileName = "C:\Data\Converter\t1_100604_0930_APP2.rpt"
columnsToSkip = 256

For index = 1 To NUMBER_SECTIONS

ReDim HugeArray(columnsToSkip) As Variant

For column = 1 To columnsToSkip

HugeArray(column) = Array(column, columnDataType)

Next

Workbooks.OpenText fileName, DataType:=xlDelimited, Tab:=True,
fieldinfo:=HugeArray

columnsToSkip = columnsToSkip + 256
Next

End Sub