Solved

Activate Workbook minus 2 days

Posted on 2011-09-28
17
198 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
  • 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
Industry Leaders: 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 92

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 92

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 250 total points
ID: 36716772
No, since as I said, the Workday function cannot be called as a method of the Worksheetfunction object.
0
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 250 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

Industry Leaders: 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!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Grouping Slicers - excel 3 37
Input box prompt with only 5 possible entries 5 38
Formula returning #N/A 9 31
Set a Range to a Cell in Excel VBA 2 9
A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

679 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