Solved

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

Posted on 2004-04-08
4
1,231 Views
Last Modified: 2012-05-04
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.
0
Comment
Question by:tdfreeman
  • 2
4 Comments
 
LVL 5

Accepted Solution

by:
PhillipBurton earned 125 total points
ID: 10784976
How about:

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

            For Each xlsheet In xlBook.Sheets()
            Next

?
0
 

Expert Comment

by:jim_1234567890
ID: 10784982

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
 
LVL 5

Expert Comment

by:PhillipBurton
ID: 10785015
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
 
LVL 1

Author Comment

by:tdfreeman
ID: 10785341
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

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

790 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question