VBA to open the Folder that contains the previous month folder

HI all ,

I am trying to build a macro that does the following:

1. Look for the folder named after the current year
2. In that folder look for the Folder that contains the previous month number and name ("4. April")
3. Create a folder in there called "Stakeholder files" and save another macro manipulated Excel file in there.

This is what I have so far. I know is not much, still some work to do.

Sub Open_PreviousMonth_WB()

    Dim strMonthNumber As Integer, strPath As String, strFolder As String, strMonth As String, strFname As String

      strPath = "\Users\PMD\Desktop\MP\Report automation"

     strMonth = Format(DateAdd("m", -1, Date), "mmmm")

    strMonthNumber = Month(currDate) - 1

    strFolder = strPath & strMonthNumber & strMonth

    'strFname = Format(Date - 1, "mmmmmmm") & ".xls"

    ChDrive "C"

    ChDir strFolder

    Workbooks.Open Filename:=(strFolder & strFname)

 End Sub

Your help would be greatly appreciated.

Hi there,

See if the following code does what you need:

Sub CreateFolder()

    Const sSOURCE_FOLDER    As String = "C:\Users\PMD\Desktop\MP\Report automation"
    Const sNEW_FOLDER       As String = "Stakeholder files"

    Dim dteLastMonth        As Date
    Dim sMonthFolder        As String
    Dim sYearFolder         As String
    Dim sFullPath           As String

    dteLastMonth = DateValue("1/" & Month(Date) & "/" & Year(Date)) - 1

    sYearFolder = Format(dteLastMonth, "yyyy")

    sMonthFolder = Format(dteLastMonth, "m. mmmm")

    sFullPath = sSOURCE_FOLDER & "\" & sYearFolder & "\" & sMonthFolder

'   Check that the monthly folder exists before trying to create the Stakeholder Files folder
    If Dir$(sFullPath, vbDirectory) = sMonthFolder Then
        MkDir sFullPath & "\" & sNEW_FOLDER
    End If

End Sub

The highlighted values can be altered to suit your own requirements.


Hope this helps - please let me know how you get on.

Regards,

Greg M