Saving a Excel File into .txt format without quotes

I have a excel sheet which has data in column A.There are many special characters in the cells.When I save the sheet in .txt format I get inverted commas at the start of each line. I tried both manually and by macro saving the file in .txt format.Why is it so? How to remove them? I am not able to remove the quotes. Attaching a pic

Solution 1:

I see this question is already answered, but wanted to offer an alternative in case someone else finds this later.

Depending on the required delimiter, it is possible to do this without writing any code. The original question does not give details on the desired output type but here is an alternative:

PRN File Type

The easiest option is to save the file as a "Formatted Text (Space Delimited)" type. The VBA code line would look similar to this:

ActiveWorkbook.SaveAs FileName:=myFileName, FileFormat:=xlTextPrinter, CreateBackup:=False

In Excel 2007, this will annoyingly put a .prn file extension on the end of the filename, but it can be changed to .txt by renaming manually.

In Excel 2010, you can specify any file extension you want in the Save As dialog.

One important thing to note: the number of delimiters used in the text file is related to the width of the Excel column.

Observe:

Becomes:

Solution 2:

This code does what you want.

LOGIC

1. Save the File as a TAB delimited File in the user temp directory
2. Read the text file in 1 go
3. Replace "" with blanks and write to the new file at the same time.

CODE

Private Declare Function GetTempPath Lib "kernel32" Alias "GetTempPathA" _
(ByVal nBufferLength As Long, ByVal lpBuffer As String) As Long

Private Const MAX_PATH As Long = 260

'~~> Change this where and how you want to save the file
Const FlName = "C:\Users\Siddharth Rout\Desktop\MyWorkbook.txt"

Sub Sample()
    Dim tmpFile As String
    Dim MyData As String, strData() As String
    Dim entireline As String
    Dim filesize As Integer
    
    '~~> Create a Temp File
    tmpFile = TempPath & Format(Now, "ddmmyyyyhhmmss") & ".txt"
    
    ActiveWorkbook.SaveAs Filename:=tmpFile _
    , FileFormat:=xlText, CreateBackup:=False
    
    '~~> Read the entire file in 1 Go!
    Open tmpFile For Binary As #1
    MyData = Space$(LOF(1))
    Get #1, , MyData
    Close #1
    strData() = Split(MyData, vbCrLf)
    
    '~~> Get a free file handle
    filesize = FreeFile()
  
    '~~> Open your file
    Open FlName For Output As #filesize
    
    For i = LBound(strData) To UBound(strData)
        entireline = Replace(strData(i), """", "")
        '~~> Export Text
        Print #filesize, entireline
    Next i
    
    Close #filesize
    
    MsgBox "Done"
End Sub

Function TempPath() As String
    TempPath = String$(MAX_PATH, Chr$(0))
    GetTempPath MAX_PATH, TempPath
    TempPath = Replace(TempPath, Chr$(0), "")
End Function

SNAPSHOTS

Actual Workbook

After Saving