跳到主要内容

如何将Excel数据(选择或工作表)导出到Excel中的文本文件?

如果需要以文本文件格式交付工作簿,则需要将工作簿转换为文本文件。 本文将介绍两种轻松将Excel数据导出到文本文件的方法。

将一张工作表导出到Excel中的文本文件

默认情况下,Excel数据将以以下格式保存为工作簿: .XLSX。 但是,我们可以将现有工作簿的工作表导出为文本文件, 另存为 特征。 请执行以下操作:

1. 转到要导出到文本文件的工作表,然后单击 文件 (或 办公按钮)> 另存为.

2. 在开幕 另存为 对话框中,选择要将导出的文本文件保存到的目标文件夹,然后在 文件名 框,然后选择 Unicode文本 (* .txt)来自 保存类型 下拉列表。 看截图:
使用另存为功能导出到文本文件

3. 然后,它将弹出两个警告对话框,要求您仅将活动工作表导出为文本文件,而忽略与文本文件不兼容的功能。 请点击 OK 按钮, 按钮。

然后,活动工作表中的数据将导出为新的文本文件。

备注提供两款控制器:一款是 另存为 该命令只能将活动工作表的数据导出为文本文件。 如果要导出整个工作簿的所有数据,则需要将每个工作表分别保存为文本文件。


使用VBA将选择内容(或一列)导出到文本文件

以下VBA代码还可以帮助您将所选范围数据(例如,一列)导出到文本文件,请执行以下操作:

1. 按住 ALT + F11 键,然后打开 Microsoft Visual Basic应用程序 窗口。

2. 点击 插页 > 模块,然后将以下代码粘贴到“模块窗口”中。

VBA:将选择内容或整个工作表导出到文本文件

Sub ExportRangetoFile()
'Update 20130913
Dim 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

3. 然后按 F5 运行此代码的关键。 然后在弹出的对话框中选择要导出的数据范围,然后单击 OK 按钮,请参见屏幕截图:
选择一列导出为文本文件

4. 现在,在另一个“另存为”对话框中,请为此文本文件指定一个名称,并指定一个文件夹将该文件放入其中,然后单击 保存 按钮。


使用 Kutools for Excel 将选择(或一列)导出到文本文件

另存为 命令可以将活动工作表中的所有数据导出为文本。如果将指定非活动工作表的所有数据或工作表中的部分数据导出为文本文件会怎么样? Excel 的 Kutools's 将范围导出到文件 实用程序可以帮助我们轻松地将所选内容中的数据导出并保存为文本文件。  

Kutools for Excel - 包含 300 多个 Excel 基本工具。 享受全功能 30 天免费试用,无需信用卡! 现在下载!

1. 选择要导出到文本文件的范围(在本例中,我们选择列A),然后单击 Kutools 加 > 导入/导出 > 将范围导出到文件,请参见屏幕截图:
Excel插件:将选择导出到文本文件

2. 将范围导出到文件 对话框,如下图所示截图:
Excel插件:将选择内容导出到文本文件
(1)检查 Unicode文本 在选项 文件格式 部分;
(2)根据需要在“文本选项”部分中选中“保存实际值”选项或“保存屏幕上显示的值”选项;
(3)指定要将导出的文本文件保存到的目标文件夹;
(4)点击 Ok 按钮。

3. 在新打开的对话框中命名导出的文本文件,然后单击 Ok 按钮。
命名导出的文本文件

然后,所选内容(选定的A列)已作为文本文件导出并保存到指定的文件夹中。

Kutools for Excel - 使用 300 多种基本工具增强 Excel 功能。 享受全功能 30 天免费试用,无需信用卡! 立即行动吧!


在Excel中将多个工作表导出到单独的文本文件

有时您可能需要将多个工作表导出到 Excel 中的多个文本文件。 “另存为”功能可能有点乏味!不用担心! Excel 的 Kutools 拆分工作簿 该实用程序为Excel用户提供了一种简单的解决方法,只需单击几下即可将多个工作表导出到许多单独的文本文件中。 

Kutools for Excel - 包含 300 多个 Excel 基本工具。 享受全功能 30 天免费试用,无需信用卡! 现在下载!

1。 点击 Kutools 加 > 工作簿拆分工作簿。 看截图:
Excel加载项:将多个工作表导出到文本文件

2.在打开的“拆分工作簿”对话框中,请执行以下操作:
Excel加载项:将多个工作表导出到文本文件
(1)检查要导出到单独文本文件的工作表 工作簿名称 部分;
(2)检查 指定保存格式 选项,然后选择 Unicode文本(* .txt) 从下面的下拉列表中,请参阅左侧的屏幕截图:
(3)点击 分裂 按钮。

3.然后在弹出的浏览文件夹对话框中,选择要将导出的文本文件保存到的目标文件夹,然后单击 OK 按钮。

到目前为止,每个指定的工作表已作为单独的文本文件导出,并保存到指定的文件夹中。

Kutools for Excel - 使用 300 多种基本工具增强 Excel 功能。 享受全功能 30 天免费试用,无需信用卡! 立即行动吧!

在Excel中轻松批量保存每个工作表为单独的PDF / text / csv文件或工作簿

通常,我们可以使用“另存为”功能将活动工作表另存为单独的 .pdf 文件、.txt 文件或 .csv 文件。但是Excel的Kutools 拆分工作簿 实用程序可以帮助您轻松地将每个工作簿另存为单独的PDF / TEXT / CSV文件或Excel中的工作簿。


广告拆分工作簿pdf 1

Kutools for Excel - 使用 300 多种基本工具增强 Excel 功能。 享受全功能 30 天免费试用,无需信用卡! 立即行动吧!


演示:将Excel数据(选择或工作表)导出到Excel中的文本文件


Kutools for Excel:超过 300 个方便的工具触手可及! 立即开始 30 天免费试用,没有任何功能限制。 立即下载!

相关文章:

最佳办公生产力工具

热门特色: 查找、突出显示或识别重复项   |  删除空白行   |  合并列或单元格而不丢失数据   |   不使用公式进行四舍五入 ...
超级查询: 多条件VLookup    多值VLookup  |   跨多个工作表的 VLookup   |   模糊查询 ....
高级下拉列表: 快速创建下拉列表   |  依赖下拉列表   |  多选下拉列表 ....
列管理器: 添加特定数量的列  |  移动列  |  切换隐藏列的可见性状态  |  比较范围和列 ...
特色功能: 网格焦点   |  设计图   |   大方程式酒吧    工作簿和工作表管理器   |  资源库 (自动文本)   |  日期选择器   |  合并工作表   |  加密/解密单元格    按列表发送电子邮件   |  超级筛选   |   特殊过滤器 (过滤粗体/斜体/删除线...)...
前 15 个工具集12 文本 工具 (添加文本, 删除字符,...)   |   50+ 图表 类型 (甘特图,...)   |   40+ 实用 公式 (根据生日计算年龄,...)   |   19 插入 工具 (插入二维码, 从路径插入图片,...)   |   12 转化 工具 (小写金额转大写, 货币兑换,...)   |   7 合并与拆分 工具 (高级组合行, 分裂细胞,...)   |   ... 和更多

使用 Kutools for Excel 增强您的 Excel 技能,体验前所未有的效率。 Kutools for Excel 提供了 300 多种高级功能来提高生产力并节省时间。  单击此处获取您最需要的功能...

kte选项卡201905


Office Tab 为 Office 带来选项卡式界面,让您的工作更加轻松

  • 在Word,Excel,PowerPoint中启用选项卡式编辑和阅读,发布者,Access,Visio和Project。
  • 在同一窗口的新选项卡中而不是在新窗口中打开并创建多个文档。
  • 每天将您的工作效率提高50%,并减少数百次鼠标单击!
Comments (19)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
How would I amend the code for Export selection (or one column) to text file with VBA to autofill the filename with the contents of cell B2?
thanks
This comment was minimized by the moderator on the site
Hi,This is great, thanks a lot.
How would I alter the code for Export selection (or one column) to text file with VBA to auto fill the filename with the contents of cell B2?
This comment was minimized by the moderator on the site
Hi, this code is very useful for me. So thank you very much.
I want to ask that: When i'm using this code, a new line append automatically at the end of the txt content.
Can you help me about preventing this by vba?
This comment was minimized by the moderator on the site
hi guys, i used that code:
Sub ExportRangetoFile()
'Update 20130913
Dim 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

and have working, but i need to savea column with cells that contain the function"concatenate", and when i play the vba code, and i save the .TXT file, only what appears it's #REF. how can i to fix it? cause i need the data that appears on that cells?
This comment was minimized by the moderator on the site
Has anyone been able to figure this out? I am having the same issue.
This comment was minimized by the moderator on the site
HELLO
THANK YOU FOR YOUR VBA CODE
SOMETIMES ON CERTAIN TEXT THERE IS "TEXT" IN EXPORT .TXT
THANK YOU FOR WHY
This comment was minimized by the moderator on the site
Thanks for the awesome piece of VBA code to export data to a text file. I have used your code with some of my own. The data I am dealing with is extremely line-length specific and after the macro has run, the text file contains some double quotation marks " at random places, which was never present in my data before. I have tried adding a code line to remove this character at various stages but that does not seem to be working. I am aware that I can manually remove this but would like to resolve it in the VBA code.

Sub Macro3()
Dim wb As Workbook
Dim saveFile As String
Dim WorkRng As Range
On Error Resume Next
Selection.AutoFilter
ActiveSheet.Range("$A$1:$B$10591").AutoFilter Field:=1, Criteria1:= _
"=NSZAP*", Operator:=xlAnd
Range("A1").Select
ActiveCell.Offset(200, 0).Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveCell.Rows("1:10101").EntireRow.Select
Selection.Delete Shift:=xlUp
Range("A1").Select
Selection.AutoFilter
Range(Selection, Selection.End(xlDown)).Select
Set WorkRng = Application.Selection
Set wb = Application.Workbooks.Add
WorkRng.Copy
wb.Worksheets(1).Paste
Selection.Replace What:="""", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
saveFile = Application.GetSaveAsFilename(fileFilter:="Text Files (*.txt), *.txt")
Selection.Replace What:="""", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
wb.SaveAs Filename:=saveFile, FileFormat:=xlText, CreateBackup:=False
Selection.Replace What:="""", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
wb.Close
Application.CutCopyMode = False
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
This comment was minimized by the moderator on the site
Hi Harish,
For removing a specified character in bulk, you can replace the character with nothing.
Ctrl + H to open the Find and Replace dialog box, type the specified character in the Find what box, and type noting in the replace with box.
This comment was minimized by the moderator on the site
3/2 You might also note that you don't need to specify the wb prefix once you've .Add ed the workbook since it becomes active. Specifying wb might or might not be more efficient but it can be omitted from some commands. Actually I entirely scrapped the variable wb; I just go Workbooks.Add, and use ActiveWorkbook when needed. (As you are suppressing ScreenUpdating it wouldn't be obvious to some that wb is Active. Tip for beginners (and higher): I always, always develop with ScreenUpdating and DisplayAlerts as True, and when done with development, I consider toggling them off for some passage of code.)
This comment was minimized by the moderator on the site
2/2 - vbYesNoCancel msgboxes and Booleans to indicate whether to export all, selection, or a user specified range - a static String for the prior range address - if len(that static)1 then I set WorkRng=activesheet.UsedRange (You can't copy multiple areas with a single copy, though with a little work you could walk the areas and copy them piecemeal.) - Idiotic Microsoft does not save off empty rows at the start and the bottom of the saved range, and does not save off empty columns at the leftmost and the rightmost of the saved range. When I detect that (UsedRange is not row 1, col. 1, or xlLastCell is not completely lower-right) I msgbox to ask user if they want to plug A1 or the lower right cell. - Then I decided to preemptively address the upper left issue by inserting a row and setting A1 to be text like "The following is for range " - I close with activeworkbook.close SaveChanges:=False - Long time habit since I'm an angry proponent of cleaning up (and the world is a sad place thanks to irresponsible programmers who shirk that responsibility when it matters), I set WorkRng = Nothing on the way out :)
This comment was minimized by the moderator on the site
1/2 (since your software limits comment length) I am a serious coder and I want to inform you that the VBA code here is outstanding. It's clean, and not one line too much, or one too few. It's exemplary coding for demonstration of the process. I'll also mention that as I extensively researched solutions for export of selection, you and only one other person suggested dropping the range into a temp new workbook. All other answers were painfully manually, walking through cell by cell. Even Chip's code is much longer and runs slower (but intentionally so, as it is much more flexible - specifiable delimiters, etc.) Just for your amusement, I made some very minor tweaks but otherwise nearly lifted the code word for word because it basically dropped right into a very intricate and specialized application. Some of these tweaks are something I'm sure you do in practice, but they add lines of code (e.g. error checking) so showing them on this webpage would have muddied your display so that the essential elements of processing would have been cumbersome for readers to follow along. Anyway: (see part 2)
This comment was minimized by the moderator on the site
My query is as mentioned below: I have a report to generate everyday. The data what I get on the final row (only one row but more than one columns) I want it to export to another excel sheet which is a summary excel sheet saved separately on my desktop. Eg: Day1 report - final row export to Summary excel sheet row1 Day2 report - final row export to Summary excel sheet row2 Day3 report - final row export to Summary excel sheet row3 and so on.. Export should be done through a click button., which means when I click on export button the data on the sheet I calculated today should go and save on the Summary excel sheet row1, next day a new excel sheet report calculated should go and save on the Summary excel sheet row2 and so on.. Will this be possible to do... If yes please someone help me on this... Thank you in advance...:-)
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations