Batch Open and Save

[James]

Hi
I have a really simple need: some code to open all xls files in a
specified directory and then save the files with a modified name (e.g.
just add "_fm" to filename)to a new subdir. In between the open and
save I will insert some code to do some simple formatting but really
would appreciate some help in opening a bunch of files and then saving
them.
Thanks
James

 

[Tom Ogilvy]

dim spath1 as string, spath2 as string, fname as string
Dim fname1 as string, wkbk as workbook
spath1 = "C:\Myfiles\"
sPath2 = "C:\MyfilesNew\"

fname = dir(sPath1 & "*.xls")
do while fname <> ""
set wkbk = workbooks.Open(fname)
' modify workbook, next line is an example
wkbk.Worksheets(1).Range("A1").Numberformat = "#,##0.0"
' no build the new name
fname1 = wkbk.name
fname1 = left(fName1,len(fname1)-4)
fname1 = fname1 & "_fm.xls"
wkbk.SaveAs sPath2 & fname1
wkbk.close SaveChanges:=False
fName = dir()
Loop

code is untested and may contain typos.