Saving a Excel File into .txt format without quote

Question

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

Answer 1

Ummm, How about this.

Copy your cells.
Open Notepad.
Paste.

Look no quotes, no inverted commas, and retains special characters, which is what the OP asked for. Its also delineated by carriage returns, same as the attached pict which the OP didn't mention as a bad thing (or a good thing).

Not really sure why a simple answer, that delivers the desired results, gets me a negative mark.

Answer 2

I have the same problem: I have to make a specific .txt file for bank payments out of an excel file. The .txt file must not be delimeted by any character, because the standard requires a certain number of commas after each mandatory field. The easiest way of doing it is to copy the contect of the excel file and paste it in notepad.

Answer 3

Try this code. This 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 (TRIED AND TESTED)

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

Answer 4

PRN solution works only for simple data in the cells, for me it cuts only first 6 signs from 200 characters cell.

These are the main file formats in Excel 2007-2016, Note: In Excel for the Mac the values are +1

51 = xlOpenXMLWorkbook (without macro's in 2007-2016, xlsx)
52 = xlOpenXMLWorkbookMacroEnabled (with or without macro's in 2007-2016, xlsm)
50 = xlExcel12 (Excel Binary Workbook in 2007-2016 with or without macro's, xlsb)
56 = xlExcel8 (97-2003 format in Excel 2007-2016, xls)

From XlFileFormat FileFormat Property

Keep in mind others FileFormatNumbers for SaveAs method:

FileExtStr = ".csv": FileFormatNum = 6
FileExtStr = ".txt": FileFormatNum = -4158
FileExtStr = ".prn": FileFormatNum = 36

Answer 5

The answer from this question provided the answer to this question much more simply.

Write is a special statement designed to generate machine-readable files that are later consumed with Input.

Use Print to avoid any fiddling with data.

Thank you user GSerg

Answer 6

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: