I have a folder called month. It contains 20 workbooks which contain
32 sheets (all have the same structure).
The filenames are in the format of nameA_Month_Year

I would like vba code to
- open every workbook automatically
- unprotect the first sheet of each workbook
- have an input box that allows me to enter the first 3 letters of the
new month I wish to update to (eg. "Jul")
- the month that was entered is placed into cell S1 of the first sheet
of every workbook
- have an input box that allows me to enter the last 2 digits of the
year I wish to update to (eg. "08")
- the year that was entered is placed into cell T1 of the first sheet
of every workbook
- reprotect the first sheet of each workbook using the same password
as to unprotect.
- override the orginal names of each workbook to update them to
include the month and year that was entered.
The end result would be nameA_Jul_08 which would override
nameA_Month_Year file.
The password for the 1st sheet is "top". All subsequent sheets are
linked to sheet 1 and do not need to be modified.
Thank you
Bob

Change the Path of Folder as required.

Sub UpdateFiles()

Folder = "C:\Months\"

FName = Dir(Folder & "*.xls")
Do While FName < ""
bk = Workbooks.Open(Filename:=Folder & FName)

With bk.Sheets(1)
.Unprotect Password:="top"
InMonth = InputBox("Enter Month (MMM) : ")
.Range("S1") = InMonth
InYear = InputBox("Enter Year (YY) : ")
.Range("T1") = InYear
.Protect Password:="top"
End With

'get base name of file
BaseName = Left(bk.Name, InStr(bk.Name, "_"))
NewName = BaseName & InMonth & "_" & InYear & ".xls"

Set newbk = bk.SaveAs(Filename:=Folder & NewName)
newbk.Close

FName = Dir()
Loop

End Sub

" wrote:

I have a folder called month. It contains 20 workbooks which contain
32 sheets (all have the same structure).
The filenames are in the format of nameA_Month_Year

I would like vba code to
- open every workbook automatically
- unprotect the first sheet of each workbook
- have an input box that allows me to enter the first 3 letters of the
new month I wish to update to (eg. "Jul")
- the month that was entered is placed into cell S1 of the first sheet
of every workbook
- have an input box that allows me to enter the last 2 digits of the
year I wish to update to (eg. "08")
- the year that was entered is placed into cell T1 of the first sheet
of every workbook
- reprotect the first sheet of each workbook using the same password
as to unprotect.
- override the orginal names of each workbook to update them to
include the month and year that was entered.
The end result would be nameA_Jul_08 which would override
nameA_Month_Year file.
The password for the 1st sheet is "top". All subsequent sheets are
linked to sheet 1 and do not need to be modified.
Thank you
Bob

On Jun 12, 2:18*am, Joel wrote:
Change the Path of Folder as required.

Sub UpdateFiles()

Folder = "C:\Months\"

FName = Dir(Folder & "*.xls")
Do While FName < ""
* *bk = Workbooks.Open(Filename:=Folder & FName)

* *With bk.Sheets(1)
* * * .Unprotect Password:="top"
* * * InMonth = InputBox("Enter Month (MMM) : ")
* * * .Range("S1") = InMonth
* * * InYear = InputBox("Enter Year (YY) : ")
* * * .Range("T1") = InYear
* * * .Protect Password:="top"
* *End With

* *'get base name of file
* *BaseName = Left(bk.Name, InStr(bk.Name, "_"))
* *NewName = BaseName & InMonth & "_" & InYear & ".xls"

* *Set newbk = bk.SaveAs(Filename:=Folder & NewName)
* *newbk.Close

* *FName = Dir()
Loop

End Sub



" wrote:
I have a folder called month. *It contains 20 workbooks which contain
32 sheets (all have the same structure).
The filenames are in the format of nameA_Month_Year

I would like vba code to
- open every workbook automatically
- unprotect the first sheet of each workbook
- have an input box that allows me to enter the first 3 letters of the
new month I wish to update to (eg. *"Jul")
- the month that was entered is placed into cell S1 of the first sheet
of every workbook
- have an input box that allows me to enter the last 2 digits of the
year I wish to update to (eg. *"08")
- the year that was entered is placed into cell T1 of the first sheet
of every workbook
- reprotect the first sheet of each workbook using the same password
as to unprotect.
- override the orginal names of each workbook to update them to
include the month and year that was entered.
The end result would be nameA_Jul_08 which would override
nameA_Month_Year file.
The password for the 1st sheet is "top". *All subsequent sheets are
linked to sheet 1 and do not need to be modified.
Thank you
Bob- Hide quoted text -

- Show quoted text -

Thanks Joel,
I get a Run time error 438 - object does not suppport this property or
method. on the line bk = Workbooks.Open(Filename:=Folder & FName)
Any ideas?
Thanks
Bob

Simple, I lerft out the word set

from
bk = Workbooks.Open(Filename:=Folder & FName)
to
set bk = Workbooks.Open(Filename:=Folder & FName)


" wrote:

On Jun 12, 2:18 am, Joel wrote:
Change the Path of Folder as required.

Sub UpdateFiles()

Folder = "C:\Months\"

FName = Dir(Folder & "*.xls")
Do While FName < ""
bk = Workbooks.Open(Filename:=Folder & FName)

With bk.Sheets(1)
.Unprotect Password:="top"
InMonth = InputBox("Enter Month (MMM) : ")
.Range("S1") = InMonth
InYear = InputBox("Enter Year (YY) : ")
.Range("T1") = InYear
.Protect Password:="top"
End With

'get base name of file
BaseName = Left(bk.Name, InStr(bk.Name, "_"))
NewName = BaseName & InMonth & "_" & InYear & ".xls"

Set newbk = bk.SaveAs(Filename:=Folder & NewName)
newbk.Close

FName = Dir()
Loop

End Sub



" wrote:
I have a folder called month. It contains 20 workbooks which contain
32 sheets (all have the same structure).
The filenames are in the format of nameA_Month_Year

I would like vba code to
- open every workbook automatically
- unprotect the first sheet of each workbook
- have an input box that allows me to enter the first 3 letters of the
new month I wish to update to (eg. "Jul")
- the month that was entered is placed into cell S1 of the first sheet
of every workbook
- have an input box that allows me to enter the last 2 digits of the
year I wish to update to (eg. "08")
- the year that was entered is placed into cell T1 of the first sheet
of every workbook
- reprotect the first sheet of each workbook using the same password
as to unprotect.
- override the orginal names of each workbook to update them to
include the month and year that was entered.
The end result would be nameA_Jul_08 which would override
nameA_Month_Year file.
The password for the 1st sheet is "top". All subsequent sheets are
linked to sheet 1 and do not need to be modified.
Thank you
Bob- Hide quoted text -

- Show quoted text -

Thanks Joel,
I get a Run time error 438 - object does not suppport this property or
method. on the line bk = Workbooks.Open(Filename:=Folder & FName)
Any ideas?
Thanks
Bob

On Jun 12, 10:11*am, Joel wrote:
Simple, I lerft out the word set

from
bk = Workbooks.Open(Filename:=Folder & FName)
to
set bk = Workbooks.Open(Filename:=Folder & FName)



" wrote:
On Jun 12, 2:18 am, Joel wrote:
Change the Path of Folder as required.

Sub UpdateFiles()

Folder = "C:\Months\"

FName = Dir(Folder & "*.xls")
Do While FName < ""
* *bk = Workbooks.Open(Filename:=Folder & FName)

* *With bk.Sheets(1)
* * * .Unprotect Password:="top"
* * * InMonth = InputBox("Enter Month (MMM) : ")
* * * .Range("S1") = InMonth
* * * InYear = InputBox("Enter Year (YY) : ")
* * * .Range("T1") = InYear
* * * .Protect Password:="top"
* *End With

* *'get base name of file
* *BaseName = Left(bk.Name, InStr(bk.Name, "_"))
* *NewName = BaseName & InMonth & "_" & InYear & ".xls"

* *Set newbk = bk.SaveAs(Filename:=Folder & NewName)
* *newbk.Close

* *FName = Dir()
Loop

End Sub

" wrote:
I have a folder called month. *It contains 20 workbooks which contain
32 sheets (all have the same structure).
The filenames are in the format of nameA_Month_Year

I would like vba code to
- open every workbook automatically
- unprotect the first sheet of each workbook
- have an input box that allows me to enter the first 3 letters of the
new month I wish to update to (eg. *"Jul")
- the month that was entered is placed into cell S1 of the first sheet
of every workbook
- have an input box that allows me to enter the last 2 digits of the
year I wish to update to (eg. *"08")
- the year that was entered is placed into cell T1 of the first sheet
of every workbook
- reprotect the first sheet of each workbook using the same password
as to unprotect.
- override the orginal names of each workbook to update them to
include the month and year that was entered.
The end result would be nameA_Jul_08 which would override
nameA_Month_Year file.
The password for the 1st sheet is "top". *All subsequent sheets are
linked to sheet 1 and do not need to be modified.
Thank you
Bob- Hide quoted text -

- Show quoted text -

Thanks Joel,
I get a Run time error 438 - object does not suppport this property or
method. *on the line bk = Workbooks.Open(Filename:=Folder & FName)
Any ideas?
Thanks
Bob- Hide quoted text -

- Show quoted text -

Thanks Joel, that helps get further.
I am now getting a run time error 13 type mismatch error on the Set
newbk = bk.SaveAs(Filename:=Folder & NewName) line.
The result is that only one (the first) workbook is opened and a new
file called Jul_08 is created (but the preceding name is not included
and it hasn't looped through the other 19 workbooks). Your help is
valued.
regards
Bob

from
Set newbk = bk.SaveAs(Filename:=Folder & NewName)
newbk.Close

to
bk.SaveAs(Filename:=Folder & NewName)
ActiveWorkbook.Close

" wrote:

On Jun 12, 10:11 am, Joel wrote:
Simple, I lerft out the word set

from
bk = Workbooks.Open(Filename:=Folder & FName)
to
set bk = Workbooks.Open(Filename:=Folder & FName)



" wrote:
On Jun 12, 2:18 am, Joel wrote:
Change the Path of Folder as required.

Sub UpdateFiles()

Folder = "C:\Months\"

FName = Dir(Folder & "*.xls")
Do While FName < ""
bk = Workbooks.Open(Filename:=Folder & FName)

With bk.Sheets(1)
.Unprotect Password:="top"
InMonth = InputBox("Enter Month (MMM) : ")
.Range("S1") = InMonth
InYear = InputBox("Enter Year (YY) : ")
.Range("T1") = InYear
.Protect Password:="top"
End With

'get base name of file
BaseName = Left(bk.Name, InStr(bk.Name, "_"))
NewName = BaseName & InMonth & "_" & InYear & ".xls"

Set newbk = bk.SaveAs(Filename:=Folder & NewName)
newbk.Close

FName = Dir()
Loop

End Sub

" wrote:
I have a folder called month. It contains 20 workbooks which contain
32 sheets (all have the same structure).
The filenames are in the format of nameA_Month_Year

I would like vba code to
- open every workbook automatically
- unprotect the first sheet of each workbook
- have an input box that allows me to enter the first 3 letters of the
new month I wish to update to (eg. "Jul")
- the month that was entered is placed into cell S1 of the first sheet
of every workbook
- have an input box that allows me to enter the last 2 digits of the
year I wish to update to (eg. "08")
- the year that was entered is placed into cell T1 of the first sheet
of every workbook
- reprotect the first sheet of each workbook using the same password
as to unprotect.
- override the orginal names of each workbook to update them to
include the month and year that was entered.
The end result would be nameA_Jul_08 which would override
nameA_Month_Year file.
The password for the 1st sheet is "top". All subsequent sheets are
linked to sheet 1 and do not need to be modified.
Thank you
Bob- Hide quoted text -

- Show quoted text -

Thanks Joel,
I get a Run time error 438 - object does not suppport this property or
method. on the line bk = Workbooks.Open(Filename:=Folder & FName)
Any ideas?
Thanks
Bob- Hide quoted text -

- Show quoted text -

Thanks Joel, that helps get further.
I am now getting a run time error 13 type mismatch error on the Set
newbk = bk.SaveAs(Filename:=Folder & NewName) line.
The result is that only one (the first) workbook is opened and a new
file called Jul_08 is created (but the preceding name is not included
and it hasn't looped through the other 19 workbooks). Your help is
valued.
regards
Bob

On Jun 12, 8:16*pm, Joel wrote:
from
* Set newbk = bk.SaveAs(Filename:=Folder & NewName)
* *newbk.Close

to
* *bk.SaveAs(Filename:=Folder & NewName)
* *ActiveWorkbook.Close



" wrote:
On Jun 12, 10:11 am, Joel wrote:
Simple, I lerft out the word set

from
bk = Workbooks.Open(Filename:=Folder & FName)
to
set bk = Workbooks.Open(Filename:=Folder & FName)

" wrote:
On Jun 12, 2:18 am, Joel wrote:
Change the Path of Folder as required.

Sub UpdateFiles()

Folder = "C:\Months\"

FName = Dir(Folder & "*.xls")
Do While FName < ""
* *bk = Workbooks.Open(Filename:=Folder & FName)

* *With bk.Sheets(1)
* * * .UnprotectPassword:="top"
* * * InMonth = InputBox("Enter Month (MMM) : ")
* * * .Range("S1") = InMonth
* * * InYear = InputBox("Enter Year (YY) : ")
* * * .Range("T1") = InYear
* * * .Protect Password:="top"
* *End With

* *'get base name of file
* *BaseName = Left(bk.Name, InStr(bk.Name, "_"))
* *NewName = BaseName & InMonth & "_" & InYear & ".xls"

* *Set newbk = bk.SaveAs(Filename:=Folder & NewName)
* *newbk.Close

* *FName = Dir()
Loop

End Sub

" wrote:
I have a folder called month. *It contains 20 workbooks which contain
32 sheets (all have the same structure).
The filenames are in the format of nameA_Month_Year

I would like vba code to
- open every workbook automatically
-unprotectthe first sheet of each workbook
- have an input box that allows me to enter the first 3 letters of the
new month I wish to update to (eg. *"Jul")
- the month that was entered is placed into cell S1 of the first sheet
of every workbook
- have an input box that allows me to enter the last 2 digits of the
year I wish to update to (eg. *"08")
- the year that was entered is placed into cell T1 of the first sheet
of every workbook
- reprotect the first sheet of each workbook using the same password
as tounprotect.
- override the orginal names of each workbook to update them to
include the month and year that was entered.
The end result would be nameA_Jul_08 which would override
nameA_Month_Year file.
The password for the 1st sheet is "top". *All subsequent sheets are
linked to sheet 1 and do not need to be modified.
Thank you
Bob- Hide quoted text -

- Show quoted text -

Thanks Joel,
I get a Run time error 438 - object does not suppport this property or
method. *on the line bk = Workbooks.Open(Filename:=Folder & FName)
Any ideas?
Thanks
Bob- Hide quoted text -

- Show quoted text -

Thanks Joel, that helps get further.
I am now getting a run time error 13 type mismatch error on the *Set
newbk = bk.SaveAs(Filename:=Folder & NewName) line.
The result is that only one (the first) workbook is opened and a new
file called Jul_08 is created (but the preceding name is not included
and it hasn't looped through the other 19 workbooks). *Your help is
valued.
regards
Bob- Hide quoted text -

- Show quoted text -

Thanks but I get a compile syntax error and I don't know enough about
it to correc the syntax

I forgot to compile the code to check for errors. The parenthesis need to be
removed. You need the parethesis when you have "set a =" in front of an
instruction and don't need the parenthesis otherwise. I forgot to remove the
parenthesis when I removed the equal sign.

from
bk.SaveAs(Filename:=Folder & NewName)

to
bk.SaveAs Filename:=Folder & NewName


" wrote:

On Jun 12, 8:16 pm, Joel wrote:
from
Set newbk = bk.SaveAs(Filename:=Folder & NewName)
newbk.Close

to
bk.SaveAs(Filename:=Folder & NewName)
ActiveWorkbook.Close



" wrote:
On Jun 12, 10:11 am, Joel wrote:
Simple, I lerft out the word set

from
bk = Workbooks.Open(Filename:=Folder & FName)
to
set bk = Workbooks.Open(Filename:=Folder & FName)

" wrote:
On Jun 12, 2:18 am, Joel wrote:
Change the Path of Folder as required.

Sub UpdateFiles()

Folder = "C:\Months\"

FName = Dir(Folder & "*.xls")
Do While FName < ""
bk = Workbooks.Open(Filename:=Folder & FName)

With bk.Sheets(1)
.UnprotectPassword:="top"
InMonth = InputBox("Enter Month (MMM) : ")
.Range("S1") = InMonth
InYear = InputBox("Enter Year (YY) : ")
.Range("T1") = InYear
.Protect Password:="top"
End With

'get base name of file
BaseName = Left(bk.Name, InStr(bk.Name, "_"))
NewName = BaseName & InMonth & "_" & InYear & ".xls"

Set newbk = bk.SaveAs(Filename:=Folder & NewName)
newbk.Close

FName = Dir()
Loop

End Sub

" wrote:
I have a folder called month. It contains 20 workbooks which contain
32 sheets (all have the same structure).
The filenames are in the format of nameA_Month_Year

I would like vba code to
- open every workbook automatically
-unprotectthe first sheet of each workbook
- have an input box that allows me to enter the first 3 letters of the
new month I wish to update to (eg. "Jul")
- the month that was entered is placed into cell S1 of the first sheet
of every workbook
- have an input box that allows me to enter the last 2 digits of the
year I wish to update to (eg. "08")
- the year that was entered is placed into cell T1 of the first sheet
of every workbook
- reprotect the first sheet of each workbook using the same password
as tounprotect.
- override the orginal names of each workbook to update them to
include the month and year that was entered.
The end result would be nameA_Jul_08 which would override
nameA_Month_Year file.
The password for the 1st sheet is "top". All subsequent sheets are
linked to sheet 1 and do not need to be modified.
Thank you
Bob- Hide quoted text -

- Show quoted text -

Thanks Joel,
I get a Run time error 438 - object does not suppport this property or
method. on the line bk = Workbooks.Open(Filename:=Folder & FName)
Any ideas?
Thanks
Bob- Hide quoted text -

- Show quoted text -

Thanks Joel, that helps get further.
I am now getting a run time error 13 type mismatch error on the Set
newbk = bk.SaveAs(Filename:=Folder & NewName) line.
The result is that only one (the first) workbook is opened and a new
file called Jul_08 is created (but the preceding name is not included
and it hasn't looped through the other 19 workbooks). Your help is
valued.
regards
Bob- Hide quoted text -

- Show quoted text -

Thanks but I get a compile syntax error and I don't know enough about
it to correc the syntax

On Jun 12, 8:53*pm, Joel wrote:
I forgot to compile the code to check for errors. *The parenthesis need to be
removed. *You need the parethesis when you have "set a =" in front of an
instruction and don't need the parenthesis otherwise. *I forgot to remove the
parenthesis when I removed the equal sign.

from
bk.SaveAs(Filename:=Folder & NewName)

to
bk.SaveAs Filename:=Folder & NewName



" wrote:
On Jun 12, 8:16 pm, Joel wrote:
from
* Set newbk = bk.SaveAs(Filename:=Folder & NewName)
* *newbk.Close

to
* *bk.SaveAs(Filename:=Folder & NewName)
* *ActiveWorkbook.Close

" wrote:
On Jun 12, 10:11 am, Joel wrote:
Simple, I lerft out the word set

from
bk = Workbooks.Open(Filename:=Folder & FName)
to
set bk = Workbooks.Open(Filename:=Folder & FName)

" wrote:
On Jun 12, 2:18 am, Joel wrote:
Change the Path of Folder as required.

Sub UpdateFiles()

Folder = "C:\Months\"

FName = Dir(Folder & "*.xls")
Do While FName < ""
* *bk = Workbooks.Open(Filename:=Folder & FName)

* *With bk.Sheets(1)
* * * .UnprotectPassword:="top"
* * * InMonth = InputBox("Enter Month (MMM) : ")
* * * .Range("S1") = InMonth
* * * InYear = InputBox("Enter Year (YY) : ")
* * * .Range("T1") = InYear
* * * .Protect Password:="top"
* *End With

* *'get base name of file
* *BaseName = Left(bk.Name, InStr(bk.Name, "_"))
* *NewName = BaseName & InMonth & "_" & InYear & ".xls"

* *Set newbk = bk.SaveAs(Filename:=Folder & NewName)
* *newbk.Close

* *FName = Dir()
Loop

End Sub

" wrote:
I have a folder called month. *It contains 20 workbooks which contain
32 sheets (all have the same structure).
The filenames are in the format of nameA_Month_Year

I would like vba code to
- open every workbook automatically
-unprotectthe first sheet of each workbook
- have an input box that allows me to enter the first 3 letters of the
new month I wish to update to (eg. *"Jul")
- the month that was entered is placed into cell S1 of the first sheet
of every workbook
- have an input box that allows me to enter the last 2 digits of the
year I wish to update to (eg. *"08")
- the year that was entered is placed into cell T1 of the first sheet
of every workbook
- reprotect the first sheet of each workbook using the same password
as tounprotect.
- override the orginal names of each workbook to update them to
include the month and year that was entered.
The end result would be nameA_Jul_08 which would override
nameA_Month_Year file.
The password for the 1st sheet is "top". *All subsequent sheets are
linked to sheet 1 and do not need to be modified.
Thank you
Bob- Hide quoted text -

- Show quoted text -

Thanks Joel,
I get a Run time error 438 - object does not suppport this property or
method. *on the line bk = Workbooks.Open(Filename:=Folder & FName)
Any ideas?
Thanks
Bob- Hide quoted text -

- Show quoted text -

Thanks Joel, that helps get further.
I am now getting a run time error 13 type mismatch error on the *Set
newbk = bk.SaveAs(Filename:=Folder & NewName) line.
The result is that only one (the first) workbook is opened and a new
file called Jul_08 is created (but the preceding name is not included
and it hasn't looped through the other 19 workbooks). *Your help is
valued.
regards
Bob- Hide quoted text -

- Show quoted text -

Thanks but I get a compile syntax error and I don't know enough about
it to correc the syntax- Hide quoted text -

- Show quoted text -

Thanks Joel,
Bob