Solved

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

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

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
Comment Utility
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
Comment Utility
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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

744 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

19 Experts available now in Live!

Get 1:1 Help Now