Saving a Excel File into .txt format without quotes

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

Solution 1

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

Solution 2

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 3

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.

Solution 4

I just spent the better part of an afternoon on this

There are two common ways of writing to a file, the first being a direct file access "write" statement. This adds the quotes.

The second is the "ActiveWorkbook.SaveAs" or "ActiveWorksheet.SaveAs" which both have the really bad side effect of changing the filename of the active workbook.

The solution here is a hybrid of a few solutions I found online. It basically does this: 1) Copy selected cells to a new worksheet 2) Iterate through each cell one at a time and "print" it to the open file 3) Delete the temporary worksheet.

The function works on the selected cells and takes in a string for a filename or prompts for a filename.

Function SaveFile(myFolder As String) As String
tempSheetName = "fileWrite_temp"
SaveFile = "False"

Dim FilePath As String
Dim CellData As String
Dim LastCol As Long
Dim LastRow As Long

Set myRange = Selection
'myRange.Select
Selection.Copy

'Ask user for folder to save text file to.
If myFolder = "prompt" Then
    myFolder = Application.GetSaveAsFilename(fileFilter:="XML Files (*.xml), *.xml, All Files (*), *")
End If
If myFolder = "False" Then
    End
End If

Open myFolder For Output As #2

'This temporarily adds a sheet named "Test."
Sheets.Add.Name = tempSheetName
Sheets(tempSheetName).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

LastCol = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Column
LastRow = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row

For i = 1 To LastRow
    For j = 1 To LastCol
        CellData = CellData + Trim(ActiveCell(i, j).Value) + "   "
    Next j

    Print #2, CellData; " "
    CellData = ""

Next i

Close #2

'Remove temporary sheet.
Application.ScreenUpdating = False
Application.DisplayAlerts = False
ActiveWindow.SelectedSheets.Delete
Application.DisplayAlerts = True
Application.ScreenUpdating = True
'Indicate save action.
MsgBox "Text File Saved to: " & vbNewLine & myFolder
SaveFile = myFolder

End Function

Solution 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

View more solutions

Comments

[nicholas]

@ankitagrawal: please see additional answer, below. if spaces are adequate delimiters, you can get by with built-in functionality of Excel

[ekkis]

it would be better to suggest a non-manual solution. manual solutions are costly and not really solutions

[ekkis]

the above code leaves the temporary file behind, which could be cleaned up with something like this With New FileSystemObject If .FileExists(yourFilePath) Then .DeleteFile yourFilepath End If End With

[Siddharth Rout]

You can also delete that file using Kill tmpFile with OERN @ekkis

[Siddharth Rout]

@ekkis: OERN: On Error Resume Next :)

[shazbot]

This doesn't work for me. It's still quoted in Notepad.

[mooseman]

One question about this code. Notice the two blank lines at the end of your text file? One is from the Upper and lower bound difference (0 to number of rows in Excel used, instead of 1 to number of rows in Excel used). The other is most likely that when you write to the new file the line ends with a vbCrLf. How can I modify your code to remove the last vbCrLf? I changed to this to remove one of the blank lines (For i = LBound(strData) To UBound(strData) -1)

[Siddharth Rout]

@mooseman: See This there are other examples as well. Just type vb6 remove last blank line from text file in Google

[Jason R. Escamilla]

Thanks for this. The column width had thrown me off the scent when I was trying to figure it out on my own, but your thorough notes allowed me to salvage the effort!

[Jason R. Escamilla]

In Excel 2010, saving to .html, I was limited to approx 255 characters per line before getting unpredictable results.

[Nikos Tsokos]

I do not get why this answer gets negative votes. It is a simple solution that fits my needs.

[Jim]

This is no more manual than saving an XLS or XLSX file as a .txt file from Excel. It's a simple column selection, copy, and paste to a new .txt file in Notepad. Takes a lot less time than the so-called automated solutions. This is how we create them when we need them occasionally for our ADP upload interface.It's dirt simple and it works. It's also the accepted solution in other forums, such as here: superuser.com/questions/206060/…

[Brandon Spilove]

I upvoted it, this answer was super simple and worked fine.

[TadLewis]

Works BUT if your cells span wider than the width of a printout they'll get moved to the bottom of the page and disrupt your data.

[gatinueta]

It doesn't deliver the desired results - I still get quotes in notepad.

[Blaisem]

Thanks. Print instead of write works. No need for all the other longer codes here.