PDA

View Full Version : Start PP Slide Show after inactivity in Excel



mike31z
05-15-2007, 03:54 AM
At our weekend sport events we use excel to keep track of scores for individuals participating. We then create a new PP slide every 20 -25 minuted in order to show on TV Monitor connected to the Computer.
Does any on know of way to monitor excel inactivity and the auto start the PP slide show.

I did find KB article by johnske

"http://http://vbaexpress.com/kb/getarticle.php?kb_id=516

It does close the excel file but it's a little controlling. I would like to just run the slide show.

any Ideas


Mike in Wisconsin

PS. Not everybody that has access to the excel file who post scores even know how restart a PP slide show.

Bob Phillips
05-15-2007, 09:50 AM
This assumes that you powerpoint prersentation has timings embedded

This is ThisWorkbook code


Option Explicit

Private Sub Workbook_Open()
Call StartTimer
End Sub

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
Call DisableTimer
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.OnTime nTime, "RunSlideShow", , False
oPPT.Quit
Set oPPT = Nothing
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Call DisableTimer
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Call DisableTimer
End Sub

This belongs in a general module



Option Explicit

Public nTime As Double
Private Const nDuration As Long = 300 ' 5 minutes

Public Sub StartTimer()
nTime = Now + nDuration / 24 / 60 / 60
Application.OnTime nTime, "RunSlideShow"
End Sub

Public Sub DisableTimer()
Application.OnTime nTime, "RunSlideShow", , False
Call StartTimer
End Sub

Public Sub RunSlideShow()
Dim oPPT As Object

Set oPPT = CreateObject("PowerPoint.Application")
oPPT.Visible = True
oPPT.Presentations.Open "C:\personal\bob\consultancy\Janus\Development\HMap.ppt"
oPPT.ActivePresentation.SlideShowSettings.Run
End Sub

mike31z
05-15-2007, 01:33 PM
Thanks xld

I haven't tried it yet but got a couple of questions

1. I see were I have to enter the location of exact file location. I hope I got it correct.

2. If the PowerPoint was currently open and just stopped woul this still work?

Public Sub RunSlideShow()
Dim oPPT As Object

Set oPPT = CreateObject("PowerPoint.Application")
oPPT.Visible = True
oPPT.Presentations.Open
' inset location of were the Power Point is stored
"C:\personal\bob\consultancy\Janus\Development\HMap.ppt"
oPPT.ActivePresentation.SlideShowSettings.Run
End Sub

You also stated "powerpoint prersentation has timings embedded" I hope You mean how long each slide is displayed during the slide show.

What we do is we have a welcome slide then after each group of 5 finish an event I create a slide that show the scores in each sub event (this happent every 20 minutes or so over 4 or 5 hours. I use XPPORT to create the slide and that just takes a minute and I am in excel when I do this but I or some else doen't remember to switch over to the power point and start the slide show.

Mike in Wisconsin

mike31z
05-15-2007, 07:13 PM
I copied you code and changed something on the firse run time error.
What I did not tell you because of oversight is that I have 2 excel file open at the same time (TodaysTrap.xls) and (xl_2_PPt.xls) because of the way xpport makes slides by : the workbook option or sheet option.
I had to create a 2d xls file that acts as a transfer point. because we receive scores for 5 people every 25 minutes and that when i or another person need to create a slide and then run the powerpoint slide show.

The first run time error I encountered was:

Private Sub Workbook_TodaysTrapv4()
' I changed the name to the source xls file v4 is a temp add on to protect the original
Call StartTimer
End Sub

My 2d error occured here:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.OnTime nTime, "RunSlideShow", , False 'error this line
oPPT.Quit
Set oPPT = Nothing
End Sub

If I under stand the above correctly this will quit or stop the Powerpoint. If i am working in excel the the powerpoint is open but not active. Our copmuter doesn't have the ability to do both seperate. it one or the other but the power point is open always.

Mike in wisconsin

mike31z
05-17-2007, 10:33 AM
xld I don't know why but it started working. I do have one big problem. It opens a new verision of the traptoday.ppt file

This must be the part that opens a new version.

Public Sub RunSlideShow()
Dim oPPT As Object

Set oPPT = CreateObject("PowerPoint.Application")
oPPT.Visible = True
'oPPT.Presentations.Open "C:\personal\bob\consultancy\Janus\Development\HMap.ppt"
oPPT.Presentations.Open "D:\My Documents\TRAP\Trap 07\traptoday.ppt" ' my location
oPPT.ActivePresentation.SlideShowSettings.Run
End Sub

I solved the multiversions by marking "PPT.Presentation.Open" inactine with a " ' "

But the slide showes runs in the background full scree which is good but excel isin front of it I try and capture a picture of it.

Mike In wisconsin