Move to another sheet in Workbook 2

[PaulBricker]

This has to be so simple as to be embarrassing but I'm opening an Excel spreadsheet. The spreadsheet opens to a specific Worksheet. I want to move to another worksheet (there are only two in the spreadsheet). How do I change the ActiveSheet from the one it open on to the other one. I've tried something like this but get Error 9, subscript out of range.

Dim objXL As Excel.Application
Dim objWkb As Excel.Workbook
Dim objSht As Excel.Worksheet
Const conSHT_NAME = "qryZBar"
Const conWKB_NAME = "c:\MyStuff\qryWOCountByMonth.xls"

Set objXL = New Excel.Application
With objXL
.Visible = True
Set objWkb = .Workbooks.Open(conWKB_NAME)
Set objSht = objWkb.Worksheets(conSHT_NAME)
ActiveSheet.PrintOut

I've tried
Worksheets("qryZBar&quot.Activate
and all kinds of variations. It there some way to go from the default worksheet to another sheet in the WorkBook.
Thanks

Paul

 

[SBendBuckeye]

Hello Paul,

Worksheets and Sheets are both Excel collections of worksheets within an open Workbook. Try the following:

Sheets(2).Activate

Note that these are 1s based, NOT 0 based like most of the collections in Access. I'm not exactly sure what the differences are between the 2 collections.

Good LucK!

 

[PaulBricker]

Go figure. I won't tell you how long I messed with this. It worked great. Thanks.

Paul

 

[xlbo]

This may or may not be relevant but as a very simple fix (non vba), the sheet which shows on opening is the sheet which is active when the file is saved so you "could" just save the file with that sheet active. Guess it depends whether anyone else will use the file and is likely to change the active sheet Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[PaulBricker]

Thanks for the interest Geoff. I was aware that I could make a specific sheet open as the default but this is for a network 3000 miles across the country so if I can make a specific sheet the ActiveSheet that will be the preferred method.

Paul

 

[RobBroekhuis]

SBB,
The difference between the Sheets and Worksheets collection is that the former includes chart sheets and worksheets, the latter only worksheets.
Rob

 

[PaulBricker]

Rob, that's good to know also. This happened to be a Chart they want to print out and I was able to get it set up for them. I don't work in Excel much(most of my work is in Access) but it seems lately that more people want some Excel automation with their Access apps.

Paul

 

[SBendBuckeye]

Thanks for the insigth Rob! I always wondered about that. If you wouldn't mind dropping a line to SBendBuckeye2000@yahoo.com, I have some Excel VBA code that is driving me nuts. It works in some instances but not for others and it is really bugging me.

In any event, thanks again for your helpfulness.

Paul, I thought you were one of the guys from the Access forums. The biggest suggestion I would make is spend a lot of time with the Range object. It shows up over and over and I'm still not sure I have my arms around it.

Also, most collections are 1s based, not 0 based like you are used to in Access.

Good Luck!

 

[PaulBricker]

Thanks. I did discover the 1 based collections while trying to work out my problem. I will take a look at the Range Object and play with that some. The school I work for has an Intranet that we have been thinking about putting some of the Databases on but the server is on what we call the "Student Side". Basically that means that all the upkeep on the Db's would have to be done on the server and I don't want that. I want the Db's to reside on the Admin side so I can maintain them from my office. Now, someone suggested looking at File Maker because it's already Web enabled but, it doesn't support ODBC, sooo, it can read Excel files (what's with that?) so what I'm thinking, at least till we get a decent Admin side server, is trying to set a small Admin Intranet by importing my Tables into Excel and let FileMaker handle the Web connection. I don't know what that will mean in the long run but for now, that means getting some Automation set up between Access and Excel. So the journey begins. Wish me luck.

Paul

 

[RobBroekhuis]

Hi SBB,
I don't mind taking a look at your code (email: broekhrr@apci.com) but if you think it's something a wider audience could benefit from, you should really post it here. I'll leave it up to you.
Cheers
Rob

 

[SBendBuckeye]

Thanks Rob. I think its too big for here as far as trying to describe it. Maybe if you find something, I can summarize the issue here.

Paul,

As I'm sure you already deduced, there is a Workbooks collection and a Worksheets collection both of which work very similarly to Access collections, except that they are ones based.

Make sure you look up something called specialcells. It is a special case property that allows you to select only blanks, only formulas, only numbers, etc. I was in the same boat you are in about 6 weeks ago and never even know some of these things existed. As always, the board came through!

Also, the Macro recording feature generates VBA code for you. Just open and close a few files while recording to see what kind of code it generates.

Also, if you write a couple functions to do range type work, make sure you dimension the range inside your functions as an object, NOT as a range type since there are different things which can perform range type operations such as worksheets, etc.

Good Luck, maybe I can spare you some of the head to brick wall controntations I had.

 

[xlbo]

I think that the reason excel collections are 1 based is because it is a collection of tangible objects whereas Access tends to be built from arrays of virtual objects. the key word here i believe is array. Even in excel, arrays are 0 based (unless you change the base) and I think Access makes much more use of array containers than excel Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde