unprotect 1st sheet in every workbook in a folder and change month inS1 & year in T1 & rename all workbooks
 

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

unprotect 1st sheet in every workbook in a folder and change month
 

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

unprotect 1st sheet in every workbook in a folder and changemonth
 

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

unprotect 1st sheet in every workbook in a folder and change m
 

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

unprotect 1st sheet in every workbook in a folder and change m
 

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

unprotect 1st sheet in every workbook in a folder and change m
 

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

unprotect 1st sheet in every workbook in a folder and change m
 

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

unprotect 1st sheet in every workbook in a folder and change m
 

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

unprotect 1st sheet in every workbook in a folder and change m
 

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