[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 107
  • Last Modified:

Amend Code

I have some code which im running which i do not fully understand.

My aim is to open a folder, thats always last month, so this month it will open July

On the 1st August, this was correct, but today it is looking for an August folder, therefore debugging as it does not exist

My suspicion is it is due to the line If Day(Date) = 1, can i remove that, is this necessary?

Any time of the month, i always want to search for the previous month

Thanks

    FileYear = Year(Date)

    MonthOffset = 0
If Day(Date) = 1 Then MonthOffset = 1
    FileMonth = Month(Date) - MonthOffset
    FileDate = Format(Date - 1, "yyyymm")
    FilePath = "R:\SPM\Monthly_Inputs\" & FileYear & "\" & FileDate & "\Reporting\PCM_Cross_Region_Lost_Raw.xlsx"

    Workbooks.Open (FilePath)

Open in new window

0
Seamus2626
Asked:
Seamus2626
  • 2
1 Solution
 
MacroShadowCommented:
Try this:
    FileYear = Year(Date)
    FileMonth = Month(DateAdd("m", -1, Date))
    FileDate = Format(Date - 1, "yyyymm")

    FilePath = "R:\SPM\Monthly_Inputs\" & FileYear & "\" & FileDate & "\Reporting\PCM_Cross_Region_Lost_Raw.xlsx"

    Workbooks.Open (FilePath)

Open in new window

0
 
MacroShadowCommented:
btw, I don't see where you use FileMonth.
0
 
Seamus2626Author Commented:
I used   FileDate = Format(Date - 15, "yyyymm"), that covers me ;-)
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now