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

User · Answer

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

User · Answer

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

User · Answer

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  amp       amp   XXXX   amp  VBA Format VBA Now   dd-MMM-yyyy hh-mm    amp    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

User · Answer

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      gt  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          gt  Create a Temp File     tmpFile   TempPath  amp  Format Now   ddmmyyyyhhmmss    amp    txt       ActiveWorkbook SaveAs Filename  tmpFile         FileFormat  xlText  CreateBackup  False          gt  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           gt  Get a free file handle     filesize   FreeFile            gt  Open your file     Open FlName For Output As  filesize      For i   LBound strData  To UBound strData          entireline   Replace strData i                         gt  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

User · Answer

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     amp  vbNewLine  amp  myFolder SaveFile   myFolder   End Function

User · Answer

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

User · Answer

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

User · Answer

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

[vba] Saving a Excel File into .txt format without quotes

Examples related to vba

Examples related to excel

Examples related to text