Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

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

Activate Workbook minus 2 days

Hi,

I have a some code and in it i am activating the below workbook

Windows("UnmatchedReport_GL_8455_56_57_20110926_TLM2.xls").Activate

Everyday the date moves on, so tomorrow when i look it will be

Windows("UnmatchedReport_GL_8455_56_57_20110927_TLM2.xls").Activate

Can i have a piece of code that looks to activate the workbook that is as above with Workday-2 days

So i dont have to change the code each day, it will look for a workbook that is called

UnmatchedReport_GL_8455_56_57_ with a date of workday - 2 days

Thanks
Seamus
0
Seamus2626
Asked:
Seamus2626
  • 6
  • 4
  • 4
  • +1
2 Solutions
 
jppintoCommented:
Can you try like this:

Windows("UnmatchedReport_GL_8455_56_57_201109" & DAY(NOW())-2 & "_TLM2.xls").Activate

jppinto
0
 
Seamus2626Author Commented:
That wont account for weekends though? and i would like the month and year to be able to roll on, so i dont have to change the code each month.

Thanks
Seamus
0
 
jppintoCommented:
This won't account for weekends:

Windows("UnmatchedReport_GL_8455_56_57_201109" & Day(Application.WorksheetFunction.WorkDay(Now(), -2)) & "_TLM2.xls").Activate

If you also want to change the year and month, then you should use this instead:

Windows("UnmatchedReport_GL_8455_56_57_" & Year(Now()) & Format(Month(Now()), "00") & Day(Application.WorksheetFunction.WorkDay(Now(), -2)) & "_TLM2.xls").Activate

jppinto
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
Seamus2626Author Commented:
Thanks Jppinto but i got the error 438 "Object doesnt support this property or method"

Can you save a WB with the name
 "UnmatchedReport_GL_8455_56_57_20110926_TLM2.xls"

and try and activate it from another WB with your code.

Cheers,
Seamus
0
 
jppintoCommented:
Is the workbook already opened?
0
 
jppintoCommented:
If the workbook is already opened you just need to remove the .xls like this:

Windows("UnmatchedReport_GL_8455_56_57_" & Year(Now()) & Format(Month(Now()), "00") & Day(Application.WorksheetFunction.WorkDay(Now(), -2)) & "_TLM2").Activate
0
 
Rory ArchibaldCommented:
Workday is not part of the worksheetfunction class in Excel pre version 2007 - it's part of the Analysis toolpak add-in.
0
 
Seamus2626Author Commented:
I have selected that in the Add ins Rory but i still get the object required error.

Taking away .xls doesnt seem to help either
0
 
Rory ArchibaldCommented:
I meant that you can't use that code becuase it's part of an addin, not part of the Object model. I'm 99.9% sure you already have code to workout the workdays from previous questions... :)
0
 
Seamus2626Author Commented:
Ya i have the code Rory, i just dont know how to work it in!

Thanks
Seamus
0
 
Patrick MatthewsCommented:
For Excel 2003 and earlier, if you want to let VBA use the extra functions that come with the Analysis ToolPak, you must also enable the "Analysis ToolPak - VBA" add-in.

Rather than go through that rigamarole, though...

Dim UseDate As Date

Select Case Weekday(Now)
    Case 1, 4, 5, 6, 7
        UseDate = Date - 2
    Case Else
        UseDate = Date - 4
End Select

Windows("UnmatchedReport_GL_8455_56_57_" & Format(UseDate, "yyyymmdd") & _
    "_TLM2.xls").Activate

Open in new window

0
 
Patrick MatthewsCommented:
Note that my suggestion above takes no account of holidays...
0
 
Rory ArchibaldCommented:
For example:
Function GetWorkday(dteStart As Date, lngDiff As Long) As Date
   Dim dtetemp As Date
   dtetemp = dteStart + lngDiff
   Select Case Weekday(dtetemp, 2)
      Case 7
         dtetemp = dtetemp - 2
      Case 6
         dtetemp = dtetemp - 1
   End Select
   GetWorkday = dtetemp
End Function

Open in new window


then your code becomes:
Workbooks("UnmatchedReport_GL_8455_56_57_" & format(GetWorkday(date(), -2), "yyyymmdd") & "LM2.xls").Activate

Open in new window

0
 
Seamus2626Author Commented:
Rory and MatthewsPatricks code has worked, would Jppintos have worked had i the add in?
0
 
Rory ArchibaldCommented:
No, since as I said, the Workday function cannot be called as a method of the Worksheetfunction object.
0
 
Patrick MatthewsCommented:
Seamus2626,

No, because as I said, in Excel 2003 or earlier to use that function in VBA you need the VBA-specific version of that add-in, and you would not hang that reference on the Application object.

It would have worked in Excel 2007/2010 without any add-ins, though.

Patrick
0
 
Seamus2626Author Commented:
Thanks all!
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 6
  • 4
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now