?
Solved

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

Posted on 2004-04-08
4
Medium Priority
?
1,247 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 5

Accepted Solution

by:
PhillipBurton earned 500 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

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

777 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