Excel VBA save as name "MONTH" converted to number (two digits)

Question

I'm new here and also in VBA. I'm trying to figure out to export active sheet to new workbook (just values) and save it as specific name where part of the name has to be month converted to two digits format. To be more specific there is a month name in "D4". I need to use this month in the name of the new workbook but converted to two digits format.

With ActiveSheet
                ActiveSheet.Range("A1:M40").Copy
                    Set NewBook = Workbooks.Add
                    With NewBook
                        NewBook.Worksheets("Sheet1").Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
                        SkipBlanks:=False, Transpose:=False
                        Dim LMonth As Integer
                        NewBook.SaveAs Filename:=ThisWorkbook.Path & "\" & Range("D4") & "_" & Range("I2") & "_" & " FLA"
                    End With
          End With

Here is the full code which prints out the sheet as pdf and then exports the sheet to new workbook:

Private Sub Print_PDF_Click()
With ActiveSheet
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
       ThisWorkbook.Path & "\" & ActiveSheet.Name & " " & Range("I2") & " FLA" _
       , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
       :=False, OpenAfterPublish:=True
          With ActiveSheet
                ActiveSheet.Range("A1:M40").Copy
                    Set NewBook = Workbooks.Add
                    With NewBook
                        NewBook.Worksheets("Sheet1").Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
                        SkipBlanks:=False, Transpose:=False
                        Dim LMonth As Integer
                        NewBook.SaveAs Filename:=ThisWorkbook.Path & "\" & Format(Range("D4"),"mm") & "_" & Range("I2") & "_" & " FLA"
                    End With
          End With
    MsgBox "PDF successfully created!"
End With 
End Sub

Please help!

As in [Convert month name into number](https://stackoverflow.com/questions/11895228/convert-month-name-into-number)? — Alex K., Aug 03 '18 at 14:56
It there really the name of the month in the cell or does the cell contain a date and is formatted to display the name of a month? — FunThomas, Aug 03 '18 at 14:59
"D4" contains Month name as "January, February ...etc." formatted as date. So if there will be "May" I need "05" in the name of new workbook. — Nuficek, Aug 03 '18 at 15:02
Still not clear to me: If you select the cell with the month: what is displayed in the data entry field at the top? A date? Then the *content* of the field is a date. In this case, simply use `format(Range("D4"), "MM")` — FunThomas, Aug 03 '18 at 15:13
"MM" saves it as month name not as two digits ("January" instead of "01"). — Nuficek, Aug 03 '18 at 15:26

asantaballa · Answer 1 · 2018-08-03T15:15:45.433

1

Looks kind of funny, but try

=TEXT(MONTH(DATEVALUE(D4 & "1")),"00")

http://www.exceltip.com/tips/converting-month-name-to-a-number-in-excel-2010-2013.html

edited Aug 03 '18 at 15:15

answered Aug 03 '18 at 15:03

asantaballa

3,919
1
21
22

I don't want to convert "D4". I just need to use "D4" value in the name of new workbook but converted to two digits format. – Nuficek Aug 03 '18 at 15:07
2

Or for `05` rather than `5`, `=TEXT(MONTH(DATEVALUE(D4 & "1")),"00")` – CLR Aug 03 '18 at 15:09
Thanks, @CLR, missed the two digits. Edited. – asantaballa Aug 03 '18 at 15:15
1

The code doesn't convert D4 as such, it converts the value in D4.... at the moment it's just text - Excel doesn't understand that it's a date just that it's some text - could just as well be "Nuficek" in there. Now, `DateValue` turns a text string into a date if it can - `DateValue("Nuficek 1")` will return an error, but `DateValue("August 1")` will return `01/08/2018` which can then be formatted to show the month number - `8`, or as @CLR added `08`. – Darren Bartrup-Cook Aug 03 '18 at 15:15

score 1 · Accepted Answer · answered Aug 03 '18 at 15:12

1

Using the VBA equivalent of @asantaballa's odd looking but great Excel formula..

Change your NewBook.SaveAs line's Filename to

 Filename:=ThisWorkbook.Path & "\" & Format(DateValue(Range("D4") & " 1"), "mm") & "_" & Range("I2") & "_" & " FLA"

This takes the month in D4, appends it with " 1" to make it look like a real date and then uses DateValue to convert it to a Date value which we can then use Format to derive the number from.

So AUGUST in cell D4

becomes AUGUST 1 by appending " 1"

which becomes 01/08/2001 using DateValue function

which is formatted to mm to produce 08.

answered Aug 03 '18 at 15:12

CLR

11,284
1
11
29

It says "Syntax Error". Are you sure there is ampersand between ("D4") & " 1") ? – Nuficek Aug 03 '18 at 15:19
You did only change everything **AFTER** `NewBook.SaveAs` yes? – CLR Aug 03 '18 at 15:21
This will produce a filename such as `"path\05_[contents of I2]_ FLA"` - did you want that space there? Did you not want to end with `.xlsx` or similar? – CLR Aug 03 '18 at 15:24
The Syntax error is now in the previous part to create PDF. So disabled this part of code and the export works like a charm! Thanks a lot @CLR! – Nuficek Aug 03 '18 at 15:31

Excel VBA save as name "MONTH" converted to number (two digits)

2 Answers2