Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Trenton, Ontario
    Posts
    175
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Running a macro at night (2000)

    The following is the code I am using to get a workbook to open, refresh and email. The code is located in "ThisWorkbook" of VB in the Personal workbook that remains open overnight but I can't get the other workbook to open, the first macro won't fire.
    Any help would be appreciated.


    Sub TrapTime()

    Application.OnTime TimeValue("20:00:00"), "Forecast"

    End Sub



    Sub Forecast()

    Workbooks.Open Filename:="Servershared dataJune Forecast.xls", UpdateLinks:=3
    ActiveWorkbook.RefreshAll
    ActiveWorkbook.Save
    ActiveWorkbook.SendMail Recipients:=Array("user1@company.com", "customer@anothercompany.com")
    ActiveWindow.Close
    End Sub

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,829
    Thanks
    136
    Thanked 482 Times in 459 Posts

    Re: Running a macro at night (2000)

    You have to run the TrapTime macro - it won't run by itself.
    Add a button to your sheet from the Forms toolbar and assign the macro TrapTime to it.
    Then click the button.

    zeddy

  3. #3
    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: Running a macro at night (2000)

    You might try creating a windows schedule as suggested here <post#=261270>post 261270</post#> to open a particular workbook in excel. That workbook could have the macro in the workbook_open event. The scheduler could do the task weekly, daily, monthly, etc without having to always run the macro with the ontime call manually every time period.

    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
  •