Trying to get/set contents of embedded file using Excel OleObject
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 |
Trying to get/set contents of embedded file using Excel OleObject
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 |
All times are GMT +1. The time now is 06:22 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com