Email:      Pass:    Pass?
Close Window   
TE
Subscribe for Email Updates!
Excel tips, help, and more!
E-mail:


Advertisements


Free Excel Forum

Save All Sheets As Separate Text Files

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

Hi,

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 Video Tutorials

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.

Thanks,
Bananas


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 http://www.mrexcel.com/forum/showthread.php?t=550778)

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.

Thanks,

Jason


Hello,

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

Mike


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.


VB:

 
 
 
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, _ 
        OtherChar:="-" 
         
        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 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:
Code:

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
    Wend
    
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 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?


Hello

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.

Code:

Sub Save_Text()

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

End Sub


Thanks


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.

[code]
Sub Copy_Sheets_to_Separate_Workbooks()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
If ws.Visible = True Then
ws.Activate
ActiveSheet.Copy
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"
.Close
End With
End If
Next ws
End Sub
[code]


Hello,

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,

etc.

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

Thanks,
V.


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?



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")
Sheets("ABC").Copy
With ActiveWorkbook
With .Sheets("ABC")
.Range("A:B").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End With
.SaveAs FileSaveName, xlTextWindows
.Close False
End With
End Sub


Thanks,
djanu


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.


VB:

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 
        sht.Copy 
        Set wbDest = ActiveWorkbook 
        wbDest.SaveAs strSavePath & sht.Name 
        wbDest.Close 
    Next 
     
    Application.ScreenUpdating = True 
     
    Exit Sub 
     
ErrorHandler: 
    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, _
CreateBackup:=False

ActiveSheet.Name = "Sheet3"


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

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:

COLUMN A

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.


After testing out some code to compile a directory of workbooks into one master workbook, creating a separate sheet for each original file, the Sheet Names all have a ".xls" in them, which I realize is due to the filename of the original file. However, I don't need this suffix and would like to run a quick script to clean off the ends since I have quite a few sheets now.

Anyone have a nugget o' wisdom? Thankee


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


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.

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
ActiveWindow.Close


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


Hi all,
I have two Excel files, one contains our data, and the other is from our source. The source data needs to be transferred into our file. The source file contains 58 sheets of data which needs to be moved to our file. The layout of each sheet in the source file is consistent with the layout in our file (thankfully). The problem is, the sheet names in the source file do not match the sheet names in our file.
Our file has sheet names "1","2",..."58". A straight up number sequence
The source file sheets names a "tab01-eng","tab02-eng"..."tab58-eng".
This (to me at least) poses a problem.
I was wondering, is it possible to remove all text from the sheet names and leave just the numbers without going at it manually? I've been trying to re-name the sheets based on the number values inside the name but have not been successful. As always, any help is greatly appreciated.

Regards