使用 VBA 将数据从 excel 选择性导出到文本文件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28534804/
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
selective export of data from excel to text file using VBA
提问by Manivannan KG
The Objective is to copy the specific rows from a worksheet to a text file.
目标是将特定行从工作表复制到文本文件。
Specific rows say (B6:D6 , B7:D7 and B14:D14, B21:D21).
特定行表示（B6:D6、B7:D7 和 B14:D14、B21:D21）。
回答by Manivannan KG
After few hours of effort this is the solution which might prove useful for many. The key is to use named range of the rows you require to copy.
经过几个小时的努力，这是一个可能对许多人有用的解决方案。关键是使用您需要复制的行的命名范围。
Private Sub btnExport_Click() Dim rng As Range Dim Sourceworksheet As Worksheet Dim DestFile As String Dim cel As Range 'Destination Path to place the text file. Application.DefaultFilePath = "\path\" DestFile = Application.DefaultFilePath & "\Test.txt" Open DestFile For Output As #1 Set Sourceworksheet = ActiveWorkbook.ActiveSheet ' select the data or rows you need to copy and make it named range. Set rng = ShAuReport.Range("DataAuReport") For Each cel In rng.Cells Write #1, cel.Address & "|" & cel.Value2 Next cel Close #1 MsgBox "txt file exported" End Sub
Private Sub btnExport_Click() Dim rng As Range Dim Sourceworksheet As Worksheet Dim DestFile As String Dim cel As Range 'Destination Path to place the text file. Application.DefaultFilePath = "\path\" DestFile = Application.DefaultFilePath & "\Test.txt" Open DestFile For Output As #1 Set Sourceworksheet = ActiveWorkbook.ActiveSheet ' select the data or rows you need to copy and make it named range. Set rng = ShAuReport.Range("DataAuReport") For Each cel In rng.Cells Write #1, cel.Address & "|" & cel.Value2 Next cel Close #1 MsgBox "txt file exported" End Sub
Regards,
问候，
Mani
摩尼
回答by KV Ramana
First add reference to "Microsoft Scripting Runtime" to use "FileSystemObject"(Tools -> References -> check the box for "Microsoft Scripting Runtime" and click ok)
首先添加对“Microsoft Scripting Runtime”的引用以使用“FileSystemObject”（工具-> 引用-> 选中“Microsoft Scripting Runtime”框并单击确定）
Sub WriteToTextFile()
Dim fs As New FileSystemObject
Dim rng As Range
Dim ws As Worksheet
Set ws = ActiveSheet
Set txtfile = fs.CreateTextFile(ThisWorkbook.Path & "\test.txt")
Set rng = Union(ws.Range("B6:D6"), ws.Range("B7:D7"), ws.Range("B14:D14"), ws.Range("B21:D21"))
For Each r In rng.Areas
For Each cel In r
txtfile.Write (cel.Value & " ")
Next
txtfile.Write (vbNewLine)
Next
End Sub
回答by Bill
Sub WriteToTextFile3()
'THIS VARIATION SEST RANGE BY COLUMN
Dim fs As New FileSystemObject
Dim rng As Range
Dim ws As Worksheet
Set ws = ActiveSheet
Set txtfile = fs.CreateTextFile(ThisWorkbook.Path & "\TestAAA.txt")
Set rng = Sheet1.Range("B2:B21")
For Each r In rng.Cells
For Each cel In r
txtfile.Write (cel.Value & " ")
txtfile.Write (cel.Offset(0, 1).Value & " ")
txtfile.Write (cel.Offset(0, 2).Value & " ") 'MORE COLUMNS? ADD OFFSET
Next cel
txtfile.Write (vbNewLine)
Next
End Sub
