Recognize Names of other Workbooks

[Guest]

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

 

[Bob Phillips]

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)

 

[Bernie Deitrick]

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

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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.