Adding MMM-YY to filename

[Beechgrove]

Hi

I have seen a lot of posts around the net regarding how to datestamp an Excel filename but I haven't yet found anything to specifically stamp it with a supplied variable.

The scenario is that the macro opens up various files, grabs the data and appends it to another output file.

I then want to save the input file into an archive folder appending " - MMM-YY" to the filename. The value of "MMM-YY" needs either to be input in a prompt at the beginning of the process or to entered into a cell in the workbook that is running the macro.

I cannot rely on trying to extract the MMM-YY from today's date as (for instance) we could be in September but the filename needs to be appended with "AUG-12".

Please can anyone advise how this can be done?

Thanks

[BrianMH]

[VBA]
Sub svwb()
Dim wb As Workbook
Dim sDate As String
Dim spath As String
spath = "C:\"
sDate = InputBox("Enter the Date for the file prefix in the format MMM-YY")
'sDate = format(range("A1"), "MMM-YY") <<<<alternative to above assuming that A1 has a date in it
Set wb = Workbooks("asdf.xlsx") 'assign the workbook to the variable however you wish to
wb.SaveAs spath & sDate & "-" & wb.Name
End Sub

[/VBA]