On 9/29/2011 6:14 PM, SS wrote:
On Sep 28, 6:08 pm, wrote:
After serious thinking SS wrote :





Hi,
Any help would be appreicated...

I have a file open named:

203416 SS Mean Enzyme Activity Template.xls

Then i open another file with all the vba.
The operation in question needs to switch to the above file, but i
only know the first six digit number, and can't be sure of anything
else, except that it has the word Enzyme.
The six digit number has already been defined as variable req1

so, in my file with macros, i try to switch to the correct open
workbook using only req1 and Enzyme, but this doesn't work:

Windows((req1)& "*Enzyme*.xls").Activate

This does work, but i can't count on anything but the req1 number and
Enzyme to be there every time:

Windows((req1)& " SS Mean Enzyme Activity Template.xls").Activate

I think it's just syntax...

Thanks for any help,
Steve

Why not have your VBA project prompt you for which file to open and set
a reference to it so your code knows which open workbook to work with.
It stands to reason that if you knew which file to open BEFORE opening
your VBA project file then you'd also know which file to select so your
project can open it for you!

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc- Hide quoted text -

- Show quoted text -

I really appreciate the help. I know you guys have the answer. The
issue is exactly this:

A user works up an analysis file (an .xls file). So the file is
currently open. Since the user has edited the filename 'somewhat', I
can only be sure of two things, the six digit number at the beginning
of the filename, and the rest contains the word 'Enzyme'. There may
be some inconsistancies in the exact filename due to operator error,
such as adding an extra space, or mispelling a word, etc.

Then, the same user opens the file with the vba and runs macros. But,
i need to address the first file they had open, and still do, but am
unsure of the 'entire' file name; but do have two pieces of the
filename that will always be there. Obviously, there may be one or
more other unrelated files open by the user at that time as well.

What i need is the syntax to address that file, from my opened file
with all the vba. So, I want to switch to that workbook, or
worksheet, but only know the six digit number and the word Enzyme.
How do i say to switch to that window when the file name will be
called this (my comments are in parenthesis):

123456(could be anything)Enzyme(could be anything).xls

again, all i know is the number 123456 and Enzyme.

Thanks again for any help!! Sorry to beat this worse than a dead
horse :)

Steve

How does Excel know which folder contains the Enzyme file? Once you know
the folder you could use the Dir function and keep looping through all
of the filenames in that folder until you find a filename that contains
the two strings you specify.

This page contains BrowseForFolder code that will let you select a folder.
http://www.vbaexpress.com/kb/getarticle.php?kb_id=837

This page contains code that will let you loop through all files in a
folder, you can add the test for the correct filename using Instr:
http://www.ozgrid.com/forum/showthre...t=65530&page=1

Mike