Email:      Pass:    Pass?
Close Window   
Free Ebook
Get Your Free Excel
Our Top 15 Excel Tutorials
Instant Access!

Subscribe for Free Excel tips & more!

Free Excel Forum

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

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 ...
How to import Text Files (CSV) into Excel
Text files in CSV format are one of the easiest ways to store and transfer data as it is one of the most compatible ...
Make Your Macros Available in All Workbooks in Excel
In Excel, you can make it so all of your macros can be easily accessed by any Excel workbook. To do this we need t ...
Make Users Enable Macros in Order to View a Workbook in Excel
Tutorial showing you how to make a user enable macros in a workbook in order to view the workbook. This is very im ...

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 have a macro which I am using currently for my data. Now I want to insert a script in my macro i.e.

I have 35 folders containing n number of files in all the folders. Now my macro gives all the file names in one files. Now i would like to add the scriprt for picking the data from specific sheets of the files. For Example each file will have 3-5 sheets and one sheet will have a common name in all the files. That sheet name might be "Reco Sheet" or "Reco Sheet 10101280" or "Reco Sheet 315100". Now I want to pick A2 column of the sheets which contains the Reco Sheet in their sheet names.

Please help me on this.

Thanks in advance.
Vimala K

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!!


I know theres a way to do this, but I cant figure out how to get it to work automagically.

would it be a macro in a third file?

this is a daily procedure that is a minor PITA, and somewhat time consuming.

as an example: todays master file has 7 sheets. names are column 1, with 3 rows of header labels
sheet one: 115 names
sheet two: 37 names
sheet three: 3488 names
sheet four : 70 names
sheet five : 1771 names
sheet six : 1771 names
sheet seven 1771 names

second file has 70 names on one sheet, names are column 2, one row of header info.

the same names appear in several sheets on the master file.
(example: sheets 5,6&7 are often identical)

the goal is to automate the comparison of the second file (only has one sheet) to the first file (all sheets) looking for the same name.

any names existing in BOTH files should ONLY be highlighted in the second file.

save revision of second file with current date ammended to file name, then do the same thing tomorrow.

any ideas on where I should start?

Thank you for any suggestions!


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?

Hello all,

I need to rename a couple of sheets and then save them each as individual workbooks.
The naming convention I have for the files definitely exceeds 31 characters (max for sheet names).

I currently have this macro and it works but only if I shorten the file/sheet name.


Sub CreateWorkbooks() 
    Dim wbDest As Workbook 
    Dim wbSource As Workbook 
    Dim sht As Object 
    Dim strSavePath As String 
    On Error Goto ErrorHandler 
    Application.ScreenUpdating = False 
    strSavePath = "C:\Documents and Settings\test" 
    Set wbSource = ActiveWorkbook 
    For Each sht In wbSource.Sheets 
        Set wbDest = ActiveWorkbook 
        wbDest.SaveAs strSavePath & sht.Name 
    Application.ScreenUpdating = True 
    Exit Sub 
    MsgBox "An error has occurred. Error number=" & Err.Number & ". Error description=" & Err.Description & "." 
End Sub 

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

But is it possible to amend this macro so that those sheets will be saved as individual workbooks, but with the names I want (I'll make a list of them file names) and not with their sheet name? I really need to name those files as per a standard requirement, around 42 characters long string.

Thanks in advance.

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

Hi there!

So I have some VBA code that saves to network drive Excel files based on their store number, after it all finishes to process using the VBA script, it creates a log sheet which shows me all the Excel files that were saved and processed. The log file is just a plain sheet that records the locations of the save in the network drive and the excel file name.

I send a notification to employees, they use those files and fill in some data into the sheets and then at the end of the month, we need to grab those files back from the network drive for analysis. The files are in the same exact network path and the file names will not change, what would be the best way to grab those files back from the network drive and import them into a workbook using VBA ?

I was thinking of using something that would be able to work by selection, or I select the rows and then have Excel import the selected files into a single workbook, to give an example:

Let say that I have 5 rows in the Log sheet in column A and each row represents a successful save:


Row1: \\networklocation\lists\region1\Excel_doc_1.xls
Row2: \\networklocation\lists\region1\Excel_doc_569.xls
Row3: \\networklocation\lists\region2\Excel_doc_587.xls
Row4: \\networklocation\lists\region2\Excel_doc_2.xls
Row5: \\networklocation\lists\region2\Excel_doc_6.xls

I would like to be able to play a VBA script, it would allow me to highlight by selection the range that I want to process, so in this example I would highlight A1:A5 and then I would continue the VBA procedure and Excel would import those 5 Excel files into a single workbook, so 5 sheets in this case, each representing the document imported ?

I was looking into Application.GetOpenFilename, but I am not sure what the best way is yet, I figured I would start here and get every ones opinion.

Thanks a lot in advance for your help and assistance.

Hi all,
i have two text files with data in it.
all i want to do is, import these text files which are on my desktop to excel sheet 1 and sheet 2 and add the values from the corresponding cells of sheet 1 and sheet 2 and the value goes to sheet 3.
i am looking to create a tool using vb script that imports two text files and inserts them in to excel sheets 1 and 2 and the values shuld be added and inserted in sheet 3. (or using macros)
i am not good with coding, i need help from some one here.

Dear Sir,
I have a sheet in which A column have some names In from B column to D column have data. I want to create sheets as per names in A column and all the sheets will be saved with data in my C drive with separate files. For ex. If there is 5 names then I need 5 separate files with the name of A column. I need to send those persons by mail.