跳到主要內容
普通 ChatGPT 用戶? 嘗試整合 ChatGPT ExtendOffice.

如何將Excel數據(選擇或工作表)導出到Excel中的文本文件?

作者:凱莉 最後修改時間:2020-05-08

如果需要以文本文件格式交付工作簿,則需要將工作簿轉換為文本文件。 本文將向您介紹兩種將Excel數據輕鬆導出到文本文件的方法。

將一張工作表導出到Excel中的文本文件

默認情況下,Excel數據將以以下格式保存為工作簿: 。XLSX。 但是,我們可以將現有工作簿的工作表導出為文本文件, 另存為 特徵。 請執行以下操作:

1. 轉到要導出到文本文件的工作表,然後單擊 文件 辦公按鈕)> 另存為.

2. 在開 另存為 對話框中,選擇要將導出的文本文件保存到的目標文件夾,然後在 文件名 框,然後選擇 Unicode文本 (* .txt)來自 保存類型 下拉列表。 看截圖:
使用另存為功能導出到文本文件

3. 然後,它將彈出兩個警告對話框,要求您僅將活動工作表導出為文本文件,而忽略與文本文件不兼容的功能。 請點擊 OK 按鈕, 有電 按鈕。

然後,活動工作表中的數據將導出為新的文本文件。

備註:“ 另存為 該命令只能將活動工作表的數據導出為文本文件。 如果要導出整個工作簿的所有數據,則需要將每個工作表分別保存為文本文件。


使用VBA將選擇內容(或一列)導出到文本文件

以下VBA代碼還可以幫助您將所選範圍數據(例如,一列)導出到文本文件,請執行以下操作:

1. 按住 ALT + F11 鍵,然後打開 Microsoft Visual Basic for Applications 窗口。

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將選擇內容(或一列)導出到文本文件

另存為 命令可以將活動工作表中的所有數據導出為文本。 如果將指定的非活動工作表的所有數據或工作表中的部分數據導出為文本文件怎麼辦? Kutools for Excel's 將範圍導出到文件 實用程序可以幫助我們輕鬆地將所選內容導出並保存為文本文件。  

Excel的Kutools - 包含 300 多個 Excel 基本工具。 享受全功能 30 天免費試用,無需信用卡! 現在下載!

1. 選擇要導出到文本文件的範圍(在本例中,我們選擇列A),然後單擊 Kutools 加 > 導入/導出 > 將範圍導出到文件,請參見屏幕截圖:
Excel插件:將選擇導出到文本文件

2. 將範圍導出到文件 對話框,如下圖所示做截圖:
Excel插件:將選擇內容導出到文本文件
(1)檢查 Unicode文本 在選項 文件格式 部分;
(2)根據需要在“文本選項”部分中選中“保存實際值”選項或“保存屏幕上顯示的值”選項;
(3)指定要將導出的文本文件保存到的目標文件夾;
(4)點擊 Ok 按鈕。

3. 在新打開的對話框中命名導出的文本文件,然後單擊 Ok 按鈕。
命名導出的文本文件

然後,所選內容(選定的A列)已作為文本文件導出並保存到指定的文件夾中。

Excel的Kutools - 使用 300 多種基本工具增強 Excel 功能。 享受全功能 30 天免費試用,無需信用卡! 立即行動吧!


在Excel中將多個工作表導出到單獨的文本文件

有時您可能需要將多個工作表導出到Excel中的多個文本文件。 另存為功能可能有點乏味! 別擔心! Kutools for Excel的 拆分工作簿 該實用程序為Excel用戶提供了一種簡單的解決方法,只需單擊幾下即可將多個工作表導出到許多單獨的文本文件中。 

Excel的Kutools - 包含 300 多個 Excel 基本工具。 享受全功能 30 天免費試用,無需信用卡! 現在下載!

1。 點擊 Kutools 加 > 工作簿拆分工作簿。 看截圖:
Excel加載項:將多個工作表導出到文本文件

2.在打開的“拆分工作簿”對話框中,請執行以下操作:
Excel加載項:將多個工作表導出到文本文件
(1)檢查要導出到單獨文本文件中的工作表 工作簿名稱 部分;
(2)檢查 指定保存格式 選項,然後選擇 Unicode文本(* .txt) 從下面的下拉列表中,請參閱左側的屏幕截圖:
(3)點擊 分裂 按鈕。

3.然後在彈出的瀏覽文件夾對話框中,選擇要將導出的文本文件保存到的目標文件夾,然後單擊 OK 按鈕。

到目前為止,每個指定的工作表都已作為單獨的文本文件導出並保存到指定的文件夾中。

Excel的Kutools - 使用 300 多種基本工具增強 Excel 功能。 享受全功能 30 天免費試用,無需信用卡! 立即行動吧!

在Excel中輕鬆批量保存每張工作表為單獨的PDF / text / csv文件或工作簿

通常,我們可以使用另存為功能將活動工作表另存為單獨的.pdf文件,.txt文件或.csv文件。 但是Kutools for Excel的 拆分工作簿 實用程序可以幫助您輕鬆地將每個工作簿另存為單獨的PDF / TEXT / CSV文件或Excel中的工作簿。


廣告拆分工作簿pdf 1

Excel的Kutools - 使用 300 多種基本工具增強 Excel 功能。 享受全功能 30 天免費試用,無需信用卡! 立即行動吧!


演示:將Excel數據(選擇或工作表)導出到Excel中的文本文件


Excel的Kutools:超過 300 個方便的工具觸手可及! 立即開始 30 天免費試用,沒有任何功能限制。 現在就下載!

相關文章:

最佳辦公生產力工具

🤖 Kutools 人工智慧助手:基於以下內容徹底改變數據分析: 智慧執行   |  生成代碼  |  建立自訂公式  |  分析數據並產生圖表  |  呼叫 Kutools 函數...
熱門特色: 尋找、突出顯示或識別重複項   |  刪除空白行   |  合併列或儲存格而不遺失數據   |   沒有公式的回合 ...
超級查詢: 多條件VLookup    多值VLookup  |   跨多個工作表的 VLookup   |   模糊查詢 ....
高級下拉列表: 快速建立下拉列表   |  依賴下拉列表   |  多選下拉列表 ....
欄目經理: 新增特定數量的列  |  移動列  |  切換隱藏列的可見性狀態  |  比較範圍和列 ...
特色功能: 網格焦點   |  設計圖   |   大方程式酒吧    工作簿和工作表管理器   |  資源庫 (自動文字)   |  日期選擇器   |  合併工作表   |  加密/解密單元格    按清單發送電子郵件   |  超級濾鏡   |   特殊過濾器 (過濾粗體/斜體/刪除線...)...
前 15 個工具集12 文本 工具 (添加文本, 刪除字符,...)   |   50+ 圖表 類型 (甘特圖,...)   |   40+ 實用 公式 (根據生日計算年齡,...)   |   19 插入 工具 (插入二維碼, 從路徑插入圖片,...)   |   12 轉化 工具 (數字到單詞, 貨幣兌換,...)   |   7 合併與拆分 工具 (高級合併行, 分裂細胞,...)   |   ... 和更多

使用 Kutools for Excel 增強您的 Excel 技能,體驗前所未有的效率。 Kutools for Excel 提供了 300 多種進階功能來提高生產力並節省時間。  點擊此處獲取您最需要的功能...

產品描述


Office選項卡為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