Results 1 to 14 of 14
  1. #1
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel & Powerpoint (2002 XP)

    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

  2. #2
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Excel & Powerpoint (2002 XP)

    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.

    <pre>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
    </pre>


    Cheers
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  3. #3
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel & Powerpoint (2002 XP)

    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?

  4. #4
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel & Powerpoint (2002 XP)

    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.

  5. #5
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts

    Re: Excel & Powerpoint (2002 XP)

    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

  6. #6
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel & Powerpoint (2002 XP)

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

  7. #7
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel & Powerpoint (2002 XP)

    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?

  8. #8
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Excel & Powerpoint (2002 XP)

    Let me think about your questions, I'm not really that speedy in PPT VBA. In fact, in previous posts you'll find me <img src=/S/aflame.gif border=0 alt=aflame width=16 height=16>, 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
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  9. #9
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Excel & Powerpoint (2002 XP)

    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).

  10. #10
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Excel & Powerpoint (2002 XP)

    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
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  11. #11
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Excel & Powerpoint (2002 XP)

    <P ID="edit" class=small>(Edited by WebGenii on 25-May-03 23:46. to correct error in macro)</P>This creates a new text box and attaches the mouseover action for the second macro to run.
    <pre>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.Slid eIndex
    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(p pMouseOver)
    .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.Slid eIndex
    CurSecond = Second(Time)
    PlusTime = Minute(Time) + 15
    CurHour = Hour(Time)
    DisplayTime = TimeSerial(CurHour, PlusTime, CurSecond)
    DisplayTime = FormatDateTime(DisplayTime, vbLongTime)
    With Application.ActivePresentation.Slides(CurSlide).Sh apes("TimeShape")
    .TextFrame.TextRange = DisplayTime
    End With
    End Sub

    </pre>

    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  12. #12
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel & Powerpoint (2002 XP)

    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" ?

  13. #13
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Excel & Powerpoint (2002 XP)

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

    Cheers
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  14. #14
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Excel & Powerpoint (2002 XP)

    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
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •