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 
The answer is
Try this code. This does what you want.
LOGIC
- Save the File as a TAB delimited File in the user temp directory
- Read the text file in 1 go
- 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
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
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
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
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:
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.
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.
I was using Write #1 "Print my Line" instead I tried Print #1, "Print my Line" and it give me all the data without default Quote(")
Dim strFile_Path As String
strFile_Path = ThisWorkbook.Path & "\" & "XXXX" & VBA.Format(VBA.Now, "dd-MMM-yyyy hh-mm") & ".txt"
Open strFile_Path For Output As #1
Dim selectedFeature As String
For counter = 7 To maxNumberOfColumn
selectedFeature = "X"
Print #1, selectedFeature
'Write #1, selectedFeature
Next counter
Close #1
Source: Stackoverflow.com