Access automation of Excel 101

[SBendBuckeye]

Hello all,

My next project is going to involve converting a large Excel spreadsheet to Access. Is there an Excel or Office equivalent to the Access Developers Handbook series by Getz, Litwin, etc?

In Access, I would loop through all the forms with something like this:

Dim frm As Form
Dim ctl As Control
Dim doc As Document
Dim dbs As Database
Set dbs = CurrentDB
For Each doc In dbs.Containers("Forms&quot.Documents
DoCmd.OpenForm doc.Name
Set frm = Forms(doc.Name)
For each ctl in frm.Controls
'Do some processing
Next ctl
DoCmd.Close acForm, doc.Name, acSaveNo
Next doc

How do I open and spin through all of the sheets of a given workbook file in a similar manner? Where can I go to learn about working with Excel using VBA?

Any help would be greatly appreciated.

Have a great day!

 

[RobBroekhuis]

There's any number of good books on Excel VBA. Generally, John Walkenbach's books get good reviews, but a peek on Amazon will get you user ratings and reviews that might guide your choice.

As for your worksheets question:

dim Sh as worksheet
for each Sh in activeworkbook.sheets
...
next Sh

Rob

 

[xlbo]

To go thru all sheets in a workbook:

For each ws in activeworkbook.worksheets '(can have chart sheets as well - just change worksheets to sheets to include those
msgbox ws.name
next

One of the things excel VBA has over Access is that there is a macro recorder

Make your vis bas toolbar visible and press the little circle button - do stuff - press the stop (square) button
Go to VBE, look at code

That's how I learned

To be honest, if you already know access vba, the best thing for you to do would be to play with excel (to understand what it can do) and look thru the object model (press F2 in the VBE) Rgds
~Geoff~

 

[SBendBuckeye]

Thanks to both of you for the helpful suggestions!

Have a great day!