VBA Script to open a specific File adn activate a specific sheet within that sheet.

Hi,

I am looking for a script that when run would look for the date of the previous working day, and from this open a specific excel file from a single directory.

all the files are named in a MMM YYYY.xls format i.e. JUL 2013.xls, within each of these workboots are 31 sheets one for each (potential) day of the month.

So if the previous working day was the 21st of June 2013, the script would open JUN 2013.xls and activate the sheet named 21, and then run another function (that i've already written).

Thanks in advance.

I have been tinkering with this and have come up with the following:

In sheet 1 cell D5 I have the the formula: ="S:\Folder\"&TEXT(MONTH(WORKDAY(TODAY(),-1))*29,"MMM")&" "&YEAR(WORKDAY(TODAY(),-1))&".xls"

and in Cell D7 I have =DAY(WORKDAY(TODAY(),-1))

and in my script I have:

Please Login or Register  to view this content.

Is this the best/most efficient method?