Close Window   
Free Ebook
Get Your Free Excel
Our Top 15 Excel Tutorials
Instant Access!
Subscribe for Free Excel tips & more!

Save All Sheets As Separate Text Files

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More


I have a workbook with 45 sheets and I am looking for a script to save all sheets as separate text files. The script should take the "save as" file names from the sheet names.

Although they should be saved as text files, the extension is php as given in the sheet names.

Unfortunately I've been out of practice for years with Excel VBA macros of this deepth, so I would highly appreciate some help for the above!

Thanks a lot, Minnesaenger

View Answers     

Similar Excel Tutorials

Sort Worksheet Tabs - Ascending or Descending Order
This macro will sort all of the worksheets in the current workbook. It can sort in ascending or descending order. ...
Export an Excel File to a CSV File
How to export an Excel file to a CSV file (comma separated values file).  This allows you to turn any Excel spread ...
Change the Default Number of Worksheets Created in a New Excel Workbook
Learn how to change the number of worksheets that are in new Excel workbooks. Excel workbooks always have 3 worksh ...
Delete All Chart Sheets in Excel - Only Chart Sheets are Deleted - Not Embedded Charts
Delete all chart sheets and tabs in Excel using this macro. This will only delete the charts and graphs that are i ...

Helpful Excel Macros

Excel Macro to Save a Specific Worksheet as a New File
- This Excel Macro allows you to save a specific worksheet within the Excel Workbook to its own new file. You will be a
Save the Current Worksheet as a New Excel Workbook File
- This Excel Macro will save the currently visible/active worksheet (the one that you see when you run the macro) to a
Name Worksheets Based on Cell Contents
- This macro allows you to have your worksheets named by whatever is in a particular cell within a worksheet. This means
Excel Macro that Searches Entire Workbook and Returns All Matches
- This is the ultimate Lookup Macro for Excel. It will search every worksheet in the workbook and return all of the mat
Delete All Chart Sheets in Excel - Only Chart Sheets are Deleted - Not Embedded Charts
- Delete all chart sheets and tabs in Excel using this macro. This will only delete the charts and graphs that are in the

Similar Topics

I have a wokbook with MANY sheets. I was wondering if there was a quick way
to save the sheets into separate files (perhaps using tab names) whilst
retaining each ones header and footer.

I'm trying to create a macro to save multiple sheets of data as separate text files but i cannot have the .txt file extension. Any ideas?

Hi guys,

I've got an Excel sheet with approximately 2,000 file names (all csv files) stored in the first column. I will call this sheet files.xls. I want to create a macro which does the following:
Step 1. Open a file called template.xls. This sheet contains all the formatting and some VBA code.
Step 2. In template.xls ... import the first file (e.g. a.csv) mentioned in the list of files listed in the first column in files.xls.
Step 3. Save template.xls with the imported data from a.csv as a new file called a.xls.
Repeat this for all the files in files.xls

Can someone help me write this vba script ? I'm a complete newbie in vba but I really need this script to save lots of time.


I have a spreadsheet with 10 sheets. I need to set up some VBA code so the macro will save three of the tabs as separate .csv files for uploading into our software.

The three tabs are named: EID Upload, Wages with Locals Upload, Wages without Local Upload.

Right now, I would like the new file names to match the sheet names.



I have multiple ".csv" files with different names in c:\temp\ that I want to combine into a single workbook in different worksheets and then save the workbook as "Output"

Is it possible to save a workbook in a macro script or no? If not it's not that big of a deal. Thank you for the help.

(xposted to

I need to create a macro that will IMPORT TXT files (the number of files will vary from 2 to 10, their size will be a constant 4 columns) from a specific folder (always the same folder). The names of the TEXT files will never be the same. I need to IMPORT each file into a separate tab within the same workbook. The files need to be imported as delimited, comma separated, with the first column formatted as TEXT. (If this could be made to operate all by itself at set intervals, deleting the original TEXT files it used each time, that would be a bonus)

Now, if you really want to get dirty...what would be EVEN MORE helpful (I don't even know if this is possible or not) would be to create something that would poll a remote FTP folder and download a TEXT file, import it into a specific EXCEL spreadsheet to a specific tab, save a different tab within that spreadsheet that has made calculations based upon the imported text file and then upload that newly save file to an internet site.

I would be VERY HAPPY if somebody could just help me get the first part right. I've done a lot of searching on the net and I just can't get it figured out. I keep getting errors with everything I try. So, I'm hoping for some assistance from you nice folks.

Thanks in advance!



I've searched the forum but can't find a thread that addresses this situation:

I have a folder (C:\Files) of many LTR files containing tilda-delimited mailing data. Each letter contains data for a different mailing template, so I don't want to combine them into one XLS file. Instead, I'd like to create a script to open each LTR file, separate the data using Text-To-Columns with tilda-delimination, and then save that file as an XLS file using the same name as the LTR file that was originally opened.

The goal is for the script to run through all the files in the folder, so if I start with 10 LTR files, after running the script, I should end up with 10 LTR files and 10 XLS files in the folder.

Any help would be greatly appreciated!!


Hello everyone,

I want to run a macro for a text to columns in all the sheets I have in my workbook. The number of sheets I'm using can go up to 60. The below code I found on the internet is only doing the work for the first sheet. It does display the other sheets' names but doesn't perform the text to columns command.

After I have all the sheets as I want them, I eventually would like to save each sheet as a separate excel file.

Is any of you able to help me on that? I.e. first get my macro to work in each sheet and then save the sheets as individual excel files.


Sub WorksheetLoop() 
    Dim WS_Count As Integer 
    Dim I As Integer 
    WS_Count = ActiveWorkbook.Worksheets.Count 
    For I = 1 To WS_Count 
        Selection.TextToColumns _ 
        Destination:=Range("A1"), _ 
        DataType:=xlDelimited, _ 
        TextQualifier:=xlDoubleQuote, _ 
        ConsecutiveDelimiter:=False, _ 
        Tab:=False, _ 
        Semicolon:=True, _ 
        Comma:=False, _ 
        Space:=False, _ 
        Other:=False, _ 
        MsgBox ActiveWorkbook.Worksheets(I).Name 
    Next I 
End Sub 

If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines

I have a sheet where column A has the names of different managers I need to distribute data to.

I would like a macro to filter by manager name, create a separate sheet with all the data on it, format it the same way and save the sheets in separate files.

Not only would I like the text to be formatted the same way, I would like the sheet itself to have the same page layout.

Anyone have one handy?

I am trying to import multiple text files form a specific directory (C:\Documents and settings\user\desktop\test) into a new excel workbook, pasting each text file onto its own sheet in order. The text files are numerically named 25, 500, 1000, 2000, 3000, etc.. up to 40000.

The naming of the sheets is not important, but the text files must be placed into the sheets in order.

Ultimately, I would like to run a script that checks my test folder automatically every 10 minutes for text files, and if found, puts them all in the excel workbook and saves it, then runs a set of macros which I have already written to process the workbook into reports.

For now, I just need to know how to write a script to call up check the folder for text files, if found then call up excel and paste in order to sheets in a workbook, then save the workbook. If no files are found, I need it to wait 10 minutes and then run the check again.

I am using Excel 2007.

Here is the code I have so far:

Sub LoadSpaceDelimitedFiles()

Dim idx As Integer
Dim fpath As String
Dim fname As String

    idx = 0
    fpath = "c:\documents and settings\user\desktop\test"
    fname = Dir(fpath & "*.txt")
    While (Len(fname) > 0)
        idx = idx + 1
        Sheets("Sheet" & idx).Select
        With ActiveSheet.QueryTables.Add(Connection:="TEXT;" _
          & fpath & fname, Destination:=Range("A1"))
            .Name = "a" & idx
            .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .TextFilePromptOnRefresh = False
            .TextFilePlatform = 437
            .TextFileStartRow = 1
            .TextFileParseType = xlDelimited
            .TextFileTextQualifier = xlTextQualifierDoubleQuote
            .TextFileConsecutiveDelimiter = False
            .TextFileTabDelimiter = False
            .TextFileSemicolonDelimiter = False
            .TextFileCommaDelimiter = False
            .TextFileSpaceDelimiter = True
            .TextFileOtherDelimiter = False
            .TextFileColumnDataTypes = Array(1, 1, 1)
            .TextFileTrailingMinusNumbers = True
            .Refresh BackgroundQuery:=False
            fname = Dir
        End With
End Sub

When I run the macro, nothing happens. I have text files loaded into the test folder, but there appears to be no activity when the code is run and no errors.

Thanks in advance.


I have a workbook with over 50 sheets on three workbooks. I need to convert each sheet to a text file and save it to the same folder as the .xls(m) file. These text files then get uploaded to another software.

The current code I have seems to create a .txt file that just fine, however the go to next tab automatically code options i have tried result in errors. I need to start from the first sheet and move to the last sheet (variable number of sheets) and run the code below at each sheet.


Sub Save_Text()

NewName = ActiveSheet.Name & ".txt"
Direct = ActiveWorkbook.Path
ActiveWorkbook.SaveAs Filename:=Direct & "\" & NewName, FileFormat:=xlText

End Sub



I have 500 folders, each of which contain about 5 text files. I am attempting to code a macro which would look into each of these folders and open the text files and save each of the texts files as a different worksheet in a workbook and save the resultant workbook. Then it should go into the next folder, again open the text file and save them as worksheets in a different workbook which should be saved.

As an example,

Folder 1 has Text files a,b,c,d,e
Folder 2 has Text files f,g,h,i,j,


So, after looking into Folder 1, the text files a,b,c,d,e should be as worksheets in a workbook say wkbI.xls and wkbI.xls should be saved. and then similarly for Folder 2.

I am relatively new to the macros and am totally lost as to how to go about this. Can someone kindly help me. It would be really helpful.

So far, I have been able to make a macro which asks me to navigate to ONE folder and then asks me to select the text files. It then brings the text files to the worksheets. I just to make this work for multiple folders.

Can someone pls help....


The other day, someone sent me a spreadsheet with multiple worksheets. I needed to be able to send each worksheet to an individual for review, and each recipient did not need to see everyone else's sheet. So I found a tip on-line that included the following VBA code to save the sheets to separate Excel files. Life was good until those who didn't have Office 2007 could not open their sheets, even though the original file had been saved with the Excel 97-2003 option. They had first received the message, "The file you are trying to open, "<filename>" is in a different format than specified by the file extension. Verify that the file is not corrupted and is from a trusted source before opening the file. Do you want to open the file now?" When they said yes, all they saw was a scramble of machine code. I thought that maybe there was a special extension for Excel 97-2003 files, but when I looked it up in a book I have, it just said the extension was .xls, which is what the code used. Is there other formating that needed to be saved? Any help would be appreciated. Here's the code.

Sub Copy_Sheets_to_Separate_Workbooks()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
If ws.Visible = True Then
With ActiveWorkbook
.SaveAs "C:\Documents and Settings\bruce.vanbibber\My Documents\TITLE I - M A I N\Title I Budgets\Allocation Budgets - Schoolwide - FY 10\" & _
InputBox("Please enter the Save As Name...", "Worksheet Save As") & ".xls"
End With
End If
Next ws
End Sub

Hi All,

I have a VBA code that successfully saved multiple excel sheets into multiple Text Delimited files. Here is the VBA Code:

Please Login or Register  to view this content.

This code has fruitfully saved all text delimited files from a workbook into a folder.

But I want to save all these text files into Text Macintosh format. I am pretty new to VBA and I have found the above VBA code in only.

Can someone tell me what changes in the code would provide me Text Macintosh format files?

Thanks in Advance.

I am using this script to save/export to text file, but it saves/exports the whole sheet ABC. How can I modify this script to save/export only column A.

This is my script:

Private Sub CommandButton3_Click()
Dim FileSaveName As Variant
FileSaveName = Application.GetSaveAsFilename(fileFilter:="Text (Tab delimited) (*.txt), *.txt")
With ActiveWorkbook
With .Sheets("ABC")
End With
.SaveAs FileSaveName, xlTextWindows
.Close False
End With
End Sub


I have a workbook that has 100 sheets. I want to save each sheet as a
separate xls file. Even though I could save each by copying into new
worksheet and save manually, I am looking for a way to automate it by using
script. Can anyone help?

Im using the below code to save the data on a worksheet to a text file. The problem Im having is when the macro names my text file, it also renames the whole workbook. I need it to name the text file based on "myfilename" in the macro, but I dont want it to rename the Excel workbook. Thanks in advance for any help.

myfilename = Worksheets("Short Script").Range("k1")

' Save file name and path into a variable
template_file = ActiveWorkbook.FullName

filesavename = Application.GetSaveAsFilename( _
InitialFileName:=myfilename + ".txt", _
fileFilter:="Text Files (*.txt), *.txt")

' Save file as .txt TAB delimited
ActiveWorkbook.SaveAs Filename:= _
filesavename, FileFormat:=xlText, _

ActiveSheet.Name = "Sheet3"

Hi all.

I am looking for a script that will open a number of files in a specified directory in excel and re-save them. The reasoning behind this is that a 3rd party system saves the files as .xls but in reality there are not true excel files therefore the files must be opened and re-saved before they can be linked to.

Can anyone help with a script that will look for *.xls files and open them in excel and re-save them?

Hi there,

Over a year ago I created a macro (with lots of help) to take a spreadsheet that had many sheets in it, and create a folder structure named for each sheet. The script then saved each sheet in .htm format into its corresponding folder.

It was run after we upgraded to Excel 2k7 but from a Terminal server running 2k3...the person who ran the script has 2k7 on her local machine but pointing to the same xla file as her TS profile does.

I am thinking that this may have caused an issue with her xla file because when she ran it the other day, it save the files without the .htm extension.

To make a short story long, I am trying to run the macro from my 2k7 installation to see if it does the same thing, but I get an error 76 folder not found when I run the script.

Can anyone help me with what is going on?

Here's the code, the error line is bolded.

Sub Save_Sheets_EBITDA()
'Save each sheet in the current workbook as a separate workbook containing just that sheet.
'The workbooks are saved in HTML format in the C:\temp\excel\\ folder.
Dim fso As Object
Dim sh As Worksheet
Dim folder As String
Dim rpttype As String
Dim rptScope As String
Dim Region As String

' Dim workSheetCount As Object
' Dim i As Integer

rptScope = Application.InputBox("Enter Monthly, Quarterly or Yearly")
Region = Application.InputBox("Enter the Region")
Set fso = CreateObject("Scripting.FileSystemObject")

' workSheetCount = ActiveWorkbook.Worksheets.Count

' Remove hidden sheets from your document

' i = 1
' While i

I recently posted this on another site. I feel like it is a dumbed down question for what is already here. Should be cake mix for you guys. I just have one column in the sheet, there are three sheets. I bet it will be quite easy for you all:

I have an excel file that has a macro on the first sheet which inputs data on three consecutive sheets. I need these sheets to be made into individual text files. Right now I just copy each tab to a new book and then save that book manually as a .txt file under a specific name (Book10, Book11, Book12) that is referenced for another macro and workbook. I do this for each of the three sheets. I can do it fine but I want other users to not have to deal with it.
I would like a macro that will save a copy of each of these sheets as I described. I don't want the user to have to select anything from a Save As screen, just save it automatically where it's supposed to go (S:\Tax\Sales Tax\Certificate Details\Book10.txt). I'm hoping the original tab names will stay the same (Col1, Col2, Col3) and that the original book will be untouched.
Any help on this would be awesome. I'm not too savvy with VBA, most of my macros are recorded, so if you could let me know what i need to change to make this macro specific to my file, that would be so helpful. Thanks a lot to people who respond to questoins like this, it is great!

Hi -

I'm using Excel 2004 on a Mac. Whenever you save a text file (tab delimited) in Excel it always prompts to ask "Do you want to save changes you made to document.txt?" Even if you have just saved the file, when you go to close the file, it will still prompt to ask if you want to save.

Basically I have a macro that copies some data, opens a new workbook, pastes the data to the new worksheet, saves it as text, then closes then repeats this over and over. But it always prompts to save changes and I just have to click Don't Save over and over.

Here is the end of the macro where it saves and closes the file.

ActiveWorkbook.SaveAs Filename:= _
"Macintosh HD:Users:User1:Desktop:Samples:"Sample.txt", FileFormat:= _xlText, CreateBackup:=False

If anyone knows how to disable the prompt that automatically asks if I want to save changes for text files, I would be really grateful. Obviously I would still want it to ask, if there are changes since my last save, but if not I would like it to just close even for text files.

Anyone have any ideas? Thanks!!

I need to save a lot of files under whatever I put into cell B and C 2 (I merged the cells into one). How I would like it to work, is whatever I change that cell into, it will create a new save file under that cell name. I have never messed with macros before, and if I need to change it to being just one cell I can do that too. Also, please note that I have multiple sheets on this original save file. The sheet names are April May June etc. for the rest of this year, and I would like the name to change on all of those as well.

I'm not sure I'm able to do this, but I ran a vbscript to create multiple text files. Now I have 68 text files that all have unique name in one folder. I need to add all the contents of the text files into one excel spreadsheet. My hope was to create a header with the name of the file and then the contents to be in the column below. The text files consist of names, so basically you would have a text file called "houston.txt" and the contents would have a bunch of names all separated with a return key.


Bill Marts
Sue Dawson
Mark Philp
Dawn Stevens
Mary Hug

So if I can't get this done with a script, I will have to open up each file, create a header and type(copy/paste) in the contents of each text file.

Please let me know if any other information is needed.


I want to create a number of text files using VBA.

Each file will have a particular header eg "UK News", "USA News" "Irish News"

The files will be created as Word documents and stored in a folder C:\inbound

The text files need to be created in a folder called C:\outbound
(The files need to saved as unicode text files)

The input file names will be: UK.doc, usa.doc, ireland.doc
Output file names need to have a prefix eg UKddmmyy.txt, USddmmyy.txt

I thought it might work to have these variables stored in cells in a Excel spreadsheet - and then we can modify them as our need changes.
Headers: File name Prefixes:
UK News UK
Irish News IE

Could anyone advise how we could use VBA to input the headers into each file, and then save them with correct names. I presume some form of loops/arrays will be needed but am not that good with these !

Many thanks

I have created a macro that opens 4x text format files as Excel files, which
copies and pastes their data into a master excel template.
The 4x text files are inserted into one data sheet, the updated data sheet
forms the basis for several pivot tables which are then run by the macro.
The macro created closes the 4x text files when they have been copied.

When closing the text files the prompt "Do You Want To Save xxxxx.txt?"
appears, requiring manual intervention to select NO. The macro continues, the
prompt appears 4 times for each file.

The macro is required daily, no data manipulation of the text files occurs,
they are only opened, copied, and closed.

I do not want the Save Prompt appearing.

How can I remove this?

Many thanks