Excel VBA 另存为制表符分隔的文件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/39766963/
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
Excel VBA Save as tab delimited file
提问by Heresh
I have some code that saves the active worksheet as a tab delimited text file, however the cell data that has a comma character in them gets exported with quotation marks, like
我有一些代码将活动工作表保存为制表符分隔的文本文件，但是其中包含逗号字符的单元格数据使用引号导出，例如
John Hopkins, Burgers
becomes
"John Hopkins, Burgers"
How can I work around this?
我该如何解决这个问题？
This is my code:
这是我的代码：
ActiveWorkbook.SaveAs ActiveWorkbook.path & "\" & filename,
FileFormat:=xlText, CreateBackup:=False
Adding this: I just found out that if I save the file again it removes all "". Can I add an additional save to the code?
添加这个：我刚刚发现，如果我再次保存文件，它会删除所有“”。我可以在代码中添加额外的保存吗？
采纳答案by Heresh
I solved it! Since the quotes disappear when you save it manually as a delimited file but not when you use
我解决了！由于当您手动将其保存为分隔文件时引号会消失，但在您使用时不会
ActiveWorkbook.SaveAs ActiveWorkbook.path & "\" & filename,
FileFormat:=xlText, CreateBackup:=False
I just added another save after the above one, but this time using the sendkey strokes to mimick the manual method.
我只是在上面的一个之后添加了另一个保存，但这次使用sendkey笔画来模仿手动方法。
ActiveWorkbook.SaveAs fil, FileFormat:= _ xlText
ActiveWorkbook.SaveAs fil, FileFormat:= _ xlText
SendKeys "^{F4}"
SendKeys "{ENTER}"
SendKeys "{ENTER}"
回答by DeLaguna
This is a variation Of some code I used to save a normal xlsb file or xls* file could be used for many different files though.
这是我用来保存普通 xlsb 文件或 xls* 文件的一些代码的变体，但可以用于许多不同的文件。
Sub SaveAsTDV()
'
' Easy way to export to Tab Deliminated Values
' By DeLaguna
' v17.05.10.15.06
'
Dim BaseFolder As String
Dim LitProg As String
Dim Path As String
Dim Extn As String
BaseFolder = "YourBase Folder\"
FileName = "FileName" 'File name with no Extension.
Extn = ".txt" 'If choosing somthing other than xlText you may change this.
Application.DisplayAlerts = False 'Auto Yes to save
ChDir BaseFolder
ActiveWorkbook.SaveAs Filename:= BaseFolder & FileName & Extn, _
FileFormat:=xlText, CreateBackup:=True 'xlText will save as Tab Deliminated text
Application.DisplayAlerts = True ' Reverts to Excel Default showing popup messages for input.
End Sub
Hopefully it helps someone let me know if I made any errors I had to redact sensitive information out of it.
希望它可以帮助有人让我知道我是否犯了任何错误，我必须从中删除敏感信息。
回答by Aditya Pansare
According to FileFormat Property, you have the following formats to choose from:
根据 FileFormat 属性，您可以选择以下格式：
xlCSV
xlCSVMac
xlCSVMSDOS
xlCSVWindows
Why don't you choose one of the comma- separated value formats?
为什么不选择一种逗号分隔值格式？
Try xlCSV.
试试xlCSV。
回答by Alfonso Gil
Change FileFormat xlText to xlTextPrinter
将 FileFormat xlText 更改为 xlTextPrinter
ActiveWorkbook.SaveAs ActiveWorkbook.path & "\" & filename,
FileFormat:=xlTextPrinter, CreateBackup:=False
