Update workbooks in folder, new name and save in new folder

B

borisg5

I posted a similiar problem with mixed success.
I am trying to write vba code to update all workbooks in a folder with
a new month & year and give them a new name and save them in a new
folder. I need it to:
1. Open all workbooks in a folder (c:/Month) (there are 30 with the
file names of name1, name2, name3 etc). There are 32 sheets in each
folder. On sheet 1, cell S1 is the month and cell T1 is the year.
2. Unprotect sheet 1 on every workbook to allow the month and year to
change. The password is "top"
3. Have an input box that prompts for new month (ie. mmm) once
4. Have an input box that prompts for new year (ie. yy) once
5. The contents of these input boxes need to be entered once only and
apply to every workbook in the folder.
6. Reprotect sheet 1 in all workbooks with the password "top".
7. Input box that asks for name of new folder. Create a new folder
off c drive (eg. c:/new)
8. Save all files in new folder as name1MonYr, name2MonYr, name3MonYr
etc to name30MonYr.
The original workbooks should remain unchanged in the folder Month.
Thank you
 
J

Joel

I think this line need to be changed

from
BaseName = Left(bk.Name, InStr(bk.Name, "_"))
to
BaseName = Left(bk.Name, InStr(bk.Name, "."))

This is suppose to remove the ".xls" from the end of the string so the year
and month can be added.
 
B

borisg5

I think this line need to be changed

from
BaseName = Left(bk.Name, InStr(bk.Name, "_"))
to
BaseName = Left(bk.Name, InStr(bk.Name, "."))

This is suppose to remove the ".xls" from the end of the string so the year
and month can be added.






- Show quoted text -

Thanks Joel,
It now saves the file name correctly. I am using the same month and
year to update for every workbook. Is there a way that I only have to
enter it once, an it is applied to all workbooks. The code now asks
me to input the month & yr for each workbook.
Regards
Bob
 
J

Joel

Just move the Inputbox line to the top of the code outside the DO LOOP.

Sub UpdateFiles()

Folder = "C:\Months\"

InMonth = InputBox("Enter Month (MMM) : ")
InYear = InputBox("Enter Year (YY) : ")

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

With bk.Sheets(1)
.Unprotect Password:="top"

.Range("S1") = InMonth
.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"

bk.SaveAs Filename:=Folder & NewName
ActiveWorkbook.Close

FName = Dir()
Loop

End Sub
 
B

borisg5

Just move the Inputbox line to the top of the code outside the DO LOOP.

Sub UpdateFiles()

Folder = "C:\Months\"

InMonth = InputBox("Enter Month (MMM) : ")
InYear = InputBox("Enter Year (YY) : ")

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

   With bk.Sheets(1)
      .Unprotect Password:="top"

      .Range("S1") = InMonth
      .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"

   bk.SaveAs Filename:=Folder & NewName
   ActiveWorkbook.Close

   FName = Dir()
Loop

End Sub






- Show quoted text -

Joel,
Thank you so much. It works beautifully. I did get a compile error. I
deleted 'option explicit' and it works fine.
Regards
Bob
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top