Open Workbooks, Used Area on Sheet

[Guest]

I would like to add the capability to the sub below of getting the name of
each worksheet within the open workbooks. I tried nesting a For Each
wsNames(j) within the For Each wb in Application.Workbooks loop. I was not
able to resolve the errors.

Your suggestions are appreciated.


Sub wbsOpen()
' Get the names of all open workbooks and store in an array
Dim wbNames(), wsNames() As String
Dim i, j As Integer, wb As Workbook, ws as Worksheet

ReDim wbNames(1 To Workbooks.Count)

i = 1
For Each wb In Application.Workbooks
wbNames(i) = wb.FullName
Debug.Print wbNames(i)
i = i + 1
Next
End Sub

 

[Shah Shailesh]

Sub wbsOpen()
Dim wb As Workbook, ws As Worksheet
For Each wb In Application.Workbooks
Debug.Print wb.FullName
For Each ws In wb.Sheets
Debug.Print ws.Name
Next
Next
End Sub


Regards,
Shailesh Shah
http://in.geocities.com/shahshaileshs/
If You Can't Excel with Talent, Triumph with Effort.

http://in.geocities.com/shahshaileshs/menuaddins
(Free addins old\classic Office Menu-2003 for Office-2007)

 

[Guest]

Thanks for the help Shah,

Taking your example I cam up with the following. However, I am not able to
ReDim the wsNames() array without creating the error subscript out of range
after the first workbook is parsed. If the define the wsNames(1 to 10) I get
the results. But I would really like to set the array to cover all open
workbooks.

This type of programming is not for a rookie 'programmer' like me.

Anyone with the know how to set this up correctly? Thanks in advance.

Sub wbsOpen()
' Get the names of all open workbooks and store in an array
Dim wbNames(), wsNames() As String
Dim i, j As Integer, wb As Workbook, ws As Worksheet
Dim cSheets As Integer

ReDim wbNames(1 To Workbooks.Count)

i = 1: j = 1
For Each wb In Application.Workbooks
wbNames(i) = wb.FullName
Debug.Print wbNames(i)
ReDim Preserve wsNames(1 To wb.Sheets.Count)
For Each ws In wb.Sheets
wsNames(j) = ws.Name
Debug.Print wsNames(j)
j = j + 1
Next
i = i + 1
Next
End Sub

 

[Shah Shailesh]

Sub wbsOpen()

' Get the names of all open workbooks and store in an array
Dim wbNames() As String, wsNames() As String
Dim i As Long, j As Long
Dim wb As Workbook, ws As Worksheet

For Each wb In Application.Workbooks

i = i + 1
ReDim Preserve wbNames(1 To i)
wbNames(i) = wb.FullName
Debug.Print wbNames(i)

For Each ws In wb.Worksheets

j = j + 1
ReDim Preserve wsNames(1 To j)
wsNames(j) = ws.Name
Debug.Print wsNames(j)

Next

Next


'for testing array on the sheet

Sheets.Add ' create new worksheet

'put array to range

Range("a1").Resize(i).Value = Application.Transpose(wbNames)
Range("b1").Resize(j).Value = Application.Transpose(wsNames)
Cells.Columns.AutoFit

End Sub



Regards,
Shailesh Shah
http://in.geocities.com/shahshaileshs/
If You Can't Excel with Talent, Triumph with Effort.

http://in.geocities.com/shahshaileshs/menuaddins
(Free addins old\classic Office Menu-2003 for Office-2007)