reference workbook by today's date

  • 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!

  • Re: reference workbook by today's date


    Try

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

    Regards
    [SIZE=3]Anthony
    [/SIZE]&WCF_AMPERSAND[SIZE=3]
    [/SIZE]&WCF_AMPERSAND&WCF_AMPERSAND&WCF_AMPERSAND[SIZE=2]You have your way. I have my way. As for the right way, the correct way, and the only way, it does not exist.[/SIZE]




  • 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!

  • 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

    Regards
    [SIZE=3]Anthony
    [/SIZE]&WCF_AMPERSAND[SIZE=3]
    [/SIZE]&WCF_AMPERSAND&WCF_AMPERSAND&WCF_AMPERSAND[SIZE=2]You have your way. I have my way. As for the right way, the correct way, and the only way, it does not exist.[/SIZE]




Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!