How to call out "other" open book
 

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

How to call out "other" open book
 

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

On Jul 17, 7:42?am, CLR wrote:
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

How to call out "other" open book
 

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

On Jul 17, 7:42?am, CLR wrote:
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

How to call out "other" open book
 

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





"Zone" wrote:

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

On Jul 17, 7:42?am, CLR wrote:
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

How to call out "other" open book
 

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

--
Regards,
Tom Ogilvy


"CLR" wrote:

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

How to call out "other" open book
 

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



"Tom Ogilvy" wrote:

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

--
Regards,
Tom Ogilvy


"CLR" wrote:

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