Hi all

I don't have a great deal of experience with VBA, but can usually muddle my way through. I have drawn a blank with the following, however.

I have a folder containing multiple excel workbooks, all of which are of the same structure. The workbooks contain multiple work sheets. I want to write a macro that saves each worksheet within each workbook as a separate .csv file that is named "NameofWorkBook_NameofWorkSheet". These would all be saved into the existing folder.

I have been reading threads on similar topics and trying to meld them together, but the best I have been able to do is save each worksheet within a single workbook as the name of the worksheet. I am using Excel 2010. Any assistance with sample code, or directing me to where this has previously been addressed would be most appreciated.

Thanks in advance...

This code OK in 2007 for one workbook only.

You will have to add a loop to go through all workbooks in the folder.

Sub Make_New_Books()
Dim w As Worksheet
Application.ScreenUpdating = False
Application.DisplayAlerts = False
FileExtStr = ".csv": FileFormatNum = 6
For Each w In ActiveWorkbook.Worksheets
w.Copy
With ActiveWorkbook
.SaveAs Filename:=ThisWorkbook.Path & "\" & ThisWorkbook.Name _
& "_" & w.Name & FileExtStr
.Close
End With
Next w
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub


Gord

On Sat, 5 May 2012 11:24:31 +0000, KQBats
wrote:


Hi all

I don't have a great deal of experience with VBA, but can usually muddle
my way through. I have drawn a blank with the following, however.

I have a folder containing multiple excel workbooks, all of which are of
the same structure. The workbooks contain multiple work sheets. I want
to write a macro that saves each worksheet within each workbook as a
separate .csv file that is named "NameofWorkBook_NameofWorkSheet". These
would all be saved into the existing folder.

I have been reading threads on similar topics and trying to meld them
together, but the best I have been able to do is save each worksheet
within a single workbook as the name of the worksheet. I am using Excel
2010. Any assistance with sample code, or directing me to where this has
previously been addressed would be most appreciated.

Thanks in advance...

Gord - Thanks, that works well. I will pop it into a loop and, hopefully, get the whole lot done in one hit. Really appreciate your time.

Cheers

Ken

OK, I am nearly there. All of the files are being produced for all the workbooks, but I am not getting the name of the file correct when I run the code from within the loop to run the saving in batch mode, and it is saving the csv files back to the "C:\" directory, rather than the one in which the files are sitting "C:\Datafiles". The code below is saving the files as Book1_WorksheetName.csv through to Book(number of worksheets in all the workbooks)_WorksheetName.csv.

Here is the code(Gord's first, followed by the loop that calls it). After I run this I run 'save all' and 'close all' macros. I am not quite getting the 'ActiveWorkBook' and 'ThisWorkBook' elements right, and have been playing around with these trying to get it to work.


Sub Make_New_Books()
Dim w As Worksheet
Application.ScreenUpdating = False
Application.DisplayAlerts = False
FileExtStr = ".csv": FileFormatNum = 6
For Each w In ActiveWorkbook.Worksheets
w.Copy
With ActiveWorkbook
.SaveAs Filename:=ActiveWorkbook.Path & "\" & ActiveWorkbook.Name _
& "_" & w.Name & FileExtStr
.Close
End With
Next w
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

Sub AllFolderFiles()
Dim WB As Workbook
Dim TheFile As String
Dim MyPath As String
MyPath = "C:\DataFiles"
ChDir MyPath
TheFile = Dir("*.xls")
Do While TheFile < ""
Set WB = Workbooks.Open(MyPath & "\" & TheFile)
Call Make_New_Books

TheFile = Dir
Loop
End Sub

You altered my original code.............

SaveAs Filename:=ActiveWorkbook.Path & "\" & ActiveWorkbook.Name _
& "_" & w.Name & FileExtStr

ActiveWorkbook is the one that was just created and has no name or
path.

My code was..........

SaveAs Filename:=ThisWorkbook.Path & "\" & ThisWorkbook.Name _
& "_" & w.Name & FileExtStr


Gord

On Sun, 6 May 2012 02:37:21 +0000, KQBats
wrote:


OK, I am nearly there. All of the files are being produced for all the
workbooks, but I am not getting the name of the file correct when I run
the code from within the loop to run the saving in batch mode, and it is
saving the csv files back to the "C:\" directory, rather than the one in
which the files are sitting "C:\Datafiles". The code below is saving the
files as Book1_WorksheetName.csv through to Book(number of worksheets in
all the workbooks)_WorksheetName.csv.

Here is the code(Gord's first, followed by the loop that calls it).
After I run this I run 'save all' and 'close all' macros. I am not quite
getting the 'ActiveWorkBook' and 'ThisWorkBook' elements right, and have
been playing around with these trying to get it to work.


Sub Make_New_Books()
Dim w As Worksheet
Application.ScreenUpdating = False
Application.DisplayAlerts = False
FileExtStr = ".csv": FileFormatNum = 6
For Each w In ActiveWorkbook.Worksheets
w.Copy
With ActiveWorkbook
SaveAs Filename:=ActiveWorkbook.Path & "\" & ActiveWorkbook.Name _
& "_" & w.Name & FileExtStr
Close
End With
Next w
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

Sub AllFolderFiles()
Dim WB As Workbook
Dim TheFile As String
Dim MyPath As String
MyPath = "C:\DataFiles"
ChDir MyPath
TheFile = Dir("*.xls")
Do While TheFile < ""
Set WB = Workbooks.Open(MyPath & "\" & TheFile)
Call Make_New_Books

TheFile = Dir
Loop
End Sub




KQBats;1601529 Wrote:
Gord - Thanks, that works well. I will pop it into a loop and,
hopefully, get the whole lot done in one hit. Really appreciate your
time.

Cheers

Ken

Gord

Yes, I did because I wasn't sure about how to have the correct workbook referenced as the loop calls the "Make_New_Books" code. The code you posted works without the loop if the macro is run from within the workbook that I am wishing to break into the sheets and save, but it doesn't correctly name them if I open a workbook with the "AllFolderFiles" macro in it, and call the "Make_New_Books" code from within that Macro.

The "AllFolderFiles" macro contains a loop to move through all the files in the folder, but it is not passing the name of the current file that it is working on to the "Make_New_Books" macro when it saves the files...at least I think that is the case.

Cheers

Ken