如何将Excel工作表的特定列数据保存到.txt文件，包括每个单元格内容中的换行符 [英] How to save particular column data of an excel worksheet to .txt file including line breaks in each cell content

问题描述

我是VBA的新手.我有一个要求，我需要将Excel工作表的特定列中的数据(假设使用的C列数据的范围)保存到.txt文件.经过一些网页搜索后，我得到了VBA代码，但是问题是，如果一个单元格(例如Cell(1,3))的数据行多行，如下所示

单元格(1,3):

我有VBA，但是有问题.

我使用的VBA将cell(1,3)内容保存在一行中-'我得到了vba，但是我有一个问题需要帮助''我不想将它放在一行中./p>

我的要求很简单.

1. 我在工作表中有一个Activex按钮.

2. 当我单击按钮时，C列中的数据必须保存到.txt文件中，包括换行符.因此，如果cell(1,3)有4行，而cell(2,3)有2行，那么.txt文件应该有6行..txt文件应完全复制我的C列.

3.并且此文本文件必须保存到"C" \ vbacodes \"路径.

下面是我在互联网上找到的代码

  Private Sub CommandButton1_Click()'更新20130913Dim wb作为工作簿昏暗的saveFile作为字符串昏暗的工作范围关于错误继续xTitleId ="KutoolsforExcel"设置WorkRng = Application.Selection设置WorkRng = Application.InputBox("Range"，xTitleId，WorkRng.Address，类型:= 8)Application.ScreenUpdating = FalseApplication.DisplayAlerts = False设置wb = Application.Workbooks.Add工作副本wb.Worksheets(1).粘贴saveFile = Application.GetSaveAsFilename(fileFilter:=文本文件(* .txt)，*.txt")wb.SaveAs文件名:= saveFile，文件格式:= xlText，CreateBackup:= False关闭Application.CutCopyMode =假Application.DisplayAlerts = TrueApplication.ScreenUpdating =真结束子 

解决方案

首先更改路径和文件名以符合您的要求，然后将此宏分配给按钮:

  Sub TextFileMaker()昏暗的MyFilePath作为字符串，MyFileName作为字符串昏暗N一样长，nFirstRow一样长，nLastrow一样长MyFilePath ="C:\ TestFolder \"MyFileName =随便什么"设置fs = CreateObject("Scripting.FileSystemObject")设置一个= fs.CreateTextFile(MyFilePath& MyFileName&".txt"，True)nLastrow =单元格(Rows.Count，"C").End(xlUp).RownFirstRow = 1对于N = nFirstRow到nLastrowt = Replace(Cells(N，"C").Text，Chr(10)，vbCrLf)a.写线(t)下一个关闭结束子 

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

Cell(1,3):

I got the VBA but I have a issue.

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.

My requirement is very simple.

1. I have an activex button in the sheet.

2. 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.

3.And this text file must be saved to "C"\vbacodes\" path.

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

解决方案

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

这篇关于如何将Excel工作表的特定列数据保存到.txt文件，包括每个单元格内容中的换行符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！