Hello:

I'm using Excel 2003. I have a workbook (utility.xls) which I use to
extract data from other workbooks. Currently, I have the code written in the
utility which forces the name of the other workbook to be named something
specific. In this case, "Cost Data.xls":

Dim UTIL, COST as Object
Set UTIL = Workbooks(ActiveWorkbook.Name).Sheets("Sheet1")
Set UTIL = Workbooks("Cost Data.xls").Sheets("Sheet1")
{and so forth}

UTIL is intended to extract data from several workbooks, but right now I can
only do them one at a time and I have to name each one "Cost data.xls" before
doing the extraction.

HERE IS MY QUESTION:
Is there a way to get Excel to recognize all of the open workbooks by their
names (say I want to do five at a time) without renaming them and having to
specify a name(s) in my code?

Thanks in advance.

MARTY

Try something like

For Each wb In Application.Workbooks
Msgbox wb.Name
Next wb

in other words, you use the wb object instead of the workbook, such as

Set UTIL = wb.Sheets("Sheet1")

BTW, you don't need to use

Set UTIL = Workbooks(ActiveWorkbook.Name).Sheets("Sheet1")

you can simply use

Set UTIL = ActiveWorkbook.Sheets("Sheet1")

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Marty" wrote in message
...
Hello:

I'm using Excel 2003. I have a workbook (utility.xls) which I use to
extract data from other workbooks. Currently, I have the code written in
the
utility which forces the name of the other workbook to be named something
specific. In this case, "Cost Data.xls":

Dim UTIL, COST as Object
Set UTIL = Workbooks(ActiveWorkbook.Name).Sheets("Sheet1")
Set UTIL = Workbooks("Cost Data.xls").Sheets("Sheet1")
{and so forth}

UTIL is intended to extract data from several workbooks, but right now I
can
only do them one at a time and I have to name each one "Cost data.xls"
before
doing the extraction.

HERE IS MY QUESTION:
Is there a way to get Excel to recognize all of the open workbooks by
their
names (say I want to do five at a time) without renaming them and having
to
specify a name(s) in my code?

Thanks in advance.

MARTY

Marty,

Don't open the workbooks before hand - use a macro to open them. See example
code below.

HTH,
Bernie
MS Excel MVP

Sub OpenMultipleUserSelectedFiles2()
Dim filearray As Variant
Dim myBook As Workbook
filearray = Application.GetOpenFilename(MultiSelect:=True)
If IsArray(filearray) Then
For i = LBound(filearray) To UBound(filearray)
Workbooks.Open filearray(i)
Set myBook = ActiveWorkbook
'Do things here to myBook.Sheets("Sheetname").Range, etc...
myBook.Close False ' to close without saving...
Next i
End If
End Sub


"Marty" wrote in message
...
Hello:

I'm using Excel 2003. I have a workbook (utility.xls) which I use to
extract data from other workbooks. Currently, I have the code written in
the
utility which forces the name of the other workbook to be named something
specific. In this case, "Cost Data.xls":

Dim UTIL, COST as Object
Set UTIL = Workbooks(ActiveWorkbook.Name).Sheets("Sheet1")
Set UTIL = Workbooks("Cost Data.xls").Sheets("Sheet1")
{and so forth}

UTIL is intended to extract data from several workbooks, but right now I
can
only do them one at a time and I have to name each one "Cost data.xls"
before
doing the extraction.

HERE IS MY QUESTION:
Is there a way to get Excel to recognize all of the open workbooks by
their
names (say I want to do five at a time) without renaming them and having
to
specify a name(s) in my code?

Thanks in advance.

MARTY

Looks simple enough. Thanks Bob.
(and thanks for the tip about omitting "Workbooks" and ".Name")

"Bob Phillips" wrote:

Try something like

For Each wb In Application.Workbooks
Msgbox wb.Name
Next wb

in other words, you use the wb object instead of the workbook, such as

Set UTIL = wb.Sheets("Sheet1")

BTW, you don't need to use

Set UTIL = Workbooks(ActiveWorkbook.Name).Sheets("Sheet1")

you can simply use

Set UTIL = ActiveWorkbook.Sheets("Sheet1")

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Marty" wrote in message
...
Hello:

I'm using Excel 2003. I have a workbook (utility.xls) which I use to
extract data from other workbooks. Currently, I have the code written in
the
utility which forces the name of the other workbook to be named something
specific. In this case, "Cost Data.xls":

Dim UTIL, COST as Object
Set UTIL = Workbooks(ActiveWorkbook.Name).Sheets("Sheet1")
Set UTIL = Workbooks("Cost Data.xls").Sheets("Sheet1")
{and so forth}

UTIL is intended to extract data from several workbooks, but right now I
can
only do them one at a time and I have to name each one "Cost data.xls"
before
doing the extraction.

HERE IS MY QUESTION:
Is there a way to get Excel to recognize all of the open workbooks by
their
names (say I want to do five at a time) without renaming them and having
to
specify a name(s) in my code?

Thanks in advance.

MARTY

Intriguing. I'll have to play around with it before I fully understand what
it does. Thanks for the solution Bernie.

"Bernie Deitrick" wrote:

Marty,

Don't open the workbooks before hand - use a macro to open them. See example
code below.

HTH,
Bernie
MS Excel MVP

Sub OpenMultipleUserSelectedFiles2()
Dim filearray As Variant
Dim myBook As Workbook
filearray = Application.GetOpenFilename(MultiSelect:=True)
If IsArray(filearray) Then
For i = LBound(filearray) To UBound(filearray)
Workbooks.Open filearray(i)
Set myBook = ActiveWorkbook
'Do things here to myBook.Sheets("Sheetname").Range, etc...
myBook.Close False ' to close without saving...
Next i
End If
End Sub


"Marty" wrote in message
...
Hello:

I'm using Excel 2003. I have a workbook (utility.xls) which I use to
extract data from other workbooks. Currently, I have the code written in
the
utility which forces the name of the other workbook to be named something
specific. In this case, "Cost Data.xls":

Dim UTIL, COST as Object
Set UTIL = Workbooks(ActiveWorkbook.Name).Sheets("Sheet1")
Set UTIL = Workbooks("Cost Data.xls").Sheets("Sheet1")
{and so forth}

UTIL is intended to extract data from several workbooks, but right now I
can
only do them one at a time and I have to name each one "Cost data.xls"
before
doing the extraction.

HERE IS MY QUESTION:
Is there a way to get Excel to recognize all of the open workbooks by
their
names (say I want to do five at a time) without renaming them and having
to
specify a name(s) in my code?

Thanks in advance.

MARTY

not sure if you can but you do have a list of range names
in the upper left corner of you sheet. click the down
arrow next to the box that usually has a cell address in
it. you named ranges will be there. also your print ranges
and any exteranl data ranges. click one and go to it.

-----Original Message-----
Hello:

I'm using Excel 2003. I have a workbook (utility.xls)
which I use to
extract data from other workbooks. Currently, I have the
code written in the
utility which forces the name of the other workbook to be
named something
specific. In this case, "Cost Data.xls":

Dim UTIL, COST as Object
Set UTIL = Workbooks(ActiveWorkbook.Name).Sheets
("Sheet1")
Set UTIL = Workbooks("Cost Data.xls").Sheets("Sheet1")
{and so forth}

UTIL is intended to extract data from several workbooks,
but right now I can
only do them one at a time and I have to name each
one "Cost data.xls" before
doing the extraction.

HERE IS MY QUESTION:
Is there a way to get Excel to recognize all of the open
workbooks by their
names (say I want to do five at a time) without renaming
them and having to
specify a name(s) in my code?

Thanks in advance.

MARTY
.