How to switch to an open window with a wildcard file name

[LeanAccountant]

Hi,

I usually have 2-3 files open when running this process and download a file from a web based program which opens an Excel file which is usually called "PL Invoices" but is sometimes called "PL Invoices (1)" or "PL Invoices (2)" (I've not seen higher numbers, but I suspect they're possible - the numbers seem to relate to the number of times I've run the report (e.g. if the macro crashed because I forgot to change something), so I am unlikely to get PL Invoices (3) or higher, if that helps).

I am trying to activate this file so I can copy and paste data (in the rest of the macro) into another file inconveniently called "PL Invoices 16/17" (relating to the financial year).
In the rest of the process I need to swap and change a couple of times between the files, so it would be nice to find/obtain the file name and be able to use it as a variable.

Can you help, please?

Thanks :-)

[JBeaucaire]

Cycle through the open workbooks looking for a matching name and then attach the found workbook name to workbook object variable, then use that variable name from then on.

Please Login or Register  to view this content.

[LeanAccountant]

Hi Jerry,

Thanks for the code. It looks as though the code is specifically looking for a file called "PL Invoices". Unfortunately, the file I download can be called "PL Invoices", "PL Invoices(1)" or "PL Invoices(2)" (theoretically there could be 3, 4, etc, but I have only ever seen blank, 1 or 2 so am happy to work with that.

Also, in the next part of the code, I am trying to activate that particular file... the specific code, below, worked fine...
'Windows("PL_Invoices.xls").Activate

However, when I try to use the variable sheet name, it doesn't work - do I need to do something else so I can use the variable file name?
Windows(wbSRC).Activate


Thanks,
Graham

[JBeaucaire]

No, the code is looking for the string "PL Invoice" in the names of all open workbooks. Instr() is a "find" tool.

We've set a workbook variable, so keep using it as the OBJECT, not a string source.

Please Login or Register  to view this content.