Simple way to refer to a workbook whose name varies

[davmec93]

Hi all. I had to post this because I was killing myself trying to make it work for a long time.
Everyday I have a need to open an excel workbook from a previous day and copy data from it to a new work book...so the name always changes. Today I had to open NameOfWorkbook 100510.xls and tomorrow it will be NameOfWorkbook 100610.xls and so on. I could never figure out how to refer to that work book in VBA once I got it opened so I could navigate around between that one and my current one. Below is what I figured out how to do. After I open the previous days workbook, I name the window. It doesn't change the name of the workbook but it does give me a constant to use.
ActiveWindow.Caption = "Yesterday"
So now no matter what the name of the workbook is (100910, 101010,101110) I just use
Windows("Yesterday").Activate

I hope someone can benefit from this like I did.:friends:

[Bob Phillips]

Why not set a global variable to that workbook after opening it



'code to open workbook
Set myWB = Activeworkbook
'more code


and then use that variable later



MsgBox myWB.Name

[davmec93]

Pure lack of knowledge....I must admit that my vba skills are in their infancy. Too bad I didn't find you a month ago.

Anyway...my solution will also work even if it is not as "proper". I just hoped that it could help someone.