VBA SaveAs xlTextMSDos creates (sometimes) quote marks

Felix Atagong

Felix Atagong

Active Member
Joined
Jun 27, 2003
Messages
359
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. :cry:

Any ideas?
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
Sort by date Sort by votes
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!
 
Upvote 0
Yes, the comma is a delimiter, so the text is surrounded by quotes so that it is ignored when the text is parsed.
 
Upvote 0
Yes, the comma is a delimiter, so the text is surrounded by quotes so that it is ignored when the text is parsed.
Click to expand...
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.
 
Upvote 0
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 :-P
will that be translated as
Felix ""Chief"" Atagong
or as
"Felix ""Chief"" Atagong" ???

Any other signs I'LL have to take care off?
 
Upvote 0
You could write the file this way:

Code: 
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.
 
Upvote 0
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!
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question Question
Help with Macros
Replies
1
Views
554
t0ny84
T
S
  • Solved
Save as Text Adding a Quote Mark " to start of some lines - not sure why
Replies
5
Views
625
sheepdemon
S
D
  • Solved
Trying to Create Folder and Save to Folder based on cell values
Replies
8
Views
993
DeeMan
D
H
  • Question Question
Export each Sheet to .bat format without Double Quotes
Replies
3
Views
619
HongRu
HongRu
T
  • Question Question
VBA Help
2
Replies
19
Views
1K
Rodness
R

Forum statistics

Threads
1,224,603
Messages
6,179,850
Members
452,948
Latest member
UsmanAli786

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top