Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Runtime error 1004 with method OpenText - but only on one computer | Excel Programming | |||
How to use Opentext method with xlFixedWidth? | Excel Programming | |||
method vbproject of object workbook fail | Excel Programming | |||
Pass string as Parameter in OpenText method | Excel Programming | |||
Can we Pass String to FieldInfo Array to OpenText Method. | Excel Programming |