Solved

Activate Workbook minus 2 days

Posted on 2011-09-28
17
193 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
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.

 

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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

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

Question has a verified solution.

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

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

831 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