Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Brooklyn, New York, USA
    Posts
    176
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Get list of Worksheets from all Open Workbooks (Excel 2003 SP1)

    I'm looking for a routine that will list all the open workbooks and their worksheets in the current file. I thought it would be relatively easy to generate such a list, but I feel like I've run into a brick wall.

    Thanks AGAIN for all the great help available here...

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Get list of Worksheets from all Open Workbooks (Excel 2003 SP1)

    Where in the "current file" do you want the list? Do you want it in a worksheet? If so, what sheet and starting in what cell? Do you want to include ALL open workbooks, even hidden workbooks like Personal.xls?
    Legare Coleman

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Get list of Worksheets from all Open Workbooks (Excel 2003 SP1)

    If you want the list in columns A:B of the ActiveSheet, and you do not want to include hidden workbooks, then:

    <code>
    Public Sub ListSheets()
    Dim oWB As Workbook, oWS As Worksheet, I As Long
    I = 0
    ActiveSheet.Range("A:B").ClearContents
    For Each oWB In Workbooks
    For Each oWS In oWB.Sheets
    If oWB.Windows(1).Visible = True Then
    ActiveSheet.Range("A1").Offset(I, 0).Value = oWB.Name
    ActiveSheet.Range("B1").Offset(I, 0).Value = oWS.Name
    I = I + 1
    End If
    Next oWS
    Next oWB
    ActiveSheet.Range("A1").EntireColumn.AutoFit
    ActiveSheet.Range("B1").EntireColumn.AutoFit
    End Sub
    </code>
    Legare Coleman

  4. #4
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Brooklyn, New York, USA
    Posts
    176
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Get list of Worksheets from all Open Workbooks (Excel 2003 SP1)

    Thank you very much, Legare. This looks exactly like what I'm trying to do.

    I will give it a shot and get back to you shortly.

    Thanks again.

  5. #5
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Brooklyn, New York, USA
    Posts
    176
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Get list of Worksheets from all Open Workbooks (Excel 2003 SP1)

    Legare, it worked like a charm, with the small exception that when the last worksheet was reached, the variable oWS produced an error. So I added an ON ERROR line in front of the loop.

    Thank you very much for your assistance. The Excel world would be a poorer place without you and your colleagues.

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Get list of Worksheets from all Open Workbooks (Excel 2003 SP1)

    Is one of the sheets a "Chart Sheet"?

    Change the line to:

    <pre>For Each oWS In oWB.<font color=red>Work</font color=red>sheets</pre>


    to make sure you only loop through the worksheet collection which is a subset of the sheets collection.

    Or if you want all the sheets (not just the worksheets). keep the line as it is and change the line:
    <pre>Dim oWB As Workbook, oWS as <font color=red>Variant</font color=red>, I As Long</pre>


    Steve

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •