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

[borisg5]

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

 

[Joel]

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

 

[borisg5]

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






- 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

 

[Joel]

Simple, I lerft out the word set

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

 

[borisg5]

Simple, I lerft out the word set

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






- 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

 

[Joel]

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

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

 

[borisg5]

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

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






- Show quoted text -

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

 

[Joel]

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

 

[borisg5]

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






- Show quoted text -

Thanks Joel,
Bob