How do I add a month to a filename extracted from a cell

[Guest]

Hi, I have a cell containing a date in the format ddmmyyy (South African
format) eg. 28/3/2006. I want to extract from this cell the value of the
month and year as "March 2006" and then use it to save the workbook name in a
folder as ("C:\Data\Import March 2006.xls"). Any ideas how to do this in code?

 

[Guest]

If A1 is your cell, you can extract the information with:

format(month(A1),"MMMM") & " " & Year(A1)

To that, just concatenate the path prefix.

 

[somethinglikeant]

Lets say your date is in A1

Sub SaveAsMonth()
datestamp = WorksheetFunction.Text([A1], "mmmmmm yyyy")
ActiveWorkbook.SaveAs "C:\Data\Import " & datestamp & ".xls"
End Sub

I like to put folder locations into a macro sheet on a workbook so that
you can do for example
ActiveWorkbook.SaveAs [A2] & datestamp & ".xls"
this allows you to change folder locations and work more transparently
and your code can work more generally

somethinglikeant

somethinglikeant

 

[Tom Ogilvy]

Seems like it should be more direct, and it is:

Dim rng as Range
set rng = Thisworkbook.worksheets("Sheet1").Range("A1")
Thisworkbook.SaveAS "C:\Data\Import " & format(rng,"mmmm yyyy") & ".xls"

If rng contains a string rather than a date serial and you are having a
translation problem (day and month get transposed) then use

format(cdate(rng),"mmmm yyyy")
rather than
format(rng,"mmmm yyyy")


--
Regards,
Tom Ogilvy

Hi, I have a cell containing a date in the format ddmmyyy (South African
format) eg. 28/3/2006. I want to extract from this cell the value of the
month and year as "March 2006" and then use it to save the workbook name in a
folder as ("C:\Data\Import March 2006.xls"). Any ideas how to do this in

code?

 

[Guest]

Thanks guys for all your help - much appreciated - it works perfectly!