davmec93
10-06-2010, 12:58 PM
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:
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: