switching workbooks using variable and wilcard filename

  • Thread starter Thread starter SS
  • Start date Start date
S

SS

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
 
I would think you may have to use DIR to loop thru the folder until
you find the right file

if left(fn,6)="203416" & instr(fn,"Enzyme")>0 then
 
I would think you may have to use DIR  to loop thru the folder until
you find the right file

if left(fn,6)="203416" & instr(fn,"Enzyme")>0 then












- Show quoted text -
Click to expand...

Sorry, this is probably so simple...
The thing is, the file will be currently open, just need to switch to
it, address it, copy from it, etc. But, I don't know the full name of
the workbook. I only have two pieces of the filename (workbook name).

I think i just need the way to syntax this type of thing (everything
between workbook and activate):

Workbook.((predefined variable) + WILDCARD (ANY TEXT) + "Enzyme" +
WILDCARD (ANY TEXT) + ".xls").Activate

Does my original question make more sense?

Thanks for the help,
Steve
 
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
Click to expand...

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!
 
After serious thinking SS wrote :















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 -
Click to expand...

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
 
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
Click to expand...

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/showthread.php?t=65530&page=1

Mike
 
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):
Click to expand...
<snip>

Apologies, I think I misunderstood what you were looking for and sent
useless information in my last post, this shows how to find a window
using a full or partial title and give it focus, even if it's minimized.

http://vbnet.mvps.org/index.html?code/system/winclasstitle.htm

Mike
 
I still think it's best to prompt the user for the file. Mike suggests
listing the open workbooks and so you could put their names in a
listbox for the user to select. Of course, if there's only 2 files open
then no need to prompt the user because one file will be your VBA file
and the other one will be the file you're after. (Assumes user did not
close or the file already)

Otherwise, your VBA file should not be included in the list.<g>

IMO, this would be easier to manage if your VBA file is an Addin (XLA)
and already running whenever Excel opens. Custom menus can be added to
give users access to your macros, which could set a reference to the
ActiveWorkbook as the target file for processing.

Example:
Set wkbTarget = ActiveWorkbook
With wkbTarget
'..do stuff
End With 'wkbTarget

HTH
 
Dim wb as Workbook, w as Workbook

For Each w in Application.Workbooks
If Ucase(w.Name) like "######*ENZYME*" Then
Set wb = w
Exit For
End If
Next w

If Not wb Is Nothing then
'do stuff with wb
Else
Msgbox "No Enzyme workbook open!"
End if
 
Back
Top