Thread: Constanty changing Workbook name( -2 business days)

try this

Workbooks("file " & Format(WorksheetFunction.WorkDay(Date, -2), "MM DD YYYY")).Activate

tried this..but getting run time error 438 'Object doesnt support this property or method'

Workbooks("file " & Format(WorksheetFunction.WorkDay(Date, -2), "MM DD YYYY")).Activate

try adding the extension of the files, but even though that shouldn't give you the mentioned error!!

Workbooks("file " & Format(WorksheetFunction.WorkDay(Date, -2), "MM DD YYYY") & ".xlsx").Activate

yeah I tried that.. got same thing.. really puzzled now lol..

could you do a little test just to know what is causing the error and tell me what happens:

Dim sWBName as String
sWBName = "file " & Format(WorksheetFunction.WorkDay(Date, -2), "MM DD YYYY") & ".xlsx"
MsgBox sWBName
Workbooks(sWBName).Activate

same, breaks w same error on Workbooks("file " & Format(WorksheetFunction.WorkDay(Date, -2), "MM DD YYYY") & ".xlsx").Activate

I used excel 2003, but I did switch .xlsx to .xls but still neither works

mmmm, I guess then the problem maybe in the workday function maybe it is not available in 2003...
try installing the analysis toolpack and try again
http://office.microsoft.com/en-us/ex...005209339.aspx

its weird because the one in blue below right before it works for another file...

Sub Clear()


Dim PositionFileName As String
Dim PositionFileName2 As String

PositionFileName = "example" & Application.WorksheetFunction.Text((Date), "ddmmmm")
PositionFileName2 = PositionFileName & ".XLS"
Dim LastRow As Long
LastRow = Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row

'Clear stuff
Workbooks("file " & Format(WorksheetFunction.WorkDay(Date, -2), "MM DD YYYY") & ".xls").Activate Sheets("data").Select

did you try installing the analysis toolpack?
the problem is probably in the Workday function
did you see the link I sent you in the previous post?

yea looks like I already have it......

Wait..... is that the code you are using??????

Workbooks("file " & Format(WorksheetFunction.WorkDay(Date, -2), "MM DD YYYY") & ".xls").Activate Sheets("data").Select

if so... that is definitively the problem.... use this instead

Workbooks("file " & Format(WorksheetFunction.WorkDay(Date, -2), "MM DD YYYY") & ".xls").Activate 
Sheets("data").Select

that just got added on by mistake..using the one as you mentioned.. thanks for your help mate.. sorry for taking your time..not sure what else I can do to fix this.

workbook is already open but then I open different workbook and need to switch back to the original