vba 如何在vba中另存为.txt
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26151021/
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
How to save as .txt in vba
提问by Tony Bergeson
I am looking to have my Macro save a new sheet that i created as a .txt file. this is the code i have so far.
我希望我的宏将我创建的新工作表保存为 .txt 文件。这是我到目前为止的代码。
Sub Move()
'
' Move Macro
'
' Keyboard Shortcut: Ctrl+m
'
Sheets("Sheet1").Select
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Workbooks.Add
ActiveSheet.Paste
ActiveWorkbook.SaveAs Filename:="e:" & _
"HDR" + Format(Now(), "YYYYMMDDhhmmss") & ".txt"
End Sub
That includes my macro. I am having trouble with the last part where it saves as a .txt file.
I am currently getting a bunch of crap on my .txt file, here is an example,
"PK ! !}?U{ ? [Content_Types].xml ￠(? ìTYN?0?7?–T?-‰1?á…a￥'?Pú3?DμMOá?????¨".
Any help would be great.
这包括我的宏。我在保存为 .txt 文件的最后一部分时遇到了问题。
我目前在我的 .txt 文件中收到一堆废话，这是一个例子，
“PK ! !}?U{ ? [Content_Types].xml ￠(?ìTYN?0?7?–T?-‰1?á …a￥'?Pú3?DμMOá?????¨”。
任何帮助都会很棒。
回答by Degustaf
Manually changing the extension of the file name does not actually change the file type. The SaveAsmethod takes a file type argument. The code you want is
手动更改文件名的扩展名实际上不会更改文件类型。该SaveAs方法采用文件类型参数。你想要的代码是
ActiveWorkbook.SaveAs Filename:="e:" & "HDR" + Format(Now(), "YYYYMMDDhhmmss") _
& ".txt", FileFormat:= xlTextWindows
Doing a search from within Excel help for XlFileFormatwill get you (almost) the full list of possible file formats, including 6 text formats, and 4 CSV formats.
在 Excel 帮助中进行搜索XlFileFormat将为您提供（几乎）可能的文件格式的完整列表，包括 6 种文本格式和 4 种 CSV 格式。
回答by AJY
Adding txt to the name does not automatically encode the word document into plain text format.
在名称中添加 txt 不会自动将 word 文档编码为纯文本格式。
Instead attempt
而是尝试
ActiveWorkbook.SaveAs Filename:="e:" & _
"HDR" + Format(Now(), "YYYYMMDDhhmmss") & ".txt", FileFormat:=wdFormatText, Encoding:=1252
回答by cyberponk
The ActiveWorkbook.SaveAs method adds double quote to the beginning and end of every line in the file.
ActiveWorkbook.SaveAs 方法在文件中每一行的开头和结尾添加双引号。
This method parses each line from a given range and transforms it into a CSV file:
此方法解析给定范围内的每一行并将其转换为 CSV 文件：
Sub SaveSheetToCSVorTXT()
Dim xFileName As Variant
Dim rng As Range
Dim DelimChar As String
DelimChar = "," 'The delimitation character to be used in the saved file. This will be used to separate cells in the same row
xFileName = Application.GetSaveAsFilename(ActiveSheet.Name, "CSV File (*.csv), *.csv, Text File (*.txt), *.txt")
If xFileName = False Then Exit Sub
If Dir(xFileName) <> "" Then
If MsgBox("File '" & xFileName & "' already existe. Overwrite?", vbYesNo + vbExclamation) <> vbYes Then Exit Sub
Kill xFileName
End If
Open xFileName For Output As #1
'Save range contents. Examples of ranges:
'Set rng = Activesheet.Range("A1:D4") 'A rectangle between 2 cells
'Set rng = Activesheet.columns(1) 'An entire column
Set rng = ActiveSheet.Range("B14").CurrentRegion 'The "region" from a cell. This is the same as pressing CTRL+T on the selected cell
For i = 1 To rng.Rows.Count
For j = 1 To rng.Columns.Count
lineText = IIf(j = 1, "", lineText & DelimChar) & rng.Cells(i, j)
Next j
Print #1, lineText
Next i
Close #1
MsgBox "File saved!"
End Sub
