reference workbook by today's date

boombox · Dec 16th 2011

Hi

Every day I run a macro:-

Code

Sub CopyFromALR_To_Matcher()
    Application.CutCopyMode = False
'Copy PO
    Windows("16.12.11 Bob ALR.xlsx").Activate

there's more code than that, but I've copied only the relevant bit.

Basically I'd like to say

Code

Windows(Today() & "Bob ALR.xlsx").Activate

or something, instead of having to modify the VB every day.

Anyone know if this is possible? I have Googled...

Thanks!

smuzoen · Dec 16th 2011

Re: reference workbook by today's date

Try

Code

Windows(Format(Now, "dd.mm.yy") & " Bob ALR.xlsx").Activate

quant · Dec 16th 2011

Re: reference workbook by today's date

Hi,

you need to do it this way, it is like
Windows(Format(Date(), "DD.MM.YY") & "Bob ALR.xlsx").Activate

Richie(UK) · Dec 16th 2011

Re: reference workbook by today's date

Hi,

I'd go with something like this:

Code

Sub test()
    Const strEndName As String = " Bob ALR.xlsx"
    Dim strToday As String
    Dim strWbkName As String
    
    strToday = Date
    strWbkName = strToday & strEndName
    MsgBox strWbkName
    
End Sub

PS: You shouldn't really need to 'Activate' any object in order to work with it.
PPS: Anthony is TDQ!

smuzoen · Dec 16th 2011

Re: reference workbook by today's date

This will work???

Code

Sub CopyFromALR_To_Matcher()
 x = Format(Now, "dd.mm.yy") & " Bob ALR.xlsx"
   MsgBox x
   Windows(x).Activate
'or
Windows(Format(Now, "dd.mm.yy") & " Bob ALR.xlsx").Activate
    End Sub

However as Richie said you only need to reference the workbook not activate it to manipulate it

reference workbook by today's date

Participate now!

Share