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]

This one sounds like fun

OK - what are we trying to do and what are we trying to do it to? For example...

What =

1. An embedded range object? If so then do =
   • Change cells formats?
   • change cells values?
2. An embedded chart? If so then do=
   • Change child chart objects properties?
   • Change chart types?

I have not automated Excel objects in PowerPoint. I've always ridden the other direction automating PowerPoint objects in Excel. So I'm not even sure how you're fetching the WB. What's the actual code you've got that's returning the workbook object?

 

[fos]

Hey, thanks for getting back so fast! to answer your question simply, (a). I am trying to manipulate spreadsheet stuff (ranges).
to answer more complexly, what I'm doing with the ranges is clearing them. I am actually then planning on deleting the sheets!
to answer your question most complexly of all, I'd love a reasonably general answer. what I'm trying to do is "learn to whoop down on powerpoint with VBA, in all cases of excel embeddings!" Make me a guru like you!

the most specific answer is:
our company makes these big excel workbooks, with all sorts of fancypants "added value". Then we make these big powerpoints, and paste little bitties from our excel into the powerpoints. except, as you know, those pastes are not little bitties, but entire workbooks.
a) we may be sharing proprietary data
b) we're bloating the files
c) nobody at my work's motivated enough to do this right (isolating the data in a new XL Worbook, and pasting THAT).

so I'm writing a script that runs through an entire ppt presentation, gets any embedded excel object, and
a) "hardens all values" (no more formulae, just string+numerical values).
b) deletes all pages EXCEPT the page that is "showing."

that's all trivial (to me, as i've had lots of practice), EXCEPT the part about finding out which page is showing!

like
dim b as workbook, s as worksheet
...
for each s in b
if s.name <> b.activesheet.name then s.delete
next s

 

[Greg Truby]

fos,

I'd be really wary of that approach. You could inadvertantly hammer source data that has not been backed up which will probably *not* get you many brownie points.

I'd be more inclined to have SOP changed to where no one is embedding Excel objects in slides, but rather paste as HTML or as Bitmaps or other pictures. I do all sorts of stuff in Excel (as you might guess). But I very seldom paste it into PowerPoint as a linked object. For ranges I'll paste as HTML. For Charts, I'll paste as a picture.

If that's a route you're willing to explore, we can see if there's a way to build something that will scan the presentation for Excel objects and try to convert them.

Regards,

Greg

Welcome to the Board, BTW.

 

[fos]

thanks again! well well...
a) sure, i'd love to know if there's an option to convert a FULL XL Workbook with some small range visible into an HTML table of just that visible range.
that sounds even harder than my original problem because we now need to know which RANGE is visible, not just the visible sheet.
b) since i don't want to burden you with actually writing me a whole project, I really would be satisfied with anyone's simple answer of how to determine which "tab" (worksheet) is "up front" (selected, or visible. active, if you will).

 

[Greg Truby]

A key point would also be what version of MS Office are you using?

 

[fos]

using powerpoint 2003 sp1

as far as i can tell, HTML pasting (like every other format we've tried BESIDES annoying embedding) does NOT preserve the exact formatting. we are looking for exact, because we make the spreadsheets in our format.

in some cases, HTML paste seems to look pretty good. there are cases in which it would be unacceptable, though
is there really no way to just check which sheet is "active"? would you like me to send you the code I have so far in competion, so you don't have to start from scratch if you're fiddling around...

 

[Greg Truby]

Well, right now, I'm on the outskirts of a lovely ice storm. So I'm packing it up and gettin' on the road. Probably won't have time to play until Monday.

 

[fos]

alright thank you.
take your time!
cheers.
-b

 

[fos]

is there a way I can resubmit this post for other comments? while Greg was helpful and informative, my problem is still unsolved, and I presume someone out there knows how to perform the simple task I seek...
anyone?