VBA Code simplification - Creating folder with month/year

[davie1982]

Hi all

I have some long code here that I want to try and simplify/shorten, if possible. It's attached to a button on one of my forms. The code works great as it is though. Any suggestion?
It checks if a directory exists before moving a spreadsheet and adding the date at the end of it before the extension, and creates the directory based on the month and year.

Private Sub Command54_Click()
On Error Resume Next
DoCmd.SetWarnings False
Dim da As String
Dim mo As String
Dim ye As String
Dim mo1 As String
Dim mo2 As String
Dim mo3 As String
Dim mo4 As String
Dim mo5 As String
Dim mo6 As String
Dim mo7 As String
Dim mo8 As String
Dim mo9 As String
Dim mo10 As String
Dim mo11 As String
Dim mo12 As String
Dim zDir As String
mo1 = "January"
mo2 = "February"
mo3 = "March"
mo4 = "April"
mo5 = "May"
mo6 = "June"
mo7 = "July"
mo8 = "August"
mo9 = "September"
mo10 = "October"
mo11 = "November"
mo12 = "December"

Select Case Month(Date)
Case 1
zDir = mo1 & " " & Year(Date)
Case 2
zDir = mo2 & " " & Year(Date)
Case 3
zDir = mo3 & " " & Year(Date)
Case 4
zDir = mo4 & " " & Year(Date)
Case 5
zDir = mo5 & " " & Year(Date)
Case 6
zDir = mo6 & " " & Year(Date)
Case 7
zDir = mo7 & " " & Year(Date)
Case 8
zDir = mo8 & " " & Year(Date)
Case 9
zDir = mo9 & " " & Year(Date)
Case 10
zDir = mo10 & " " & Year(Date)
Case 11
zDir = mo11 & " " & Year(Date)
Case 12
zDir = mo12 & " " & Year(Date)
End Select
If Len(Day(Date)) = 1 Then da = "0" & Day(Date) Else da = Day(Date)
If Len(Month(Date)) = 1 Then mo = "0" & Month(Date) Else mo = Month(Date)
ye = Year(Date)

If Len(Dir("\\networkpath1\Zapper\" & zDir, vbDirectory)) = 0 Then
MkDir "\\networkpath1\Zapper\" & zDir
End If
DoCmd.OpenQuery "Q1-ManualImport"
Name "\\networkpath1\Zapper\spreadsheet.xlsx" As "\\networkpath1\Zapper\" & zDir & "\spreadsheet" & ye & mo & da & ".xlsx"
DoCmd.SetWarnings True
End Sub

 

[jmacleary]

Hi there. This should work: (changes in red)

Private Sub Command54_Click()
On Error Resume Next
DoCmd.SetWarnings False
[COLOR=#ff0000]Dim yemoda As String[/COLOR]
Dim zDir As String

[COLOR=#ff0000]zDir = Format(Date, "mmmm yy")

yemoda = Format(Date, "yymmdd")[/COLOR]

If Len(Dir("\\networkpath1\Zapper\" & zDir, vbDirectory)) = 0 Then
MkDir "\\networkpath1\Zapper\" & zDir
End If
DoCmd.OpenQuery "Q1-ManualImport"
Name "\\networkpath1\Zapper\spreadsheet.xlsx" As "\\networkpath1\Zapper\" & zDir & "\spreadsheet" & [COLOR=#ff0000]yemoda[/COLOR] & ".xlsx"
DoCmd.SetWarnings True
End Sub

 

[xenou]

As a note, consider using yyyy-mm as your format for directories (i.e., 2019-01,2019-02, etc.) which has the advantage of maintaining order across months and years (and makes it easier to sort and search, in my opinion, although I hardly ever finding anyone doing things this way - I guess that's life).

 

[davie1982]

Thank you for help/advice.

I should've thought about the format() function earlier but I just couldn't think of it.

Thanks
:>

 

[jmacleary]

You're welcome and thanks for the feedback.

 

[jmacleary]

BTW, I have to agree with xenou - using dates in a sortable/searchable year,month,day format is a good way to go - like him, I don't see it often, and in fact a couple of my colleagues really struggle to understand that e.g. 20190711 is 11th September 2019!!

 

[davie1982]

Hi, yes, that's how i'm currently organising my spreadsheets (all created through access) and it's quite helpful! Everything is nice and easy to find, which is nice especially when you get requests or queries.

 

[Fazza]

... in fact a couple of my colleagues really struggle to understand that e.g. 20190711 is 11th September 2019!!

 

[jmacleary]

oops!! Of course I should have said July!!!!!! No wonder they didn't get it!