Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    Feb 2004
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Timer Function (2002)

    I have a timer (StartTimer & EndTimer) that when activated calls a sub SaveClose. This sub is supposed to simply save the sheet and then close. The problem is that if any other excel spreadsheet is open, that also closes. I used the code Workbooks("excelfile.xls").Close savechanges:=True. I have also tried to open another excel sheet and then close down the original from that (using Auto_Open) but Auto_Open fails to run.

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Timer Function (2002)

    Could you show us your code?
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    New Lounger
    Join Date
    Feb 2004
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Timer Function (2002)

    Firstly
    Timer.StartTimer 5 'This is run from Auto_Open()

    then

    Function StartTimer(timer_duration As Single)
    TimerSeconds = timer_duration
    TimerID = SetTimer(0&, 0&, TimerSeconds * 1000&, AddressOf SaveClose)
    End Function


    Public Sub SaveClose() (ByVal HWnd As Long, ByVal uMsg As Long, _
    ByVal nIDEvent As Long, ByVal dwTimer As Long)
    EndTimer
    MsgBox "test"
    'Windows("filename.xls").Close
    End Sub

    Public Sub EndTimer()
    On Error Resume Next
    KillTimer 0&, TimerID
    End Sub

    The timer is activate (MsgBox 1 appears) as well as the timer is killed. Hope you can figure this out!

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Timer Function (2002)

    There is no need to use the API timer you are using. The API timer will use brute force to start the procedure you have set it to run, even when Excel is in a state in which *normally* no VBA can be run. This may crash Excel, or -as you have seen- terminate it without warning.

    Use the Application.Ontime method instead.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Timer Function (2002)

    Check out Chip Pearson who details this technique along with Application.OnTime.

    I can not work with your code since I only use XL97 and this technique requires Office 2000 or higher.

    Your line:
    <hr>Public Sub SaveClose() (ByVal HWnd As Long, ByVal uMsg As Long, _
    ByVal nIDEvent As Long, ByVal dwTimer As Long) <hr>
    should probably be:
    <pre>Public Sub SaveClose(ByVal HWnd As Long, ByVal uMsg As Long, _
    ByVal nIDEvent As Long, ByVal dwTimer As Long)</pre>


    I don't see how it is closing down excel or more than the workbook named "filename.xls" with the code that is listed. Does the workbook "filename.xls" have a before close macro?

    Steve

  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Timer Function (2002)

    <hr>I can not work with your code since I only use XL97 and this technique requires Office 2000 or higher.<hr>

    If you're interested I have the code for '97 too.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  7. #7
    New Lounger
    Join Date
    Feb 2004
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Timer Function (2002)

    Thanks all very much for your help. I will have a look into that other method. To Steve, I apparently made a mistake when I was coping the code over as the code I have in my program is OK.

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Timer Function (2002)

    Thanks, but for how much I might use it (I haven't had a need yet) I would just use OnTime.

    I try to keep and get the code I might use frequently, but, if I start collecting everything, I will never be able to find anything.

    Steve

Posting Permissions

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