How to call out "other" open book

C

CLR

Hi All.........
I have a little macro that works fine. However, I would like to change a
part of it that calls out the "other open workbook" to NOT be
hardcoded.......that is, to change the lines that read

Windows("ItemMaster.XLS").Activate
ActiveWindow.Close

to something that will do the same thing, but does not require the name
"ItemMaster.XLS" to be hard coded in, so that it will be selected no matter
what name it might be.

Here's the whole macro for reference....

Sub ObtainNewData()
Dim s As String
s = Range("MainMenu!c17").Value
If Range("c17").Value <> "" Then
Workbooks.Open FileName:=s
Else
MsgBox s & " Not found"
End If
Sheets("sheet1").Select
Sheets("sheet1").Copy After:=ThisWorkbook.Sheets("MainMenu")

Windows("ItemMaster.XLS").Activate
ActiveWindow.Close

Sheets("sheet1").Name = "ImportedData"
Sheets("MainMenu").Select
Range("e21").Select
End Sub

TIA
Vaya con Dios,
Chuck, CABGx3
 
Z

Zone

Chuck, well, you could use something like
For k=1 to workbooks.count
if workbooks(k).name<>activeworkbook.name then
workbooks(k).activate
exit for
end if
Next k
But what if more than 2 workbooks are open? How will Excel know which
one you want? James
 
Z

Zone

Chuck,
You could use something like
For k=1 to workbooks.count
if workbooks(k).name<>activeworkbook.name then
workbooks(k).activate
exit for
end if
next k

But what if more than 2 workbooks are open? Then the "other" workbook
could be one of several. James
 
C

CLR

Cool.........that worked fine James, many thanks.
The opening and closing of additional workbooks is done in this instance
only under program control, so there will never be more than one "other" one
open at a time....but thanks for the concern....and thanks again for the help.

Vaya con Dios,
Chuck, CABGx3
 
T

Tom Ogilvy

If only two visible windows (this will eliminate Personal.xls from the mix as
an example)

Sub BBB()
Dim WinDw As Window
For Each WinDw In Application.Windows
If WinDw.Visible = True And _
WinDw.Caption <> ThisWorkbook.Windows(1).Caption Then
WinDw.Activate
MsgBox WinDw.Caption
End If
Next
End Sub
 
C

CLR

Thanks Tom........didn't consider personal.xls. Although most of my users
don't have one, this will take care of the ones that do.........thanks again.

Vaya con Dios,
Chuck, CABGx3
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top