[HELP] Macro to switch between workbook with date on its name

[sabatine]

Hello!

I need to create a macro that updates some datas for me, but I am having some troubles switching workbooks.

I need to activate a workbook that has the date of the yesterday on it's name.

i.e: a have an workbook that is updated everyday, so it's name is Open PO_yyyymmdd.

This macro will open this workbook (which is the one that I need to update data) and two others.

After it opens these workbooks, I need it to activate the one named Open PO_yyyymmdd.

Does anyone knows if it's possible to do so?

Here is the part of the code that I need help (specifically the last line of code):

Workbooks.Open Filename:="C:\Users\Felipe\Desktop\Open PO_XXX" & Format(Date, "yyyymmdd")
Workbooks.Open Filename:="C:\Users\Felipe\Desktop\POs_With_Cost"
Workbooks.Open Filename:="C:\Users\Felipe\Desktop\Macro"
Workbooks("Open PO_XXX ").Activate

Can anyone help me, please?

Thanks in advance.

 

[JoeMo]

You need the full name of the workbook including the file extension:

Workbooks("Open PO_yyyymmdd.xls").Activate

change the file extension to match your workbook.

 

[sabatine]

I appreciate your answer, but it's not that what I need.

I need a formula that alternate between open workbooks.

though, one these workbooks has on it filename a formula that saves him with today's date.

i.e.: Workbooks.Open Filename:="C:\Users\Felipe\Desktop\Open PO_XXX" & Format(Date, "yyyymmdd")

This formula will open the workbook that has the prefix Open PO_XXX and also has the current day date.

I need a formula that alternates between another workbook and this one.

Reminding that I will use this macro everyday, so workbook name will change accordingly to the date of the day. Because of this, I cannot ask macro to alternate to a file that has a fixed date, because it won't be right for what I need to do.

Do you know how to do that? Someone? Help!

Tks!

 

[Leith Ross]

Hello Sabatine,

When working with multiple workbooks, it is a good practice to a assign each workbook that is opened to a unique object variable. This will allow you to easily access each one. As JoeMo pointed out you also need to include the file type extension when opening the file.

Dim Wkb1 As Workbook
Dim Wkb2 As Workbook
Dim Wkb3 As Workbook

    Set Wkb1 = Workbooks.Open(Filename:="C:\Users\Felipe\Desktop\Open PO_XXX" & Format(Date, "yyyymmdd") & ".xls")
    Set Wkb2 = Workbooks.Open(Filename:="C:\Users\Felipe\Desktop\POs_With_Cost.xls")
    Set Wkb3 = Workbooks.Open(Filename:="C:\Users\Felipe\Desktop\Macro.xls")

    Wkb1.Activate

 

[sabatine]

Thank you, guys!

You solved my problem!

And sorry for my broken english.