Phantom spaces/quotation marks when using SaveAs Text

[cogent]

Hello

I am using VBA to create a script in text format. Inexplicably, the SaveAs
xlText VBA commands add phantom quotation marks which cause the script to be
unusable.

Here is the conmmand as written:


I use

Newbook.Worksheets("Sheet1").Range("A1").Resize(UBound(TheArray, 1),
UBound(TheArray, 2)).Value = TheArray



to paste in the array.... Debug.Print shows no problems....

Here is the text as it appears in the array at all stages prior to being
saved:

echo on
textsave sort.out.txt
|
| ***************************************
| * PROGRAM SCRIPT *
| ***************************************
|
| Step 1 Deletes all the previous junk. Be careful.
local rm -Rf *.csv *.CSV *.out A B C D E F G
|---- Preamble -------------------------------------
|
| This covers any global parameters, or things we are not currently
| using.
|
version 1.24
time
debug off
auditing off


No problems.

I use

Newbook.SaveAs rFileLocation & "\" & SaveName & ".txt", FileFormat:=xlText

to save it...

BUT THIS IS HOW IT IS SAVED:

echo on
textsave sort.out.txt
|
| ***************************************
| * PROGRAM SCRIPT *
| ***************************************
|
| Step 1 Deletes all the previous junk. Be careful.
local rm -Rf *.csv *.CSV *.out A B C D E F G
|---- Preamble -------------------------------------
|
"| This covers any global parameters, or things we are not currently"""
| using.
|
version 1.24
time
debug off
auditing off


There are quotation marks that appear here as above and in other places in
the script.

It is enough to drive a man to madness. What gives???

W

 

[Steve Garman]

In your example, it's the comma that causes it.

Excel assumes you are going to reimport a text file into Excel later.

If it didn't put the quotes, the text after the comma would appear in a
second comma.

If you want to write pure text, your better off not trying to use saveas

You might like to look at the sample under "Print # Statement" in VBA help.

 

[Steve Garman]

If it didn't put the quotes, the text after the comma would appear in a
second comma.

Sorry, that should be "second column"

 

[cogent]

Steve, thank you for your comments. I really did remove everything which
could be perceived as a punctuation mark, but your solution gets to the
heart of the problem.

After many attempts I found that for reasons unknown (but I believe could
not be sound) Microsoft says that "When you
save a file in [text] format, if any of the cells in the spreadsheet...could
be misinterpreted when you reopen the file, Microsoft Excel encloses those
values in quotation marks." [Microsoft Knowledge Base Article Q104997]... it
offers no solutions.

I cleaned my data painstakingly, but this feature by Microsoft continued to
render spurious recognition of punctuation.

I discovered a "dirty" way around it:

Newbook.SaveAs rFileLocation & "\" & SaveName & ".txt",
FileFormat:=xlTextPrinter

whereas the FileFormat constant "xlTextPrinter" only saves to the file that
which can be specifically recognized as a printable character. Therefore
SaveAs ignores all spurious recognition of unprintable characters.

I think that roughly approximates your Print # VBA statement which I shall
also check out.

Thank you!