Prev: Button image not loading on Custom Ribbon
Next: Run-time error '380' Invalid property value.
From: Wes_A on 16 Apr 2010 09:23
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.
From: Wes_A on 16 Apr 2010 09:34
OK, OK!! Should be 2007!

"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.
From: sali on 16 Apr 2010 10:23
"Wes_A" <WesA(a)discussions.microsoft.com> je napisao u poruci interesnoj
grupi:A7AE71D7-2E56-49FD-BC8E-FF9D967C82AA(a)microsoft.com...

>> Is there a way to reference the files within the macro no matter what the
>> first character may be in each case?

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:

----
for each wb in workbooks
msgbox wb.name
next
----


From: Dave Peterson on 16 Apr 2010 12:53
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
From: Wes_A on 16 Apr 2010 21:19
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
> .
>
 |  Next  |  Last
Pages: 1 2
Prev: Button image not loading on Custom Ribbon
Next: Run-time error '380' Invalid property value.