Save as Text Adding a Quote Mark " to start of some lines - not sure why

[sheepdemon]

I have an excel workbook which I'm using to build some SQL. I've gotten it all working beautifully, but now when trying to Save As the sheet with the SQL code on it I am running into issues.

Heres the code I'm using, which works fine :

Sub Export_Imported_Sheet_As_CSV()
    Application.DisplayAlerts = False
    Dim Sht As Worksheet
    Dim Filename As String
    Worksheets("Enter Data Here").Activate
    Filename = Range("C6").Value
    Set Sht = Worksheets("SQL Script")
    Sht.Copy
    ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\" & Filename & ".sql", FileFormat:=20
    ActiveWorkbook.Close
    Application.DisplayAlerts = True
End Sub

This saves the file in "Windows Text" file format, I've tried Windows MS-Dos too. The file exports fine and is created, however some lines have quote marks at the start, others do not. I'm not sure what is causing some to have and others not. Example :

These lines, when exported to txt, are fine, and produced exactly as they look in the Excel Doc.

This next selection though - lines 3 and 4 have a " added to the start when exported to TXT, and I've no idea why.

Any ideas why this is occuring, and how to prevent it from occurring?

 

[RoryA]

It will be due to the presence of certain characters in those lines - eg commas or quote marks.

You could save it as formatted text (space delimited)?

 

[sheepdemon]

Do you know the FileFormat Value I would use for that? I don't see Formated Text as an option on the Excel File Format Enumeration page : XlFileFormat enumeration (Excel)

 

[sheepdemon]

I suppose I could rewrite the code to pop up a "Save As" prompt as well, but again I'd prefer that to have the correct filetype pre selected if possible, so same question comes up.

 

[RoryA]

It's xlTextPrinter. You'll probably need to rename the file afterwards as I suspect it will add a .prn extension to the name even if you supply an extension.

 

[sheepdemon]

That has worked perfect, and has not saved as a .prn. Thanks