Do indiv. sheets (or cells) have "TextToColumns" properties?

[Conan Kelly]

Hello all,

(XL 2003 SP3 on Win XP Pro SP2)

Are there "TextToColumns" properties? If so, are these properties on
individual sheets? Are they on individual cells?

I have 5 SQL scripts run on MS SQL Server (only tested the first 3 so far)
and the results get pasted into XL. Among the results, there are 4 cells
that look something like this:

[Account Type Description] - <$20,000
[Account Type Description] - >=$20,000

When I pasted the 1st query's results into the first sheet in XL, the "... -
<$20,000" was all kept in one cell.

But pasting the 2nd & 3rd query's results into the 2nd & 3rd sheet in XL, XL
is recognizing the comma in "... - <$20,000" as a column delimiter for the
"Text to Columns" functionality of XL. So one cell gets "... - <$20", the
next cell to the right ends up being 0 (zero), and then all cells to the
right of that are offset by one cell.

Any ideas as to how I can get around this?

Thanks for any help anyone can provide,

Conan Kelly

 

[Bill Renaud]

"TextToColumns" is a method that applies to a Range object. I believe that
it is essentially the same as the "OpenText" method that works on a
Workbooks object. (I believe that they actually share common code inside
the VBA runtime.)

Both of these "remember" previously used settings for some of the
arguments, similar to the Find method. If you used either of these methods
between your database queries, then the behavior that you experienced will
result. Visual Basic Help does not mention this behavior (at least for
Excel 2000).

If fetching the query directly to a worksheet is not possible or desirable,
then you might save your query (export the data from your database) to a
text file, then use a macro to open the text file, like the following
generic code:

Workbooks.OpenText _
Filename:=strFileName, _
Origin:=xlWindows, _
StartRow:=1, _
DataType:=xlDelimited, _
TextQualifier:=xlTextQualifierNone, _
ConsecutiveDelimiter:=False, _
Tab:=False, _
Semicolon:=False, _
Comma:=False, _
Space:=False, _
Other:=False, _
FieldInfo:=Array(Array(1, xlTextFormat))

I would be sure to specify all of the parameters, to insure that it works
the way you want. Note that I have the arguments for Tab, Semicolon, Comma,
Space, and Other all set to False, to prevent any parsing of the data out
to other columns. Change these as appropriate for your data.

 

[Bill Renaud]

I forgot to mention that when you paste data to a worksheet directly from
the clipboard, Excel automatically applies the TextToColumns method to the
data (they apparently assume this is what you want to do next!). If you
used the Data|TextToColumns command while working with the data from
Query1, then this is what is causing your data to be parsed out against
your wishes.

If you don't want to have to write a macro to overcome this problem, or are
in a hurry to analyze some data, then simply call up the Data|TextToColumns
command on a blank worksheet, and reset all of the parameters back to
nothing before pasting in your 2nd query of data to a new worksheet.