Excel & Powerpoint (2002 XP)

Topic

New Reply

How would I insert the value from a single Excel cell into a ppt slide? During a presentation, I will have the worksheet open and it will be running a macro that recalculates every few minutes. When my slide is displayed, I want it to show the contents (whatever the value is as of the last recalculation) of that cell.

The purpose of this is to display 15 minutes ahead of the current time. In other words, if the current time is 10:30, I want my slide to display the contents of that Excel cell which will have a value of 10:45.

Thank you in advance

Reply | Quote

Replies

The attached presentation has the following macro in it to give you the current time +15 minutes. You can display the time using this macro in a couple of ways.
1) As a text box which updates when the mouse moves over it or clicks on it (as in example)
2) You could have a message box pop up when you click on a button (this could be easily reproduced on multiple pages, instead of having to return to the “time” slide).
Depends on what you want to achieve.
I know I haven’t answered your Excel question – but it seemed doable in PPT alone.

Sub InsertCurrentTimePlus15()
Dim PlusTime As Integer
Dim CurHour As Integer
Dim CurSecond As Integer
Dim DisplayTime As Variant

CurSecond = Second(Time)
PlusTime = Minute(Time) + 15
CurHour = Hour(Time)
DisplayTime = TimeSerial(CurHour, PlusTime, CurSecond)
' DisplayTime = FormatDateTime(DisplayTime, vbShortTime) 'rounds this off to the 24 hour clock
With Application.ActivePresentation.Slides(1).Shapes(1)
    .TextFrame.TextRange = DisplayTime
End With

' or you could use a msgbox
' MsgBox (DisplayTime)

End Sub

Cheers

Reply | Quote

Thanks Catharine, This concept is even better than linking it to excel. Regrettably, however, the macro doesn’t seem to work for me. It displays a slide with a text box containing 2:13:00 AM and there seems to be no way to update it. BTW, my current time is 6:29:00 AM. When I try to run the macro, the run command is grayed out. When I display this slide in a slideshow, my cursor chages to a hand when I move overt he time yet there is no time update, even if i click. Maybe i am doing something wrong?

Any ideas?

Reply | Quote

I don’t understand why I am unable to runa Macro in PPT. This is the first time I’ve tried so I am sure that there is something I am missing. I created my own using the followng code:

Sub timeshow()

Dim MyTime
MyTime = Time
With Application.ActivePresentation.Slides(1)
MsgBox (MyTime)
End With
End Sub

It doesn’t work either and oddly, when I go to the macro window (in tools), the run command is grayed out.

Reply | Quote

What setting do you have under Tools > Macro > Security?
Do you get any errors if you try to compile your code using Debug > Compile VBAProject

StuartR

Reply | Quote

that was the problem – security setting. It was on high, I changed it to medium and all works very well now.

Reply | Quote

Catharine,

I got the macro working fine now after adjusting the security settings. I have also adjusted it to show only the hour and second. Is it possible to do things more with this macro? 1) have the text box appear blank initially and/or 2) have the macro run automatically when the slide is displayed?

I also have a couple of questions about your code – I am new at VBA and trying to learn. When you wrote “CurSecond = Second(Time)” I understand that Cursecond is a variable you defined but where is Second(Time)? I looked in VBA help and couldn’t find it. Is it a reference to system time? Where would I look in Help to find it?

Reply | Quote

Let me think about your questions, I’m not really that speedy in PPT VBA. In fact, in previous posts you’ll find me , about it.
The Time Function returns a Date indicating the current system time.
The Second Function returns an Integer specifying a whole number between 0 and 59, inclusive, representing the second of the minute.
So when I wrote CurSecond = Second(Time), what I was doing was extracting the current value of seconds from the current time. I also did this for Minutes and Hours. This is so I could add the number 15, to the current value of minutes.
Then I used TimeSerial to put it back together as a time again.

Cheers

Reply | Quote

Usually, you can get help on VBA methods and statements by highlighting a word and hitting F1. If the help is not helpful, you might need to go back into the Office installer and install the VBA help files (they are not part of the standard install, I don’t think).

Reply | Quote

(Edited by WebGenii on 25-May-03 23:46. to correct error in macro)

This creates a new text box and attaches the mouseover action for the second macro to run.

Sub CleanInsertCurrentTimePlus15()
'does not require an existing text box

Dim PlusTime As Integer
Dim CurHour As Integer
Dim CurSecond As Integer
Dim DisplayTime As Variant
Dim CurSlide As Variant

CurSecond = Second(Time)
PlusTime = Minute(Time) + 15
CurHour = Hour(Time)
DisplayTime = TimeSerial(CurHour, PlusTime, CurSecond)
DisplayTime = FormatDateTime(DisplayTime, vbLongTime)
 CurSlide = ActivePresentation.SlideShowWindow.View.Slide.SlideIndex
Application.SlideShowWindows(1).View.Exit
With Application.ActiveWindow
    .ViewType = ppViewSlide
End With
With ActivePresentation.Slides(CurSlide).Shapes
    With .AddShape(msoShapeRectangle, 150, 100, 150, 70)
         .Line.Visible = msoFalse
         .Fill.Visible = msoFalse
         .Select
         .Name = "TimeShape"
        With .TextFrame
             .TextRange = DisplayTime
             .TextRange.Font.Bold = msoTrue
             .TextRange.Font.Size = 40
             .TextRange.Font.Name = "forte"
             .TextRange.Font.Color.RGB = RGB(50, 100, 255)
            
        End With
     End With
End With
    With ActiveWindow.Selection.ShapeRange.ActionSettings(ppMouseOver)
        .Run = "UpdateCurrentTimePlus15"
        .Action = ppActionRunMacro
    End With

ActivePresentation.SlideShowSettings.Run
Application.SlideShowWindows(1).View.GotoSlide CurSlide
End Sub
Sub UpdateCurrentTimePlus15()
Dim PlusTime As Integer
Dim CurHour As Integer
Dim CurSecond As Integer
Dim DisplayTime As Variant
Dim CurSlide As Variant

CurSlide = ActivePresentation.SlideShowWindow.View.Slide.SlideIndex
CurSecond = Second(Time)
PlusTime = Minute(Time) + 15
CurHour = Hour(Time)
DisplayTime = TimeSerial(CurHour, PlusTime, CurSecond)
 DisplayTime = FormatDateTime(DisplayTime, vbLongTime) 
With Application.ActivePresentation.Slides(CurSlide).Shapes("TimeShape")
    .TextFrame.TextRange = DisplayTime
End With
End Sub

Reply | Quote

Just a further note:
The CleanInsertCurrentTimePlus15 macro has to leave the slide show view to create the text box containing the time. It then returns to the slide it was on in Slide Show view. This is what causes the flashing on the screen (and in fact you may get a quick glimpse of normal view). Perhaps some other lounger will have a suggestion on how to supress the screen updating during this procedure, or may have a better technique.

Cheers

Reply | Quote

Thank you, I will try it. In the meantime, I have found that if i insert a space into the existing text box, I get what I want – the appearance of no text box and the timeplus appears on mouseover.

Also, regarding the screen flash as it exits the slide show – did you try “application.screenupdating = false” ?

Reply | Quote

application.screenupdating = false
seems to only be recognized by Excel.

Cheers

Reply | Quote

Just a note for those following this thread.
Sam Barret recommended this link at Shyam’s website for code to supress screen updating.
It works, somewhat. You no longer see the normal view although there is still a flash (through black effect).

Cheers

Reply | Quote