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