Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Activate Workbook minus 1 days

Posted on 2011-09-30
2
Medium Priority
?
223 Views
Last Modified: 2012-05-12
I have some code attached that allows me to activate a spreadsheet that has a date of minus 2 days, i am looking for an amendment. First that it activates the spreadsheet minus 1 day and secondly the name of the spreadsheet changed.

It will always be

Sophis Fiscal ddmmyyyy.xls

So if it can look for the spreadsheet Sophis Fiscal ddmmyyyy.xls which has a date of minus 1 day, that would solve my issue!

Thanks
Seamus

Sub ActivateWB()




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.xls").Activate



End Sub

Open in new window

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
2 Comments
 
LVL 24

Accepted Solution

by:
StephenJR earned 2000 total points
ID: 36890811
Seamus - not entirely sure about the logic there, but try this:
Sub ActivateWB()

Dim UseDate As Date

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

Windows("Sophis Fiscal " & Format(UseDate, "yyyymmdd") & ".xls").Activate

End Sub

Open in new window

0
 

Author Closing Comment

by:Seamus2626
ID: 36890828
Perfect Stephen!!

Thanks
Seamus
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

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,…
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 create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

670 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