vba Excel VBA导出到没有引号的txt文件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24575543/
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 Export to txt file without Quotation marks
提问by Windmill
I'm needing to export to a text file without " marks example
我需要导出到一个没有 " 标记的文本文件示例
exporting this
出口这个
Create bts; sitemask = "0110"; pcmlink = 40
创建 bts；站点掩码 = "0110"; pcmlink = 40
exports like this
像这样出口
"Create bts; sitemask = ""0110""; pcmlink = 40"
"创建 bts; 站点掩码 = ""0110""; pcmlink = 40"
This code I found for doing this works and strips off the " marks
我找到的用于执行此操作的代码并去除了 " 标记
Sub Export()
Dim r As Range, c As Range
Dim sTemp As String
Open "c:\MyOutput.txt" For Output As #1
For Each r In Selection.Rows
sTemp = ""
For Each c In r.Cells
sTemp = sTemp & c.Text & Chr(9)
Next c
'Get rid of trailing tabs
While Right(sTemp, 1) = Chr(9)
sTemp = Left(sTemp, Len(sTemp) - 1)
Wend
Print #1, sTemp
Next r
Close #1
End Sub
This code works from selecting the cells running the code which exports the current selected cells, my question is this how can I modify this code to work on a predefined cell range eg A1 to A10 for exporting rather than the current selection?
这段代码的工作原理是选择运行导出当前选定单元格的代码的单元格，我的问题是如何修改此代码以在预定义的单元格范围内工作，例如 A1 到 A10 用于导出而不是当前选择？
Also is there away to modify the file path to save in the same directory as the active excel sheet instead of having to put the full file path"C:\MyOutput.txt" instead "MyOutput.txt" (or something like that)?
是否还可以修改文件路径以保存在与活动 Excel 表相同的目录中，而不必将完整的文件路径“C:\MyOutput.txt”而不是“MyOutput.txt”（或类似的东西）？
Many thanks
非常感谢
回答by Othya
Don't open it directly:
不要直接打开：
Sub Export()
Dim r As Range, c As Range
Dim sTemp As String
Dim fn As Long
Dim sfilename
sfilename = "C:\MyOutput.txt"
fn = FreeFile
Open sfilename For Output As #fn
For Each r In Selection.Rows
sTemp = ""
For Each c In r.Cells
sTemp = sTemp & c.Text & Chr(9)
Next c
'Get rid of trailing tabs
While Right(sTemp, 1) = Chr(9)
sTemp = Left(sTemp, Len(sTemp) - 1)
Wend
Print #fn, sTemp
Next r
Close #fn
End Sub
回答by Othya
The modification would be pretty obvious: assuming that your workbook is named "ThisWorbook.xls", and the worksheet that holds the range you want to write to file is named "This Worksheet", then you'll adapt the code like
修改将非常明显：假设您的工作簿名为“ThisWorbook.xls”，并且包含您要写入文件的范围的工作表名为“This Worksheet”，那么您将调整代码，例如
' ... Previous original code
' Open file in the same folder as the worksheet
Open Workbooks("ThisWorbook.xls").Path & "\MyOutput.txt" For Output As #1
' Loop in predefined range instead of current selection
For Each r In Worksheets("This Worksheet").Range("A1:A10").Rows
' ... Following original code
回答by Administrator
You have an excel file, and when You save the file as .txt the Quotation marks appear. Mark the data ( CTRL + A), copy the data to clipboard ( CTRL + C). Open notepad, paste the data ( CTRL + V) save the data... There, data without Quotation marks.
您有一个 excel 文件，当您将文件另存为 .txt 时，会出现引号。标记数据（CTRL + A），将数据复制到剪贴板（CTRL + C）。打开记事本，粘贴数据（CTRL+V）保存数据...那里，没有引号的数据。
回答by Andrew Slentz
From what I've seen in VBA 2010, the Write function creates output with the unwanted wrapping quotation characters.
从我在 VBA 2010 中看到的情况来看，Write 函数使用不需要的环绕引号字符创建输出。
However the similar Print function writes to the file without the quotes.
然而，类似的 Print 函数会在没有引号的情况下写入文件。
But if you wanted quotes in the text file, you could create a string concatenation at the quote locations to add them in with Chr(39), eg.
但是如果你想在文本文件中引用引号，你可以在引号位置创建一个字符串连接，以将它们添加到 Chr(39) 中，例如。
For an output of: Create bts; sitemask = "0110"; pcmlink = 40
对于输出：创建 bts; 站点掩码 = "0110"; pcmlink = 40
Code as:
代码为：
"Create bts; sitemask = " & Chr(39) & "0110" & Chr(39) & "; pcmlink = 40"
回答by user3079695
Save your file as ASCII file not unicode
将您的文件保存为 ASCII 文件而不是 unicode
fso.CreateTextFile(path, True, False)
fso.CreateTextFile(path, True, False)
