VBA Open Previous Month's Folder

[Littlemalky]

I need to be able to open files in a folder from the previous month rather than the current month. I have code that does the current month, how do I tweak this to adjust for that change? The exact path is this:

G:\Inventory Control\Monthly Fulfillment reports\2011 Monthly Fulfillments\4-2011

Here is the code I have for opening the file currently:

Sub Open
Dim dirName As String, Fname As String, FileTime As Date, Filename As String, LatestFile As String
dirName = "G:\Inventory Control\Monthly Fulfillment reports\" & Format(Date, "yyyy") & " Monthly Fulfillments\" & Format(Date, "mm-yyyy") & "\"
Fname = Dir(dirName & "*.xlsx")
LatestFile = Fname
FileTime = FileDateTime(dirName & Fname)
While Fname <> ""
    If FileDateTime(dirName & Fname) > FileTime Then
        LatestFile = Fname
        FileTime = FileDateTime(dirName & Fname)
    End If
    Fname = Dir()
Wend
If LatestFile = "" Then
    MsgBox "There are no files in the directory"
Else
    Workbooks.Open dirName & LatestFile, UpdateLinks:=0
End If
End Sub

 

[Gary McMaster]

You can subtract a month from today's date with something like this:

Dim LastMonth As Date

LastMonth = DateSerial(Year(Date), (Month(Date) - 1), Day(Date))

MsgBox LastMonth
MsgBox Format(LastMonth, "mm-yyyy")

If I'm following your question, then you could substitute the variable "LastMonth" for "Date" in what you already have.

You will also have to develope a special case for January where the year also gets changed in the "DateSerial" function above.

Also, I'm not sure how it will behave if today's date is the 31st and last month only had 30 days. You may also have to check for that.

Hope it helps.

Gary

 

[Littlemalky]

Yeah, that's the tricky part that I was just realizing. I'm kind of screwed when I need to pick up December. I don't run the data until the next month so I need to figure that part out. Thanks, in the meantime, your code worked.

 

[Gary McMaster]

Maybe like this:

Dim LastMonth As Date

If Month(Date) = 1 Then
    LastMonth = DateSerial(Year(Date) - 1, 12, 1) 'If Jan subtract 1 from year set month = Dec, Day = 1
Else
    LastMonth = DateSerial(Year(Date), Month(Date) - 1, 1) ' Not Jan subtract 1 from month, Day = 1
End If

MsgBox Format(LastMonth, "mm/yyyy")

 

[Littlemalky]

Hey, so I'm still relatively new to VBA. If I wanted to set Lastmonth equal to that for the entire module I'm working in, so that I don't have to keep setting it each sub, how do I implement that?

 

[Gary McMaster]

You could make it "Public" (Global)

In the "Declarations" section of the module:

Public LastMonth as Date

Then comment out (or delete) "Dim LastMonth as Date" everywhere else.

Gary

 

[Littlemalky]

Ok, I can do that, but If I wanted to continue to reference this workbook throughout my macro wouldn't I need this "If, Then" statement in every Sub? How do I make that part public so that I don't have to continually restate that?

 

[Gary McMaster]

You would set the global variable once, in any place that is convenient. It will retain its value until the program ends or encounters an unhandled error. You will be able to retrieve the value of that global variable from any other procedure or change it from any other procedure if need be.

Gary