Solved

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

Posted on 2004-04-08
4
1,229 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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
word 2016 1 29
zero pad a access form field 2 14
Using Multiple  Filters (XLS and XLSX) in File selection dialog 6 26
SQL multicriteria from ONE textbox 32 41
The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

816 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now