Caution

For the remainder of your Excel session, Excel will remember the delimiter settings.There is an annoying bug (feature?) in Excel.After Excel remembers that you are using a comma or a tab as a delimiter,any time that you attempt to paste data from the clipboard to Excel, the data is parsed automatically by the delimiters specified in the OpenText method.Thus, if you attempted to paste some text that included the customer ABC, Inc., the text would be parsed automatically into two columns,with text up to ABC in one column and Inc. in the next column.

Excel has built-in options to read files where fields are delimited by tabs, semicolons, commas, or spaces. Excel can actually handle anything as a delimiter. If someone sends pipe-delimited text, you would set the Other parameter to True and specify an OtherChar parameter:

Workbooks.OpenText Filename:="C:\sales.txt", Origin:=437, _

DataType:=xlDelimited, Other:=True, OtherChar:="|", FieldInfo:=...

Reading Text Files with More Than 65,536 Rows

If you use the Text Import wizard to read a file with more than 65,536 rows of data, you will get an error saying File not loaded completely. The first 65,536 rows of the file will load correctly.

If you use Workbooks.OpenText to open a file with more than 65,536 rows of data, you are given no indication that the file did not load completely. Excel 2003 loads the first 65,536 rows and allows macro execution to continue. Your only real indication that there is a problem is if someone notices that the reports aren't reporting all the sales. If you think that your files will ever get this large, it would be good to check whether cell A65536 is nonblank after an import. If it is, the odds are that the entire file was not loaded.

Reading Text Files with Less Than 98,304 Rows

There is an easy built-in workaround. On the Text Import Wizard, there is a field where you can specify Start Import At Row. This is equivalent to using the StartRow parameter in the OpenText method. This would be a perfect solution. In theory, you could specify a StartRow of 65537 and continue reading the file. However, if you attempt to specify a StartRow higher than 32767, you get runtime error 1004. Microsoft wrote this import code back in the days when there were only 16,000+ rows in a worksheet. This field is assigned an integer datatype, which limits it to values between 1 and 32767. One would think that in the five versions released since they bumped up to 65,536 rows, someone would have remembered to change the datatype on this variable, but it hasn't happened.

I can think of some examples where you specifically know that you will have less than 98,304 rows. Imagine you are importing inventory records for a chain of retail stores. You know each store has roughly 2,000 rows and your company just opened stores 31 through 35. All of a sudden, the text file goes from 60,000 to 70,000. When that happens, you could code something like the following to completely load the file:

0 0

Post a comment