Results 1 to 9 of 9

Thread: VBA Code simplification - Creating folder with month/year
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Nov 2007
    Posts
    168
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA Code simplification - Creating folder with month/year

    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.

    Code:
    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
    Last edited by davie1982; Jul 22nd, 2019 at 07:42 AM.

  2. #2
    Board Regular jmacleary's Avatar
    Join Date
    Oct 2015
    Location
    at a desk in the UK
    Posts
    628
    Post Thanks / Like
    Mentioned
    15 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Code simplification - Creating folder with month/year

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

    Code:
    Private Sub Command54_Click()
    On Error Resume Next
    DoCmd.SetWarnings False
    Dim yemoda As String
    Dim zDir As String
    
    zDir = Format(Date, "mmmm yy")
    
    yemoda = Format(Date, "yymmdd")
    
    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" & yemoda & ".xlsx"
    DoCmd.SetWarnings True
    End Sub
    Last edited by jmacleary; Jul 22nd, 2019 at 08:32 AM.
    If my answer has helped, please vote using the Thanks or Like buttons on the left.
    John

  3. #3
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    16,471
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default Re: VBA Code simplification - Creating folder with month/year

    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).

    Using: Office 2010/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  4. #4
    Board Regular
    Join Date
    Nov 2007
    Posts
    168
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Code simplification - Creating folder with month/year

    Thank you for help/advice.

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

    Thanks
    :>

  5. #5
    Board Regular jmacleary's Avatar
    Join Date
    Oct 2015
    Location
    at a desk in the UK
    Posts
    628
    Post Thanks / Like
    Mentioned
    15 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Code simplification - Creating folder with month/year

    You're welcome and thanks for the feedback.
    If my answer has helped, please vote using the Thanks or Like buttons on the left.
    John

  6. #6
    Board Regular jmacleary's Avatar
    Join Date
    Oct 2015
    Location
    at a desk in the UK
    Posts
    628
    Post Thanks / Like
    Mentioned
    15 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Code simplification - Creating folder with month/year

    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!!
    If my answer has helped, please vote using the Thanks or Like buttons on the left.
    John

  7. #7
    Board Regular
    Join Date
    Nov 2007
    Posts
    168
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Code simplification - Creating folder with month/year

    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.

  8. #8
    MrExcel MVP
    Join Date
    May 2006
    Location
    Excel 2003, Australia
    Posts
    9,154
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    3 Thread(s)

    Default Re: VBA Code simplification - Creating folder with month/year

    Quote Originally Posted by jmacleary View Post
    ... in fact a couple of my colleagues really struggle to understand that e.g. 20190711 is 11th September 2019!!
    To receive a better answer, put more work into asking the question.


  9. #9
    Board Regular jmacleary's Avatar
    Join Date
    Oct 2015
    Location
    at a desk in the UK
    Posts
    628
    Post Thanks / Like
    Mentioned
    15 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Code simplification - Creating folder with month/year

    oops!! Of course I should have said July!!!!!! No wonder they didn't get it!
    If my answer has helped, please vote using the Thanks or Like buttons on the left.
    John

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •