Cycle through worksheets in a workbook (Excel): How to

What I am trying to do is to open an Excel file and process each worksheet in that file.  I have declared both a Excel.Workbook and a Excel.Worksheet variable.

    Dim xlApp               As Excel.Application
    Dim xlBook              As Excel.Workbook
    Dim xlSheet            As Excel.Worksheet

I thought I might be able to cycle through using a For Each loop such as:

            For Each xlsheet In xlBook
            Next

When I execute this I get an error that the object does not support this method.

Any Ideas?

Thank you.
LVL 1
tdfreemanAsked:
Who is Participating?
 
PhillipBurtonConnect With a Mentor Commented:
How about:

    Dim xlApp               As Excel.Application
    Dim xlBook              As Excel.Workbook

            For Each xlsheet In xlBook.Sheets()
            Next

?
0
 
jim_1234567890Commented:

Dim wbook As Workbook
Dim ws As Worksheet

Set ws = ActiveSheet   '  set ws to the current work sheet.

' You can use Workbooks.Open FileName, UpdateLinks:=False, ReadOnly:=True
' To open a work book in the code.

For Each wbook In Workbooks
    ' This will cycle throuth each open workbook.
      For Each ws In Worksheets
           ' This will cycle through each worksheet in the current open workbook.
            ' DO you r stuff here to the work sheet.

      Next ws
Next wbook

0
 
PhillipBurtonCommented:
Maybe tdfreeman's initialisation needs to be added (perhaps)? If so, then:

    Dim xlApp As Excel.Application
    Dim xlBook

Set xlApp = Excel.Application
Set xlBook = xlApp.Workbook
           
            For Each xlsheet In xlBook.Sheets()
               
            Next
0
 
tdfreemanAuthor Commented:
I think if I answer to this question I may be able to see the results.  For some reason it won't let me see the answer (and yes I have tried to refresh and reload the question).
0
All Courses

From novice to tech pro — start learning today.