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