Hi all, I'm trying to read/write to a large embedded text file in an
Excel Worksheet. This file has too many columns for a regular sheet
but is used to source a window of data in another Excel sheet.

I understand that this embedded text file can be accessed using
OleObject with the Excel object model. However, I would like to know
how to read/write to this object using VBA, even if an additional COM
component for streams needs to be written.

Know the text could instead be saved in a Word document but this would
be too heavyweight. Also know a Textbox could be used but not sure
what the upper limitations are for this.

Any ideas appreciated!

Thanks

Rich

Instead of embedded the text file as an OLE object, can you import the text
file into column $A, without parsing it out to other columns? You could hide
this worksheet to prevent users from looking at it directly. In other words,
use the following code to import the data into only column $A, then write
routines to access it from there when needed. Notice that all of the
delimiters are set to False to prevent the OpenText method from parsing out
the data to other columns on each row.

I was able to import a large text file that had 100 lines of 4,000
characters on each line (over 400 columns, if parsed out), separated by Tab
characters. For Excel 2000, the specifications say that each cell can
contain a maximum of "32,767 characters. Only 1,024 display in a cell; all
32,767 display in the formula bar.".

'Open the file as a text file, import all data into column 1
'as text, with no conversion to numbers, etc.
Workbooks.OpenText _
Filename:=strPathFileName, _
Origin:=xlWindows, _
StartRow:=1, _
DataType:=xlDelimited, _
TextQualifier:=xlTextQualifierNone, _
ConsecutiveDelimiter:=False, _
Tab:=False, _
Semicolon:=False, _
Comma:=False, _
Space:=False, _
Other:=False, _
OtherChar:="", _
FieldInfo:=Array(1, xlTextFormat)

Just be sure that the data does not contain anything that resembles HTML
tags, since the OpenText method does attempt to interpret the data in some
cases (i.e. if it thinks that the data is HTML, but "MIME-version: 1.0" does
not exist at the beginning of a line near the top of the file). See my post
a while back about this issue. (Title was "OpenText method won't read text
files without "MIME-version" line in the header!" posted on 3/11/04).
http://groups.google.com/groups?as_u...2.texas.rr.com
--
Regards,
Bill


"Rich" wrote in message
om...
Hi all, I'm trying to read/write to a large embedded text file in an
Excel Worksheet. This file has too many columns for a regular sheet
but is used to source a window of data in another Excel sheet.

I understand that this embedded text file can be accessed using
OleObject with the Excel object model. However, I would like to know
how to read/write to this object using VBA, even if an additional COM
component for streams needs to be written.

Know the text could instead be saved in a Word document but this would
be too heavyweight. Also know a Textbox could be used but not sure
what the upper limitations are for this.

Any ideas appreciated!

Thanks

Rich