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

On Jun 18, 8:26*pm, Joel wrote:
What was wrong with the code on Friday?

see posting:http://www.microsoft.com/office/comm....mspx?author=b...



" wrote:
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- Hide quoted text -

- Show quoted text -

Hi Joel,
Re code on Friday, the main problem is that I need to enter the month
and year for each workbook and it does not save each one. it only
saves one file as MonYr. I will study vba code more and see if I can
figure it out and modify it as my needs change. Thanks
Bob

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

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.


" wrote:

On Jun 18, 8:26 pm, Joel wrote:
What was wrong with the code on Friday?

see posting:http://www.microsoft.com/office/comm....mspx?author=b...



" wrote:
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- Hide quoted text -

- Show quoted text -

Hi Joel,
Re code on Friday, the main problem is that I need to enter the month
and year for each workbook and it does not save each one. it only
saves one file as MonYr. I will study vba code more and see if I can
figure it out and modify it as my needs change. Thanks
Bob

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

On Jun 18, 9:50*pm, Joel wrote:
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.



" wrote:
On Jun 18, 8:26 pm, Joel wrote:
What was wrong with the code on Friday?

see posting:http://www.microsoft.com/office/comm....mspx?author=b...

" wrote:
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- Hide quoted text -

- Show quoted text -

Hi Joel,
Re code on Friday, the main problem is that I need to enter the month
and year for each workbook and it does not save each one. *it only
saves one file as MonYr. *I will study vba code more and see if I can
figure it out and modify it as my needs change. *Thanks
Bob- Hide quoted text -

- 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

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

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


" wrote:

On Jun 18, 9:50 pm, Joel wrote:
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.



" wrote:
On Jun 18, 8:26 pm, Joel wrote:
What was wrong with the code on Friday?

see posting:http://www.microsoft.com/office/comm....mspx?author=b...

" wrote:
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- Hide quoted text -

- Show quoted text -

Hi Joel,
Re code on Friday, the main problem is that I need to enter the month
and year for each workbook and it does not save each one. it only
saves one file as MonYr. I will study vba code more and see if I can
figure it out and modify it as my needs change. Thanks
Bob- Hide quoted text -

- 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

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

On Jun 19, 8:15*pm, Joel wrote:
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



" wrote:
On Jun 18, 9:50 pm, Joel wrote:
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.

" wrote:
On Jun 18, 8:26 pm, Joel wrote:
What was wrong with the code on Friday?

see posting:http://www.microsoft.com/office/comm....mspx?author=b...

" wrote:
I posted a similiar problem with mixed success.
I am trying to write vba code toupdateallworkbooksin 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 allworkbooksin 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 allworkbookswith 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 originalworkbooksshould remain unchanged in the folder Month.
Thank you- Hide quoted text -

- Show quoted text -

Hi Joel,
Re code on Friday, the main problem is that I need to enter the month
and year for each workbook and it does not save each one. *it only
saves one file as MonYr. *I will study vba code more and see if I can
figure it out and modify it as my needs change. *Thanks
Bob- Hide quoted text -

- Show quoted text -

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

- 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