?
Solved

Activate Workbook minus 2 days

Posted on 2011-09-28
17
Medium Priority
?
206 Views
Last Modified: 2012-05-12
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
Comment
Question by:Seamus2626
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
  • 4
  • +1
17 Comments
 
LVL 33

Expert Comment

by:jppinto
ID: 36716075
Can you try like this:

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

jppinto
0
 

Author Comment

by:Seamus2626
ID: 36716087
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
 
LVL 33

Expert Comment

by:jppinto
ID: 36716135
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Seamus2626
ID: 36716178
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
 
LVL 33

Expert Comment

by:jppinto
ID: 36716189
Is the workbook already opened?
0
 
LVL 33

Expert Comment

by:jppinto
ID: 36716194
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
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 36716232
Workday is not part of the worksheetfunction class in Excel pre version 2007 - it's part of the Analysis toolpak add-in.
0
 

Author Comment

by:Seamus2626
ID: 36716259
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
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 36716280
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
 

Author Comment

by:Seamus2626
ID: 36716550
Ya i have the code Rory, i just dont know how to work it in!

Thanks
Seamus
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 36716574
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
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 36716580
Note that my suggestion above takes no account of holidays...
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 36716594
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
 

Author Comment

by:Seamus2626
ID: 36716692
Rory and MatthewsPatricks code has worked, would Jppintos have worked had i the add in?
0
 
LVL 85

Assisted Solution

by:Rory Archibald
Rory Archibald earned 1000 total points
ID: 36716772
No, since as I said, the Workday function cannot be called as a method of the Worksheetfunction object.
0
 
LVL 93

Accepted Solution

by:
Patrick Matthews earned 1000 total points
ID: 36716780
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
 

Author Closing Comment

by:Seamus2626
ID: 36716844
Thanks all!
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a serious pitfall that can happen when deleting shapes using VBA.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question