Opening a list of workbooks with vba, similar to going through sheets in a workbook?
 

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

Opening a list of workbooks with vba, similar to going through sheets in a workbook?
 

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

Opening a list of workbooks with vba, similar to going through she
 

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

Opening a list of workbooks with vba, similar to going through she
 

Thanks Chip & Tom. I appreciate the swift response.

Ron