Hi guys,

Can I go through (1 by 1) a series of workbooks in the same folder just as
I can go through a series of worksheets one by one in a workbook.

I have a spreadsheet for every day (about 6 months worth) and each
individual workbook is named something like thur-13-apr.

I need to open each in turn and do some changes to them all. The changes
can be done with vba as they are all formatted the same inside but I want
to open each with a macro (if possible) and then close when the work is
done.

I don't want to have to open each manually if possible.

Is this possible?

Ron

You'll have to open each workbook in order to make changes. Use
code like

Dim FName As Variant
Dim WB As Workbook
ChDrive "H:" '<<< CHANGE
ChDir "H:\Test" '<<< CHANGE
FName = Dir("*.xls")
Do Until FName = ""
Set WB = Workbooks.Open(FName)
Debug.Print WB.Name
' work with WB
WB.Close savechanges:=True
FName = Dir()
Loop


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

"Ron" wrote in message
...
Hi guys,

Can I go through (1 by 1) a series of workbooks in the same
folder just as
I can go through a series of worksheets one by one in a
workbook.

I have a spreadsheet for every day (about 6 months worth) and
each
individual workbook is named something like thur-13-apr.

I need to open each in turn and do some changes to them all.
The changes
can be done with vba as they are all formatted the same inside
but I want
to open each with a macro (if possible) and then close when the
work is
done.

I don't want to have to open each manually if possible.

Is this possible?

Ron

Dim sName as String, v as Variant
Dim bOK as Boolean, i as Long
Dim bk as Workbook
sName = Dir("C:\Myfiles\" & "*.xls")
v = Array("Jan","Feb","Mar","Apr","May","Jun", _
"Jul","Aug","Sep","Oct","Nov",Dec")
do while sname < ""
bOK = False
for i = lbound(v) to ubound(v)
if Instr(1,sName,v(i) & "-",vbTextCompare) 0 then
bOK = True
exit for
end if
Next
if bOk then
set bk = workbooks.Open("C:\Myfiles\" & sName)
' process bk
bk.Close Savechanges:=True
end if
sName = Dir()
Loop


--
Regards,
Tom Ogilvy

"Ron" wrote:

Hi guys,

Can I go through (1 by 1) a series of workbooks in the same folder just as
I can go through a series of worksheets one by one in a workbook.

I have a spreadsheet for every day (about 6 months worth) and each
individual workbook is named something like thur-13-apr.

I need to open each in turn and do some changes to them all. The changes
can be done with vba as they are all formatted the same inside but I want
to open each with a macro (if possible) and then close when the work is
done.

I don't want to have to open each manually if possible.

Is this possible?

Ron

Thanks Chip & Tom. I appreciate the swift response.

Ron