Save files in accordance of month etc

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,380
Office Version
2007
Platform
Windows
Evening.
I have supplied the code below that is in use & works as it should.


Code:
Private Sub GrassSummarySheet_Click()    Dim strFileName As String
    
    strFileName = "C:\Users\Ian\Desktop\GRASS CUTTING\CURRENT GRASS SHEETS\" & Range("A3") & " " & Range("D3") & ".pdf"
    If Dir(strFileName) <> vbNullString Then
        MsgBox "GRASS SHEET " & Range("A3") & " " & Range("D3") & " WAS NOT SAVED AS IT ALLREADY EXISTS", vbCritical + vbOKOnly, "SUMMARY GRASS SHEET MESSAGE"
        Exit Sub
    End If
    
    With ActiveSheet
        .ExportAsFixedFormat Type:=xlTypePDF, Filename:=strFileName, Quality:=xlQualityStandard, IncludeDocProperties:=True
        MsgBox "GRASS SHEET " & Range("A3") & " " & Range("D3") & " WAS SAVED SUCCESSFULLY", vbInformation + vbOKOnly, "SUMMARY GRASS SHEET MESSAGE"
        Range("A5:B41").ClearContents
        Range("A5").Select
        ActiveWorkbook.Save
    End With
End Sub
All the above works fine but i need to think about how the files etc look in the saved foler.
I mean currently i see this
APRIL,JUNE,MAY

As the months sheets are added i will see December towards the very start.

I cant explain things correctly but if they could be save with a number in front the it would be in the correct order in the saved folder like so,
04 APRIL
05 MAY
06 JUNE
07 JULY
08 AUGUST
09 SEPTEMBER
10 OCTOBER
11 NOVEMBER
12 DECEMBER
13 JANUARY
14 FEBRUARY
15 MARCH
16 APRIL
This would be the full year save in the correct month order as the numbers would be save in the numerical sequence.

Otherwise it will be like,
APRIL
AUGUST
DECEMBER
JANUARY
JUNE
JULY
etc etc

many thanks
 

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,380
Office Version
2007
Platform
Windows
I needed the save file like JULY 2019 but spending some time today it was everything but so had to split them up
 

NoSparks

Well-known Member
Joined
Mar 15, 2013
Messages
890
Office Version
2010
Platform
Windows
How about saving them the other way around
Code:
    strFileName = "C:\Users\Ian\Desktop\GRASS CUTTING\CURRENT GRASS SHEETS\" & _
                  Range("D3") & "_" & Format(Month(DateValue(Range("A3") & " 1, " & "2019")), "00") & " " & Range("A3")
 

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,380
Office Version
2007
Platform
Windows
Nice but not quite.
Reason being June & July come before May
 

NoSparks

Well-known Member
Joined
Mar 15, 2013
Messages
890
Office Version
2010
Platform
Windows
After you added the file extension, were the file names not
2019_05 May.pdf
2019_06 June.pdf
2019_07 July.pdf
 

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,380
Office Version
2007
Platform
Windows
Sorry my mistake.
yes you are correct it works fine.

Thanks
 

Watch MrExcel Video

Forum statistics

Threads
1,102,455
Messages
5,486,998
Members
407,575
Latest member
calc

This Week's Hot Topics

Top