Using Workbooks.OpenText to read a tab-delimited file into a worksheet
 

Scott,

You need to use the textqualifier property

Workbooks.OpenText Filename:=lFile, DataType:=xlDelimited, _
TextQualifier:=xlTextQualifierNone, Tab:=True

Dan E

"Scott Lyon" <scott.lyonNOSPAM_at__NOSPAMrapistan.comNOSPAM wrote in message ...
I've got a VBA application (in Excel 2002) that uses the Workbooks.OpenText
function to read in a tab-delimited text file.


However, I've just noticed a problem. It seems that if any of the "cells"
either start and end with double quotes ("), or contain two sets of double
quotes together (""), but only if the cell starts/ends with double-quotes as
well, then the data is not importing properly. In the case of the start/end
double quotes, it simply drops them. In the case of the duplicate double
quotes, it removes one.


I need to have this represent the data exactly as is (and not drop the
"extra" double quotes). Is there a way to do that, without changing what I'm
doing too much?


The line I actually use to import is as follows (where lFile is a string
containing the path and filename of the text file to be read):

Workbooks.OpenText Filename:=lFile, DataType:=xlDelimited, Tab:=True


To better explain, if, in the tab-delimited text file, there is a "cell"
that contains the following text:
"The quick brown fox jumped over the lazy dog"

it will import as:

The quick brown fox jumped over the lazy dog


Also, if a "cell" contains:
"It was a ""dark"" night"

then it will import as:
It was a "dark" night



Is there a parameter I can set in the OpenText function, to keep from Excel
trying to be too smart (and reducing the double-quotes)?


Thanks!
-Scott