VBA access to Excel object in PowerPoint

Hello VBA/Excel/PowerPoint folks,

I am having an issue with an embedded Excel Object in PowerPoint that I'm hoping someone can assist me with. I posted my question in the PowerPoint forum here, but doesn't appear to be much traffic there as here...

I am running PowerPoint 2010 and while I'm ok with general usage, I'm not as familiar with the inner workings as I am with Excel. From what I've Googled, there is no longer a Record Macro option in PP 2010 so my main method of learning is not available in PowerPoint.

In my current presentation, I have multiple slides with embedded Excel spreadsheets (Insert -> Table -> Excel Spreadsheet). Multiple people will be updating various slides and there's no guarantee they'll refocus the excel object to cells to "A1" to ensue the correct information is displayed when the presentation runs.

What I want to do is create a macro that will go through each slide and set the focus of each embedded excel sheet to cell "A1" to ensure when the presentation runs the information I want displayed is in frame.

I have generated the below code thus far:

Please Login or Register  to view this content.

that can write to cell A1 or any other cell that I want, but if cell AZ1234 is displayed on screen, I can't get it to scroll up to cell A1 to display the information I want.

I've tried .Select, .SetFocus .Show and a few others but either nothing happens or I get an error.

for .Select I get the error: "Run-Time Error '1004' Select method of Range failed"

Can anyone provide any assistance or direction in this (I'm assuming/hoping) simple Macro?

r/

SUPPO

Hi SUPPO

I 100% agree about recording macros in PowerPoint. It's more than 10 years since they dropped the capability and I still miss it.

This seems to work for me, run within PP. Perhaps you can tweak it to suit.

Please Login or Register  to view this content.

upload a sample file to analyze

NickyC,

Thanks for your help thus far. that does actually work..... partially.

There are 4 slides in this presentation. Slides 2-4 have an embedded Excel Object. Cell A1 has data that I want displayed in a presentation. If a user edits the file and leaves the focus on cell “XFD1048576” as it is in this file, I want a macro that will go through all the slides and refocus each excel object on cell “A1”

The Macro I have thus far is:

Please Login or Register  to view this content.

When running this I get an error: “OLEFormat (unknown member): Invalid request. The window must be in slide or notes view.”

If I navigate to slide 2 in slide or notes view and modify the code to this:

Please Login or Register  to view this content.

it works perfectly. I'm working to figure you how to cycle through the slides and will try to find a way to upload a sample file. I can't seem to do it from work at the moment.

r/

SUPPO

I've attached a sample file that has the issue I'm trying to solve. I had to put it in a .zip file since we're not able to upload .ppt files...

r/

SUPPO

VBA Gurus, can one of you come up with something more functional than this?

If not, this is really slows and clunky but seems to work. The difficulty I hadwas that, to change the selection, you have to actually open each workbook which makes it slow and unstable. Its much easier to manipulate the contents without opening the sheet, but that won't change the visible cell.

you will need to have the Microsoft excel object library checked for it to work (in powerpoint's VBA select tools > references and scroll down to msexcelobject. The name will vary a bit depending on your version).

Please Login or Register  to view this content.

thanks NickyC,

It works as I'd hoped it would. as you say, it is a bit clunky, but it works for the 22ish slides that I have. It'd be nice if there was a .screenrefresh method in PPT, but I found a go around/alternate option via google that I may implement if it comes to that point.

r/

SUPPO.