wildcard in filenames in macro

  • Thread starter Thread starter Wes_A
  • Start date Start date
W

Wes_A

Excel 3007. I have a n application where I have several workbooks open at the
same time all controled from a "Main Menu" workbook.
The filenames are standard and always the same excepting for the first
character which varies. I am trying to write a macro's to 1) switch between
the windows and 2) to close the files when closing the Main Menu workbook.
Is there a way to reference the files within the macro no matter what the
first character may be in each case?
Any help or suggestion is greatly appreciated.
 
file/workbook is referenced in macro by its object, not by name. name is
necceccery only once, when the file/workbook is openned

try this code, while having oppened few workbooks:
 
I think that the 2nd question would be simple. You could loop through the
workbooks and inspect the names. If the 2nd through last characters match what
you want (the name of the main menu workbook???), you can close it.

dim wkbk as workbook
dim MainWkbk as workbook

set mainwkbk = workbooks("somenamehere.xlsm")
for each wkbk in workbooks
if lcase(mid(wkbk.name,2)) = lcase(mainwkbk.name,2) then
wkbk.close savechanges:=false 'or true and how would you know???
end if
next wkbk

But the first question is more difficult. How would you know what window to
change to? An alpha/numeric sequence????

Or just random selection <vbg>.
 
Hi Dave, let me clarify: I have 7 worksheets loaded plus the main menu. The 7
will always each have the same 7 names excepting the first character which
will be a single alpha character. This single alpha would be the same for all
7 files in any one run of the program, but the next time it may be a
different alpha for all 7, i.e. different files would have been loaded on
opening main menu.
Could one not perhaps do something similar when changingwindows as you would
do if saving or opening a filename from a varying filename in a cell?
 
So lets say you have:

A_namehere.xls
A_namehere.xls
A_namehere.xls
A_namehere.xls
A_namehere.xls
 
Sorry, I hit the wrong key!

So lets say you have:

A_namehere.xls
B_namehere.xls
C_namehere.xls
D_namehere.xls
E_namehere.xls
F_namehere.xls
G_namehere.xls

Say D_namehere.xls is active.

What workbook should be activated next?

Where would this code be located and how would the user run this macro?

I guess I'm still not understanding.
 
No, what I mean is that there would be the same workbooks open in each
instance, e.g. workbook1 ... workbook 7 Those parts of the filenames will
always be the same.
In each instance the workbook names would have a different single alpha
prefix e.g. A ... G, so one time Aworkbook1 ... Aworkbook7, the next instance
it may be Dworkbook1 ... Dworkbook7, or Fworjbook1 ... etc.
The user would intiiate the macro by clicking a control button.
Appoligies for not explaining the problem in a clearere manner in the first
place, and thank you so much for your help.
Dave, a second and unrelated question: How can I obtain the most recent date
froma column of some 1000+ dates? i.e. The highest value date.
Thanks again. Wes.
 
Since dates are just numbers, you can use:
=max(a1:A1000)
adjust the range to match.

And I don't see the difference between my example and yours. Except that in
yours, the names aren't identical after the first character (1, 2, ..., 7 at the
end).

But you still haven't say how the program would know how it would know what
workbook to activate next.

If the prefixes are always A to G and you want to activate the workbook with the
next letter:

Option Explicit
Sub testme()

Dim CurName As String
Dim NextName As String
Dim NextLetter As String
Dim TestWkbk As Workbook
Dim LastLetter As Long

CurName = ActiveWorkbook.Name

LastLetter = Asc(UCase("g"))

NextLetter = Chr(1 + Asc(UCase(Left(CurName, 1))))

If Asc(NextLetter) > LastLetter Then
NextLetter = "A"
End If

Set TestWkbk = Nothing
On Error Resume Next
Set TestWkbk = Workbooks(NextLetter & Mid(CurName, 2))
On Error GoTo 0

If TestWkbk Is Nothing Then
MsgBox "design error!"
Else
TestWkbk.Activate
End If

End Sub

But this will only work if the names always start with A to G (and none are
missing).

If the characters can be anything (0-9, a-z, ...), then I think you'll have to
build a table of open workbooks with that kind of name, sort the way you want,
and find the next one in the list.
 
Like Dave, I'm not 100% clear how the user interface is supposed to work.
However, if it helps any, I would note that you can switch "windows" (open
workbooks) using either...

ActiveWindow.ActivateNext

which will move to a different window (in the order the workbooks were
opened, I think). Or, you can move to a specific workbook by just activating
it...

Workbooks("Book1.xls").Activate

where you would use one of your actual workbook names in place of my example
workbook name of Book1.xls.
 
Back
Top