Any alternatives to WorkBook.ActiveSheet or Window.ActiveShe

[fos]

Hello. Background:
I am trying to write some PowerPoint VBA to fiddle with Excel objects embedded in my slides.
I have a slide, on which a (very small view of) an Excel Worksheet is embedded. As a matter of fact, the entire Workbook is embedded. I've managed to do ALMOST everything I want to do. I have a correct reference that WORKS:

dim wb as Workbook, ws as Worksheet
set wb = [blah blah, code that fetches the workbook from the slide]
set ws = wb.ActiveSheet

after running this code. ws is "nothing." I assume this is because the excel workbook is not actually Activated (workbook.windows.count returns 0).

I want ws to be the sheet which is VISIBLE in the ppt slide. assume there are 10 sheets in the workbook, and I am trying to delete all but the visible one. Is there some other code? i.e. wb.SELECTEDsheet or wb.VISIBLEsheet or something? obviously, neither of those exist.

I have tried the following, with identical results. i still get ws=nothing:

dim wb as Workbook, ws as Worksheet
set wb = [blah blah, code that fetches the workbook from the slide]
wb.activate
set ws = wb.ActiveSheet

can anyone help me out with a way to get a reference to the "visible" sheet? i would RATHER not have to "Activate" or open some Excel Application instance, or crap like that, but I'm desperate so will accept any hack. The reason I don't feel I should need to do these things is that I know i can do all sorts of other manipulations to the spreadsheets without having to do this.

Thank you in advance. The actual code follows.

Sub foo(s As Slide)
Dim x As Shape, y As Workbook
set x = s.shapes(1)
Set y = x.OLEFormat.Object
msgbox y.activesheet.name 'fails
end sub

 

[Greg Truby]

fos - it's perfectly acceptable to bump your own thread if it has drifted well down the list. ("Bumping" means to just add a post, generally just putting "bump" in as the post text.) This will move your thread back to the top of the heap. A couple of comments: it can come across as being a bit rude or impatient if you bump after only a brief period of time (only an hour or two) since everyone here is a volunteer. But if you've waited a day or even half a day and have received no additional posts, its okay to bump.

You are correct, HTML does not always to a spiffy job of replicating a fancy layout in Excel. And there are also times when a JPG or BMP doesn't do it justice either. In your particular case this may not be as simple as you seem to think it will be. Again, I would be very concerned about impacting source documents. Furthermore, you are talking about automating Excel inside of PowerPoint (or vice-versa, perhaps). That is an overlap of skills that somewhat diminishes the pool of respondents.

*Sorry* this advice on timing of bumps was really based on the speed with which threads move off the front page(s) in the main forum. I just remembered that this particular thread is in the "other discussions" forum. I just took a look and this thread is still #2 on the list many hours after the last post. If your thread is still in the top page of a forum, it's probably getting hit by as many eyeballs as its gonna and bumping it will not do any good. The good news is that most of the MVP's do tend to meander in through here from time to time, so your odds of getting seen by a heavy hitter are pretty good.