CODEWITHSUNDEEP
excelvba
Djh006
Djh006

Reputation: 67

I want to run a macro in my personal macro workbook automatically

Sub Auto_Run
    If Weekday(Now()) =6 then
        Application.OnTime TimeValue(“15:15:00”), “DCSReport”
    End If
End sub
Sub DCSReport
   ‘My code
End sub

How do I get it to run every Friday at 3:15?

Upvotes: 0

Views: 96

Answers (2)

Vityata
Vityata

Reputation: 43593

Running VBA code at a specific time with Excel only is kind of risky, because many things can happen. Probably it is a simpler and better idea to do it through the task scheduler or through some kind of cloud service. Anyway if you are willing to risk, here is a work-around:

  • Dedicate a separate spreadsheet, named "Log" in order to log the weeks in which the code was run. To make the weeks unique, add the year - Year(Now()) & WorksheetFunction.WeekNum(Now())

  • Make some kind of an event in the workbook, which is not triggered often - E.g. Workbook_AfterSave could do the job

  • Make a few checks in the Event, making sure that it would run at the specific time and day: If Weekday(Now()) = vbFriday And Time > ("15:15:00") And notSavedThisWeek Then

Code in the ThisWorkbook:

Private Sub Workbook_AfterSave(ByVal Success As Boolean)

    If Weekday(Now()) = vbFriday And Time > ("15:15:00") And notSavedThisWeek Then
        DCSReport
        LogThisAction
    End If

End Sub

Sub LogThisAction()

    With Worksheets("Log")
        Dim lastRow As Long
        lastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
        .Cells(lastRow, 1) = weekYearNumber
    End With

End Sub

Function notSavedThisWeek() As Boolean

    With Worksheets("Log")
        Dim someRange As Range
        Set someRange = .Cells.Find(What:=weekYearNumber)
        notSavedThisWeek = CBool(Not someRange Is Nothing)
    End With

End Function

Function weekYearNumber()
    weekYearNumber = Year(Now()) & WorksheetFunction.WeekNum(Now())
End Function

Sub DCSReport()
   Debug.Print "Code"
End Sub

Upvotes: 1

Geoff Griswald
Geoff Griswald

Reputation: 1093

There's a number of ways to approach running VBA macros automatically on a schedule. I find the simplest and easiest way to do it is with good old Windows Task Scheduler. You will need:

  • A .VBS file which opens your workbook and starts your macro
  • A Scheduled Task in Task Scheduler which executes that .VBS file using the CSCRIPT command

An example .VBS file might look like this:

Option Explicit
Dim xlApp
Dim xlBook
Set xlApp = CreateObject("Excel.Application")
xlApp.Application.DisplayAlerts = False
Set xlBook = xlApp.Workbooks.Open("\\Location-Of\Your\Personal-Macro-Workbook.xlsm", 0, False)
xlApp.Run "NameOfYourMacro"
xlApp.Application.EnableEvents = False
xlBook.Close False
Set xlBook = Nothing
xlApp.Quit
Set xlApp = Nothing
WScript.Quit

Just create that in Notepad or whatever your preferred text editor is and save it as a .VBS file

Then in Windows Task Scheduler, create a basic task.

The name, Description, Trigger (schedule) are all up to you, but in "Action" you need to select "Start a Program", then just type "cscript" in the "Program/script" box, and in "Add arguments (Optional)" you put the full path and filename for your .VBS file. Making sure you surround that in Quotation marks.

Upvotes: 0

Related Questions