VBA workbook reference

[madhatter40]

I have this code that works but the name of the workbook name changes each week. I currently have to go in and change the part W2 to W3
Each week the number will be different.
My question is how do I reference this workbook(Work PTS P6W2.xlsm) without referencing the exact workbook name?

Please Login or Register  to view this content.

[romperstomper]

Simple way is to either have the code open the workbook, or pop up a form with a list of open workbooks on it.

[joe31623]

I would avoid the .Activate method in most applications but that is besides the point of your question (most solutions that do not require .Activate would need the name of your workbook).

If you simply need to reference the workbook in which the code is running in, you can easily do that using: ThisWorkbook.Name (that will return a string, including the extension: .xlsm).

If you need to reference a workbook other than what the code is running in, that may be done using a set of workbooks that are open (given some assumptions on the name and a few If statements using Instr(...).
If you need to reference a workbook using its directory location, that can easily be done as well.

[joe31623]

...Note that if you must use .Activate with the workbook that is open, its name is not required. It may be done using command: ThisWorkbook.Activate