vba 将 Excel 文件保存为不带引号的 .txt 格式
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11501531/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Saving a Excel File into .txt format without quotes
提问by ankit agrawal
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 removethe quotes.Attaching a pic 
我有一个 Excel 工作表，其中 A 列中有数据。单元格中有许多特殊字符。当我以 .txt 格式保存工作表时，每行的开头都会出现引号。我尝试手动和通过宏将文件保存为 .txt 格式。为什么会这样？如何删除它们？我无法删除的报价。附上一张照片
采纳答案by Siddharth Rout
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.
- 将文件另存为用户临时目录中的 TAB 分隔文件
- 一键读取文本文件
""用空格替换并同时写入新文件。
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
保存后
回答by nicholas
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
PRN 文件类型
The easiest option is to save the file as a "Formatted Text (Space Delimited)" type. The VBA code line would look similar to this:
最简单的选择是将文件另存为“格式化文本（空格分隔）”类型。VBA 代码行类似于：
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.
在 Excel 2007 中，这会令人讨厌地在文件名末尾放置一个 .prn 文件扩展名，但可以通过手动重命名将其更改为 .txt。
In Excel 2010, you can specify any file extension you want in the Save As dialog.
在 Excel 2010 中，您可以在“另存为”对话框中指定所需的任何文件扩展名。
One important thing to note: the number of delimiters used in the text file is related to the width of the Excel column.
需要注意的一件重要事情：文本文件中使用的分隔符数量与 Excel 列的宽度有关。
Observe:
观察：
Becomes:
变成：
回答by Eric Gross
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).
看没有引号，没有引号，并保留特殊字符，这是 OP 所要求的。它也由回车符划定，与 OP 未提及为坏事（或好事）的附加图片相同。
Not really sure why a simple answer, that delivers the desired results, gets me a negative mark.
不太确定为什么一个简单的答案，提供了预期的结果，会给我一个负面的评价。
回答by asdf30
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.
第二个是“ActiveWorkbook.SaveAs”或“ActiveWorksheet.SaveAs”，它们都具有更改活动工作簿文件名的非常糟糕的副作用。
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.
这里的解决方案是我在网上找到的几种解决方案的混合体。它基本上是这样做的：1) 将选定的单元格复制到新工作表 2) 一次遍历每个单元格并将其“打印”到打开的文件 3) 删除临时工作表。
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
结束函数
回答by Vali Ploiesti
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.
我有同样的问题：我必须从 excel 文件中为银行付款制作一个特定的 .txt 文件。.txt 文件不能被任何字符分隔，因为标准要求每个必填字段后有一定数量的逗号。最简单的方法是复制excel文件的内容并将其粘贴到记事本中。
回答by Krzysztof Gapski
PRN solution works only for simple data in the cells, for me it cuts only first 6 signs from 200 characters cell.
PRN 解决方案仅适用于单元格中的简单数据，对我而言，它仅从 200 个字符的单元格中剪切前 6 个符号。
These are the main file formats in Excel 2007-2016, Note: In Excel for the Mac the values are +1
这些是 Excel 2007-2016 中的主要文件格式，注意：在 Excel for Mac 中，值为 +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:
请记住其他 FileFormatNumbers for SaveAs 方法：
FileExtStr = ".csv": FileFormatNum = 6
FileExtStr = ".txt": FileFormatNum = -4158
FileExtStr = ".prn": FileFormatNum = 36
回答by timclancy
The answerfrom this questionprovided 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.
Write 是一个特殊的语句，旨在生成机器可读的文件，这些文件稍后会与 Input 一起使用。
使用打印来避免对数据的任何摆弄。
Thank you user GSerg
感谢用户 GSerg
回答by Yain Patel
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(")
我正在使用 Write #1 “Print my Line” 而不是我尝试 Print #1, “Print my Line” 它给了我没有默认 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
