VBA SaveAs xlTextMSDos creates (sometimes) quote marks

[Felix Atagong]

I've got an annoying problem. A macro generates a flat-text file using the command

ActiveWorkbook.SaveAs Filename:= "Export.txt", FileFormat:=xlTextMSDOS

The generated text file has on some lines a starting and ending quote mark, and on others, with the same length and with exactly the same start and ending sequence, not.

For instance:
P000000xxxxx700characters00000 vs.
"P000000xxxxx700characters00000"

I can't save as xlTextPrinter as my strings are too big (715 characters) and that fileformat cuts the strings in smaller substrings. XLTextWindows also creates quote marks on some of the lines.

I really can't find a reason why these quote marks are generated, but they are always generated on the same line. So there is a consistency in the error.

Any ideas?

 

[Andrew Poulsom]

What exactly are your 700 characters?

 

[Felix Atagong]

I think (still got to test it) I found it. The lines with a quote mark around, have a comma (,) somewhere in the data, for instance in an address field.

So I think that
Brussels street 10
is transformed into
Brussels street 10 but
Brussels street, 10 into
"Brussels street, 10".

Added a few minutes later: YES THAT'S IT!

 

[Andrew Poulsom]

Yes, the comma is a delimiter, so the text is surrounded by quotes so that it is ignored when the text is parsed.

 

[Felix Atagong]

Yes, the comma is a delimiter, so the text is surrounded by quotes so that it is ignored when the text is parsed.

Well, I find that a rather useless 'option' in Excel (it isn't even an option as you can't apparently switch it off). If I want to save something as a flat text file, I want it as a flat text file with every line exactly the same length (715 characters) and not 717 or more because there seems to be a comma somewhere in between...

The result is that I now have to replace every comma with another sign (I took a point) which is rather messy, but it works.

 

[Felix Atagong]

Can I ask another question: what are the 'protected' characters then that will cause errors?

Comma (,) I know of now.
But quote mark as well, I suppose that will be surrounded by other quote marks (or not?): what if my original file has the following entry:
Felix "Chief" Atagong
will that be translated as
Felix ""Chief"" Atagong
or as
"Felix ""Chief"" Atagong" ???

Any other signs I'LL have to take care off?

 

[Andrew Poulsom]

You could write the file this way:

Sub Test()
'   Change file name to suit
    Const FileName As String = "C:\TEMP\MyFlatFile.txt"
    Dim FileNo As Integer
    Dim x As Long
    FileNo = FreeFile
    Open FileName For Output As #FileNo
'   Change sheet reference to suit
    With Worksheets("Sheet1")
        For x = 1 To .Range("A1:A" & .Range("A65536").End(xlUp).Row).Count
            Print #FileNo, .Cells(x, 1).Value
        Next x
    End With
    Close #FileNo
End Sub

That will avoid Excel putting in any extra characters.

 

[Felix Atagong]

Thanks, very nice code (and it works, but I'll bet you knew that).
Have to change my code that it attaches the new Flattextfile to an email, but that is no problem.
Thanks again!