Combination question for excel and powerpoint macros

Hi,

I have an excel sheet with macros that will open a powerpoint presentation at a specific slide and show it as a slide show. After an animation is run in powerpoint I want to return to excel. I presently accomplish this with a macro in powerpoint which simply closes powerpoint. This is not very elegant for two reasons:

1. I use a CommandButton in powerpoint to launch the Application.Quit macro. This button is visible throughout the presentation, which is distracting/confusing. Ideally the button will only appear after the animation has completed.
2. The Application.Quit macro then returns the excel sheet to the monitor. It would be better if I could decide which excel sheet to show, as the desired sheet may not be the one that was last visible.

Does anyone have any thoughts on improvements in the solution, and hopefully also some working code? I am using Office 2007 and 2010.

BTW I have experimented with having only one macro, in excel that will start the powerpoint presentation, then wait for a while, then close powerpoint and go to the desired excel sheet. The disadvantage of this solution is that I want a manual trigger of the closing of the powerpoint presentation, as the user should be able to study whatever is on the screen at his/her own pace, before closing the presentation and reverting to excel. Would it be possible to have a message box appear in the powerpoint presentation, triggered by the excel macro, which would continue the macro (i.e. closing powerpoint and returning to excel) when the user responds?

Appreciate all help, hints and comments
Christopher

Wow, sounds great! Can't wait to try this Probably will not be for a few days though, busy with boring office stuff...

Dear Lewis, very impressive solution, far above my level of understanding, but I am looking, testing and learning! The two issues I am experimenting with is:

1. getting the excel macro to open a specific (but changing) numbered slide, depending on a variable value in excel
2. getting the correct power point macro to return to a specific excel sheet, depending on which slide was shown in powerpoint (only one slide is to be shown per instance of running the macros). I guess that means a different macro for each button, which is OK, there will only be about 30-40 slides.

I have cracked the code of how to run a specific slide number from my excel macro, just have to implement it within your huge and complext set up, but so far not certain how to return from excel to to a named powerpoint sheet...

Chris

Hi Chris,

1. getting the excel macro to open a specific (but changing) numbered slide, depending on a variable value in excel
2. getting the correct power point macro to return to a specific excel sheet, depending on which slide was shown in powerpoint (only one slide is to be shown per instance of running the macros).

I'm working on both of these problems, but it will take several days. Excel/Powerpoint dual debugging can be tedious, frustrating, and rewarding at the same time.

I'm working on starting the slide show from a list of slide numbers and/or names on the Excel side. There can be many lists of slide numbers/names (and the items on the list can be in any order and can be repeated).

In the mean time the following POWERPOINT macro may help you obtain a slide number from a name. To the best of my knowledge, PowerPoint can't directly access a slide by name, only by number. The following macro translates a name into a slide number (and returns 0 (ZERO) if the name doesn't exist.

Please Login or Register  to view this content.

Lewis

Lewis, the following simple code works for opening the slide number I want:

Sub ShowPhaseVideo()
'

Please Login or Register  to view this content.

I just need to copy the relevant parts of this code into your macro. I did have some problems when I tried, but haven't really had the time to test it thoroughly.... Thanks for all your help so far, looking forward to seeing if you come up with some solution for opening/returning to a specific Excel worksheet when the appropriate button is pressed on the excel slide.

Hi Christopher,

Thanks for posting your code. I'm still working on the update to my previous post. If I don't have any major problems or unforseen demands on my time, I should be finished in a week or two.

Lewis