Macro to Find Names of All open workbooks

Posted by Haywood on January 30, 2002 10:54 AM

Can anyone supply the code to find the names of all open workbooks and then assign variables to them? I usually write macros that transfers numbers from one workbook to another and I can always get ONE of the names by using code that looks like this:
Sname = ActiveWorkbook.Name

How would I discover and then assign the other open workbook to a variable?

Thanks is advance!

Posted by Barrie Davidson on January 30, 2002 11:08 AM

Haywood, you can try something like this:

Dim BookNames()

ReDim BookNames(Windows.Count)
i = 1
For i = 1 To Windows.Count
BookNames(i) = Workbooks(i).Name
Next i

So Booknames(1) will equal the name of the first workbook open. Is this clear enough for you?

Regards,
BarrieBarrie Davidson

Posted by Larry on January 30, 2002 11:10 AM

****** Try this

Sub Workbook_Names()
For Each x In Application.Workbooks
wb_names = wb_names & Chr(13) & x.Name
Next x
MsgBox wb_names
End Sub

Posted by Haywood on January 30, 2002 11:30 AM

Thanks Barrie, just a quick question though...
Does it assign the numbers based on the order in which they are opened? For example if I open 2 workbooks, PERSONAL.xls will automatically be given the first spot in the array. Will the workbook I opened FIRST be given the next spot in the array or what?
Thanks again.

Posted by Barrie Davidson on January 30, 2002 11:36 AM

Haywood, to be honest, I don't know. I think it will be just as you described. Why don't you try it and let me know (I would be most curious to find out)?

BarrieBarrie Davidson

Posted by Haywood on January 30, 2002 12:17 PM

Barrie,

Strangely enough, it appears that is assigns the numbers on a first come, first serve basis. Whatever order the workbooks were loaded into Excel, is the number in cue they take. Learn something new everyday I guess.

Oh... one other thing Barrie, is there a way to select a RANGE using the (0,0) notation? I have only seen the range used with ("A3:Y3"). How would select a range using a variable? IE.

**This is what my brain wants to do
Dim per as Integer

Range(per,0 : per,26).select

** I know the syntax is off, what should I use?

Thanks :)

Posted by Barrie Davidson on January 30, 2002 12:55 PM

Haywood, I am assuming the variable "per" is assigning a row number. If that is the case, use this syntax:

Range(Cells(per, 1), Cells(per, 26)).Select

Note, you can't use 0 as a column number (1=column A).

Hope this helps you out.
Regards,
BarrieBarrie Davidson

Posted by Larry on January 30, 2002 2:38 PM

Hidden Workbooks

******* You can check whether a window is hidden, thus probably eliminating workbooks you are not interested in messing around with (like the Personal.xls workbook).

This code identifies visible and hidden windows, so you can put a sub routine in your code to ignore the hidden files (visible = false).

For X = 1 To Workbooks.Count
MsgBox (Workbooks(X).Name & Application.Windows(X).Visible)
Next X