I have a task that involves the following steps.

1. Open Excel file
2. Invoke Macro with customer menu button
3. After macro runs I select File-Save-As...then I change a date that
resides in the filename (looks like this) XXXX_todaysdate).xls
4. Close file (keep Excel open)

I know how to write the macro to save (as is) and close the file but I
don't know how to get it grab todays date and text it into the
filename before saving.

"Ideally"...I'd like to have the Macro rename the file with BOTH
todays date AND text in a fixed cell in the spreadsheet.

Such as: Monthly_Report_010107_.xls (Monthly Report) being the text in
the cell.

I have to run this on multiple files so I'd really like to see how to
automate this part.

Any suggestions you could give me would be appreciated.

Ken

I think this might be what you need:
Sub SaveMyReport()
ActiveWorkbook.SaveAs Filename:="G:\Dokumenter\Documents\" &
Range("A1").Value & Date & ".xls"
End Sub

Watch out for line breaks.

Per Erik

kcleere wrote:
I have a task that involves the following steps.

1. Open Excel file
2. Invoke Macro with customer menu button
3. After macro runs I select File-Save-As...then I change a date that
resides in the filename (looks like this) XXXX_todaysdate).xls
4. Close file (keep Excel open)

I know how to write the macro to save (as is) and close the file but I
don't know how to get it grab todays date and text it into the
filename before saving.

"Ideally"...I'd like to have the Macro rename the file with BOTH
todays date AND text in a fixed cell in the spreadsheet.

Such as: Monthly_Report_010107_.xls (Monthly Report) being the text in
the cell.

I have to run this on multiple files so I'd really like to see how to
automate this part.

Any suggestions you could give me would be appreciated.

Ken

the following will grab the file name from Cell A1 and the date from Cell B1
and concatonate them and you can then use them for the filename. In the
format part set the format within the quotes to any format that you want the
date. consider "yyyymmdd" and they they will always index nicely in the
folder when you are looking for them.

Sub Test_Save()

Dim wbSaveName As Variant

wbSaveName = Sheets("Sheet1").Range("A1") & "_" _
& Format(Sheets("Sheet1").Range("B1"), "ddmmyyyy") _
& ".xls"

ActiveWorkbook.SaveAs Filename:=wbSaveName

End Sub
Regards,

OssieMac

"kcleere" wrote:

I have a task that involves the following steps.

1. Open Excel file
2. Invoke Macro with customer menu button
3. After macro runs I select File-Save-As...then I change a date that
resides in the filename (looks like this) XXXX_todaysdate).xls
4. Close file (keep Excel open)

I know how to write the macro to save (as is) and close the file but I
don't know how to get it grab todays date and text it into the
filename before saving.

"Ideally"...I'd like to have the Macro rename the file with BOTH
todays date AND text in a fixed cell in the spreadsheet.

Such as: Monthly_Report_010107_.xls (Monthly Report) being the text in
the cell.

I have to run this on multiple files so I'd really like to see how to
automate this part.

Any suggestions you could give me would be appreciated.

Ken

On Apr 5, 6:50 pm, OssieMac
wrote:
the following will grab the file name from Cell A1 and the date from Cell B1
and concatonate them and you can then use them for the filename. In the
format part set the format within the quotes to any format that you want the
date. consider "yyyymmdd" and they they will always index nicely in the
folder when you are looking for them.

Sub Test_Save()

Dim wbSaveName As Variant

wbSaveName = Sheets("Sheet1").Range("A1") & "_" _
& Format(Sheets("Sheet1").Range("B1"), "ddmmyyyy") _
& ".xls"

ActiveWorkbook.SaveAs Filename:=wbSaveName

End Sub
Regards,

OssieMac



"kcleere" wrote:
I have ataskthat involves the following steps.

1. Open Excel file
2. Invoke Macro with customer menu button
3. After macro runs I select File-Save-As...then I change a date that
resides in the filename (looks like this) XXXX_todaysdate).xls
4. Close file (keep Excel open)

I know how to write the macro to save (as is) and close the file but I
don't know how to get it grab todays date and text it into the
filename before saving.

"Ideally"...I'd like to have the Macro rename the file with BOTH
todays date AND text in a fixed cell in the spreadsheet.

Such as: Monthly_Report_010107_.xls (Monthly Report) being the text in
the cell.

I have to run this on multiple files so I'd really like to see how to
automate this part.

Any suggestions you could give me would be appreciated.

Ken- Hide quoted text -

- Show quoted text -

OssieMac...

Works perfectly...thank you..!!