Solved

Activate Workbook minus 2 days

Posted on 2011-09-28
17
186 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

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