Close Window   
Free Ebook
Get Your Free Excel
ebook!
Our Top 15 Excel Tutorials
Instant Access!
E-mail:
Subscribe for Free Excel tips & more!
E-mail:
Advertisements



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 Tutorials

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 ...
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 ...
Close Excel Workbook using VBA Macros
How to close an Excel workbook using VBA and macros, including how to save the file before you close it or discard ...
Macro to get Data from Another Workbook in Excel
Macro to get data from a workbook, closed or open, over a network or locally on your computer. This is a versatile ...

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?


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)

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


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



I have the following code in a macro:
Code:

fileSaveName = Application.GetSaveAsFilename( _
     fileFilter:="Text Files (*.csv),*.csv,")
If fileSaveName <> False Then
     MsgBox "Save as " & fileSaveName
End If


When it runs, it appears to do everything I want it to do. It opens a "Save As" window, the "Save as type:" is prefilled with "Text Files (*.csv)", however, when I change the file name and click "Save," it doesn't actually save anything in the folder.

Any help would be greatly appreciated.


I am setting up a spreadsheet to analyze data from a program I use. The files are with extension .npe, but to upload it, I added the .txt.

I have the following worksheets: Input, Run Selection, Summary, and R1 through R20.

On the input tab are the path file locations, starting in C6 and down to C25 (up to 20 files). The paths are from another workbook, and brought in automatically to the Input spreadsheet.

I want to write a VBA code that reads the info in C6-C25, and writes the text from those files into sheets R1-R20. However, there may be 2 files, 13 files, or 20 files.

I have most of the code written, but I am having the hardest time figuring out how to read in each of the files and put them into the individual sheets. I have found code that comes close, but the issue is that the code is really extensive, and I'm not sure I need most of it.

I have attached a sample text file, and the coding I have so far. Any assistance would be appreciated.

Thanks


I have a few files sent out to field users. These users, for some reason, really love to change my .xlsm excel files to .xlsx when they save. Is there any VBA code that will help with this? The closest I could come up with is using the BeforeSave workbook event, but this still allows the user to make any changes they want to extension once the Save As dialog box appears.

Example of what I want:

User clicks save as, types in a new file name and selects .xls
Macro assigns the previous file extension to the file then saves it.


I have a workbook that consists of 2 worksheets, after the data is entered
the 2 sheets are separate and saved into a folder with the the 2 sheets as
individual files. This all works fine if I specify the saving location, what
I would like to know is how to automatically save the the new folder and
files into the root directory of the device the original workbook was
started. The workbook runs on several portable usb devices so the starting
location can vary from device to device. The name of the parent folder that
the new folders and files are saved to is constant. ie: ??:\Sheets\??????.
Thank you



I'm running Excel 2007 on a WinXP (SP2) machine (dual core, 4GB RAM, 8GB free HDD space).

I keep getting a strange error message frequently when I try to save large files (> 2MB) with lots of sheets. Excel seems to save okay (the save progress bar at the bottom finishes), but then a dialog box pops up saying:
Quote:

Your change could not be saved to filename.xls because of a sharing violation. Trying saving to a different file.

Once I hit Ok on that dialog box, it immediately gives another dialog:
Quote:

The file you are trying to open, '56C2F10', 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? Y/N

I hit Yes, and it then opens another version of the same file and gives me the Save As... dialog box. Once I enter a different name, it saves that file and closes it, leaving me with the original file that I was trying to save (but could not). I then close that original file and open the new/renamed file and it contains all my changes.

As I mentioned, this happens usually with large files (>2MB), usually ones which have many sheets (>10). It has happened with .xls, .xlsx, and .xlsm formats. I am saving only to my local hard drive, and the file is not shared with/on any other user/server/machine.

It is very annoying, since I have to interrupt my work to close the "corrupted" file and open the newly renamed one, not to mention that I end up with multiple copies of the files.

Any insight as to what's causing this problem, and how to fix it?


I was reading another thread similar to my issue, but one of the replies in the thread said only one question per thread...so I figured I better just create a new thread.

My issue: I have 494 .txt files that are output files from a label scanner that I am trying to put into Excel 2007 to be able to work with better. Currently I am going to Data > Get External Data From Text > Select 1 file > Text Import Wizard (Delimited > Delimiters [Tab, Semicolon, Comma, Space, Other field set to (=)]) This seems to work out the best to separate the data in the text files into columns that I can work with. All the file names are the date that the output files were created, so I have been manually imputing that date into column A to separate the strings of data from each file.

I don't know if there is any better way to do this. If there is, it is way beyond my knowledge of Excel. I tried recording a macro, but that failed miserably. I guess my hope is that I could get Excel to find the files and import them, separating the file data with the date in the file name.

Any help is greatly appreciated!!!

Adam

I have a large dataset that will be read by a statistics package. The data has to be a txt file for each sample (the sample is a text string that is being analyzed). So I have a Filename column (A) and a Sample column (B), which have 1400+ rows (i.e. 2 x 1400 cells). How can I program a VBA macro to take the contents of Column B, row n, put it into a text file, then save that text file with the name in Column A, row n, and loop on down all the rows so I end up with 1400 or so files?

The text in the Sample can be quite long (but not more than say 2000-ish characters).

Neil.


I have an excel workbook with 3 different worksheets contained, these sheets are labeled:

Generic, CoBrand, TriBrand what I would like to do is have a script reference the excel workbook and save each sheet as a separate tab-delimited text file.


But to only export the Fields that have data (check against blank spaces or characters).

we will be using the same workbook for the headers and deleting the data daily, I know that excel will still act as though data is within those fields and I can't use it if it saves multiple blank lines in the text file.

Thanks in advance,
Best regards,
Joe


I have an excel file that is distributed within my organization. That file contains certain worksheets that the end-users do not need to see or make changes to, so in the VBA project, I change those files to "VeryHidden". Then I protect the workbook's VBA code by going to Tools->VBAProject- Project Properties-> Protection tab and adding a password. This worked great as it allowed all the other sheets to function as intended, but didn't allow the users to access the sheets that were hidden.

However, we recently upgraded to Excel 2007, and the password protection is stripped whenever the file is saved with the extension .xlsx. I can save it as a "macro-enabled" file with the extension .xlsm, but all that needs to be done to remove the password is to save it with the .xlsx extension again. Is there something that can be done in Excel 2007 to protect VBA code, or am I doing something completely wrong?

This is my first post here, so please let me know if I've done something wrong!


I need to save a file as a Tab-delimited text file and as you may be aware any fields that contain punctuation (line breaks, quotes, commas and periods) are saved by Excel with quote marks around the contents. There is no way to disable this function.

I need the file to be saved without these quote marks.

I found this VBA script that supposedly will save Excel spreadsheets as txt files without these added quote marks:

Code:

Sub SaveExcelToTabDelimitedWithoutQuotes()
   Dim r    As Long
   Dim arr  As Variant
   Dim file As Variant

   file = Left(ActiveWorkbook.FullName, InStrRev(ActiveWorkbook.FullName, ".") - 1) & ".txt"
   Set file = CreateObject("Scripting.FileSystemObject").CreateTextFile(file, True, True)

   With ActiveSheet
      arr = .Range(.[A1], .UsedRange.Cells(.UsedRange.Cells.Count))
      For r = 1 To UBound(arr)
         file.WriteLine Join(WorksheetFunction.Index(arr, r), vbTab)
      Next
   End With

   file.Close
End Sub


Unfortunately when I run the script I get an error-
"Run-time error '13':
Type mismatch"

on the line:

file.WriteLine Join(WorksheetFunction.Index(arr, r), vbTab)

Any help would be greatly appreciated!


Fellow Forum Members,
Can someone out there please help me out with a VBA scrpt that will do the following:

I have five TEXT files, and each of these TEXT files contains three different text entries. What I need is an EXCEL VBA script that will find and replace one set of text entries with three different text entries (ALL IN CAPS). Is it possible to write a script that will enable Excel to open each text file (located in specified path) then do a Find/Replace operation, and then close the TEXT file?

This is a basic example. In reality I have 50 text files each one containing three text entries I need changed to something else. I'm currently doing this find/replace work manually and I would like to automate it. Any help will be greatly appreciated. Thanks in advance.




Hi,

I have one master Excel workbook that acts as a log-in (or gateway) file (1MB) and then each passworded link in this file opens up a separate larger workbook for each logged in memeber to use (10MB+ per link (20-30 Links).

I want the user to be able to save (Done through Macros), manipulate and interact with the data in the files BUT I don't want them to be able to see the excel menus and be able to Edit the pages, copy or save the files.

In Excel 2003 there was an 'Add Interactivy' button in the 'Save as a Webpage' file menu that seems to have been removed that would of been perfect for what I was looking for...

I can't use SkyDrive because it keeps telling me that the file exceeds their 5MB 'view online' limit, and it prompts the user to download and save the file (which defeats the object of what I want).

Basically... I want people to type in an address, for it to open an Interactive log-in workbook. For them to use the Log-in workbook to open their own personal Interactive workbook or 'Profile' and be able to Interact with it at SHEET LEVEL ONLY. (No menus, no editting, no copying, so saving etc.)

I've created all of the Excel files I need to compele this task (offline) and it works perfectly- I just need it to work online!

The files are Xlsm files and over 10MB.

Thanks in advance for any help!

I'm converting a text file with strings such as this into a form that can be read by another application.

"2JC","0278294","JP Morgan Life UK Liquidity 3",E

I need to get rid of the quotes characters but keep the data separated by the commas.

As I open the txt file into Excel, the Text Import Wizard appears and recognizes the file as Delimited.

I follow the steps and, at Step 2, I choose Comma as the delimiter.

However, when I come to save the file as a TXT, the commas that separated the data have gone. (The Save As dialogue box only offers Text (Tab Delimited) as an option for Text files.)

The application that I'm going to import into needs those commas to separate the various items of data.

Is there a way to retain (or restore) the commas?

Thanks.


Is there a way for a VBA script to reference a seperate file and return that files last saved time as a string?

I know how to check the last saved time of an active workbook but how can a dirfferent workbook that is not opened be checked?

If it helps I am only checking other .xlsm files nothing else.

I was using something like this but it only checks the active workbook:

Code:

 
Dim FileDate As String
FileDate = WorksheetFunction.Text(ThisWorkbook.BuiltinDocumentProperties("last save time").Value, "mm/dd/yyyy")





Using some sample VB Script code, I've come up with a script that will pull
some data and print out the resulting workbook. As odd as this as this may
or may not sound, I don't want or need the workbook to be saved for archival
purposes. The long term goal is to put this script on a schedule as we need
a report to print every 30 minutes or so. Problem: everytime the script
closes the workbook and excel, Excel prompts the user to save the file.
This is not acceptable if we want this to run in an automated. How can I
get this prompt to go away? I'm pretty sure Excel is generating the prompt.
Seems to me there is a setting in there somewhere to disable this prompt but
I can't find it. Any thoughts would be appreciated. I doubt anyone would
need to see the script but here it's pasted below. I just had a thought: I
wonder if I set the excel application to nothing without closing the
workbook and Excel if that would do what I want? Hmmm... This script is
run by using cscript from the command prompt. As an aside since I'm asking
questions: how is it possible to save the data in a Comma Seperated File
(CSV)? Thanks in advance for any suggestions!

Dim oExcel
Dim strFileName
strFileName = "test.xls"
Dim strNewName

'--Find the current date and time so this info can be appended to the file
name when
'--the excel sheet is saved
strNewName = Year(Now()) & "-" & Month(Now()) & "-" & Day(Now()) & " "
strNewName = strNewName & Hour(Now()) & "." & Minute(Now()) & " " &
strFileName

'--Start Excel and run it invisibly
Set oExcel = CreateObject("Excel.Application")
oExcel.Visible = False

'--Open a workbook preconfigured with ActiveFactory Workbook functions.
'--using "oExcel.Workbooks.Add [Path to file]"
'--Note: The act of opening the workbook will cause the functions to update
oExcel.Workbooks.Add "D:\script\test.xls"

'--Print the file
oExcel.Workbooks(1).Printout

'--Save the workbook in htm format (44), commented out for now
'----oExcel.Workbooks(1).SaveAs "D:\script\save\" & strNewName, 44

'--Close the workbook and Excel
oExcel.Workbooks.Close
oExcel.Quit
Set oExcel = Nothing


Chris Smith





Hi,

i'm trying to loop through a folder of csv files, import them each into a separate workbook, and save as xls files into a different folder using the contents of a particular cell as the name of the xls.

i've found code for loops, but i don't have the expertise to customize for cvs files. and the save-as has been giving me fits.

any help would be appreciated!


An IT expert converted all of my Macintosh Clarisworks files to Excel/Word
and burned them on a **. I transfered the folders/files to my new IBM PC hard
drive and then opened them. I made some changes to a file but I am unable to
save them. I keep getting a "read only" message. I have checked Excel help
and followed the directions, but I get the same "read only" answer. How can I
open these files, change them, and then save the new files? Thanks.



I find the script below online which will save a xls file as a csv file.
However, my file has Japanese and Chinese Character and all that Character got lost. Right now we have to do it manually on 10 to 15 files to get this process done. We would open the xls file save it as uicode text file on excel and then open up notepad save the unicode text file as UTF8 file. So is there a way (perhaps similar to the idea as the script below I can save it as a UTF8 tab delimited file??)

if WScript.Arguments.Count < 2 Then
WScript.Echo "Error! Please specify the source path and the destination. Usage: XlsToCsv SourcePath.xls Destination.csv"
Wscript.Quit
End If
Dim oExcel
Set oExcel = CreateObject("Excel.Application")
Dim oBook
Set oBook = oExcel.Workbooks.Open(Wscript.Arguments.Item(0))
oBook.SaveAs WScript.Arguments.Item(1), 6
oBook.Close False
oExcel.Quit
WScript.Echo "Done"


Hi.

I'm terrible with creating macros, so I figured I'll ask you wizards for help. The actual file what I'm using is about 4mb, so I'll try to explain without uploading it, hope this is ok!

I have a NHL player data .xlsx, from where I'd need to export players info to separate text-files. I have one cell in each row containing all the needed info from the specific row (e.g. =A1&CHAR(10)&B1&CHAR(10)&...ect). So I would also need the macro to understand those line changes for the text file.

The cells I'd like to export are in BA4:BA934 and I'd like each text file named with the player name in cells J4:J934. Basically this would save me doing 1000 files separately, so any help is really appreciated!


Hello,

I am trying to drag a bunch of text files into an Excel workbook. I would like each text file to be its own worksheet inside a single workbook. When I click and drag files over into Excel, it opens a new workbook for each file. Is there a way to drag them into a single workbook as separate sheets rather than individual workbooks?

Thanks,