vba 如何将 Excel 工作表的特定列数据保存到 .txt 文件，包括每个单元格内容中的换行符
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27729408/
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 particular column data of an excel worksheet to .txt file including line breaks in each cell content
提问by Rajnavakoti
I am pretty new to VBA. I have a requirement where I need to save the data in a particular column of an Excel worksheet (let's say used range of C column data) to a .txt file. After some web searching I got the VBA code, but the issue is, if a cell (say Cell(1,3)) is having data in multiple lines as shown below
我对 VBA 很陌生。我有一个要求，我需要将 Excel 工作表的特定列中的数据（假设使用的 C 列数据范围）保存到 .txt 文件。经过一些网络搜索后，我得到了 VBA 代码，但问题是，如果一个单元格（比如 Cell(1,3)）在多行中包含数据，如下所示
Cell(1,3):
单元格（1,3）：
I got the VBA but I have a issue.
我得到了 VBA，但我有一个问题。
The VBA my using is saving the cell(1,3) content in a single line -'i got the vba but I have a issue help me out' I don't want it to be in a single line.
我使用的 VBA 将单元格 (1,3) 内容保存在一行中 -'我得到了 vba，但我有一个问题帮助我'我不希望它在一行中。
My requirement is very simple.
我的要求很简单。
I have an activex button in the sheet.
when I click the button, data in C column must be saved to .txt file including line breaks. so if cell(1,3) has 4 lines and cell(2,3) has 2 lines then .txt file should have 6 lines. .txt file should exactly replicate my C column.
我在工作表中有一个 activex 按钮。
当我单击按钮时，C 列中的数据必须保存到 .txt 文件中，包括换行符。所以如果 cell(1,3) 有 4 行，cell(2,3) 有 2 行，那么 .txt 文件应该有 6 行。.txt 文件应该完全复制我的 C 列。
3.And this text file must be saved to "C"\vbacodes\" path.
3.并且这个文本文件必须保存到“C”\vbacodes\“路径。
Below is this code I found on internet
下面是我在网上找到的这段代码
Private Sub CommandButton1_Click()
'Update 20130913Dim wb As Workbook
Dim saveFile As String
Dim WorkRng As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set wb = Application.Workbooks.Add
WorkRng.Copy
wb.Worksheets(1).Paste
saveFile = Application.GetSaveAsFilename(fileFilter:="Text Files (*.txt), *.txt")
wb.SaveAs Filename:=saveFile, FileFormat:=xlText, CreateBackup:=False
wb.Close
Application.CutCopyMode = False
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
回答by Gary's Student
First change the path and filename to match your requirements and then assign this macro to a button:
首先更改路径和文件名以符合您的要求，然后将此宏分配给按钮：
Sub TextFileMaker()
Dim MyFilePath As String, MyFileName As String
Dim N As Long, nFirstRow As Long, nLastrow As Long
MyFilePath = "C:\TestFolder\"
MyFileName = "whatever"
Set fs = CreateObject("Scripting.FileSystemObject")
Set a = fs.CreateTextFile(MyFilePath & MyFileName & ".txt", True)
nLastrow = Cells(Rows.Count, "C").End(xlUp).Row
nFirstRow = 1
For N = nFirstRow To nLastrow
t = Replace(Cells(N, "C").Text, Chr(10), vbCrLf)
a.WriteLine (t)
Next
a.Close
End Sub
