Excel VBA Save as tab delimited file

Question

I have some code that saves the active worksheet as a tab delimited text file, however the cell data that has a comma character in them gets exported with quotation marks, like

John Hopkins, Burgers
becomes
"John Hopkins, Burgers"

How can I work around this?

This is my code:

ActiveWorkbook.SaveAs ActiveWorkbook.path & "\" & filename,
FileFormat:=xlText, CreateBackup:=False

Adding this: I just found out that if I save the file again it removes all "". Can I add an additional save to the code?

Check this out: http://stackoverflow.com/questions/11501531/saving-a-excel-file-into-txt-format-without-quotes — Vityata, Sep 29 '16 at 10:11

score 3 · Accepted Answer · answered Sep 30 '16 at 06:53

3

I solved it! Since the quotes disappear when you save it manually as a delimited file but not when you use

ActiveWorkbook.SaveAs ActiveWorkbook.path & "\" & filename,
FileFormat:=xlText, CreateBackup:=False

I just added another save after the above one, but this time using the sendkey strokes to mimick the manual method.

ActiveWorkbook.SaveAs fil, FileFormat:= _ xlText

SendKeys "^{F4}"
SendKeys "{ENTER}"
SendKeys "{ENTER}"

answered Sep 30 '16 at 06:53

Heresh

305
1
4
18

If it's important for anyone to not lose formatting or other features of the original workbook, you could also use VBA to create a new workbook, copy over the sheet in question, then export that workbook and close it without saving - thereby giving you the exported text file while leaving your original workbook intact. I do this when I have a workbook with macros and multiple sheets used to populate the CSV sheet that I want. I export that sheet, but leave my working file as is. – nollaf126 May 11 '22 at 20:43

score 1 · Answer 2 · edited Sep 29 '16 at 10:21

1

According to FileFormat Property, you have the following formats to choose from:

xlCSV
xlCSVMac
xlCSVMSDOS
xlCSVWindows

Why don't you choose one of the comma- separated value formats?

Try xlCSV.

edited Sep 29 '16 at 10:21

Neeraj Kumar

771
2
16
37

answered Sep 29 '16 at 09:55

Aditya Pansare

1,112
9
14

I tried all of them, none produces the result I want. – Heresh Sep 29 '16 at 10:00
Is it possible to read in that text file and do a simple search and replace to remove all ", marks? – Heresh Sep 29 '16 at 10:00
it still produces "" – Heresh Sep 29 '16 at 10:50
please share file with sample data and the code you have written? – Aditya Pansare Sep 29 '16 at 11:06
Yes, we could replace double quotes("). – Aditya Pansare Sep 29 '16 at 11:23

score 0 · Answer 3 · answered May 10 '17 at 19:13

This is a variation Of some code I used to save a normal xlsb file or xls* file could be used for many different files though.

Sub SaveAsTDV()

'
' Easy way to export to Tab Deliminated Values
' By DeLaguna
' v17.05.10.15.06
'

Dim BaseFolder As String
    Dim LitProg As String
    Dim Path As String
    Dim Extn As String
      BaseFolder = "YourBase Folder\"    
      FileName = "FileName" 'File name with no Extension.
      Extn = ".txt" 'If choosing somthing other than xlText you may change this.

 Application.DisplayAlerts = False 'Auto Yes to save

    ChDir BaseFolder
        ActiveWorkbook.SaveAs Filename:= BaseFolder & FileName & Extn, _
        FileFormat:=xlText, CreateBackup:=True  'xlText will save as Tab Deliminated text

Application.DisplayAlerts = True ' Reverts to Excel Default showing popup messages for input.

End Sub

Hopefully it helps someone let me know if I made any errors I had to redact sensitive information out of it.

score 0 · Answer 4 · answered Feb 05 '18 at 09:48

0

Change FileFormat xlText to xlTextPrinter

 ActiveWorkbook.SaveAs ActiveWorkbook.path & "\" & filename,
    FileFormat:=xlTextPrinter, CreateBackup:=False

answered Feb 05 '18 at 09:48

Alfonso Gil

1

Makes a real mess ignoring all but the strings in formula. – flywire May 23 '18 at 05:19

score 0 · Answer 5 · answered Jan 22 '21 at 11:10

I was also trying to get this working, and found that xlText isn't a valid FileFormat but xlTextWindows is. This might be version related, but just in case anybody else has the same issue.

ActiveWorkbook.SaveAs ActiveWorkbook.path & "\" & filename,FileFormat:=xlTextWindows, reateBackup:=False

A full list of valid entries for FileFormat is held here

https://learn.microsoft.com/en-us/office/vba/api/excel.xlfileformat

Excel VBA Save as tab delimited file

5 Answers5