From: Dave Peterson on
So lets say you have:

A_namehere.xls
A_namehere.xls
A_namehere.xls
A_namehere.xls
A_namehere.xls


Wes_A wrote:
>
> 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?
>
> "Dave Peterson" wrote:
>
> > 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>.
> >
> >
> >
> > Wes_A wrote:
> > >
> > > 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.
> >
> > --
> >
> > Dave Peterson
> > .
> >

--

Dave Peterson
From: Dave Peterson on
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.

Wes_A wrote:
>
> 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?
>
> "Dave Peterson" wrote:
>
> > 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>.
> >
> >
> >
> > Wes_A wrote:
> > >
> > > 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.
> >
> > --
> >
> > Dave Peterson
> > .
> >

--

Dave Peterson
From: Wes_A on
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.

"Dave Peterson" wrote:

> 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.
>
> Wes_A wrote:
> >
> > 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?
> >
> > "Dave Peterson" wrote:
> >
> > > 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>.
> > >
> > >
> > >
> > > Wes_A wrote:
> > > >
> > > > 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.
> > >
> > > --
> > >
> > > Dave Peterson
> > > .
> > >
>
> --
>
> Dave Peterson
> .
>
From: Dave Peterson on
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.



Wes_A wrote:
>
> 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.
>
> "Dave Peterson" wrote:
>
> > 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.
> >
> > Wes_A wrote:
> > >
> > > 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?
> > >
> > > "Dave Peterson" wrote:
> > >
> > > > 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>.
> > > >
> > > >
> > > >
> > > > Wes_A wrote:
> > > > >
> > > > > 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.
> > > >
> > > > --
> > > >
> > > > Dave Peterson
> > > > .
> > > >
> >
> > --
> >
> > Dave Peterson
> > .
> >

--

Dave Peterson
From: Rick Rothstein on
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.

--
Rick (MVP - Excel)



"Wes_A" <WesA(a)discussions.microsoft.com> wrote in message
news:602EE188-E959-4407-8091-0DC893EF1173(a)microsoft.com...
> 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.
>
> "Dave Peterson" wrote:
>
>> 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.
>>
>> Wes_A wrote:
>> >
>> > 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?
>> >
>> > "Dave Peterson" wrote:
>> >
>> > > 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>.
>> > >
>> > >
>> > >
>> > > Wes_A wrote:
>> > > >
>> > > > 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.
>> > >
>> > > --
>> > >
>> > > Dave Peterson
>> > > .
>> > >
>>
>> --
>>
>> Dave Peterson
>> .
>>