Run an ms word vba macro before a specific time

acmuller · #1 11-11-2023, 09:14 PM

In ms word, I have a VBA macro that runs each time I start Word. But I would like it to run only the first time I start my computer in the morning, not every time during the day. I have discovered the function of

Application.OnTime TimeValue("xxxx"),

But I would like it to run only before 11 am, and not after that. Or, on the first startup of Word
and not after that. I think I need to use a variable for the time value,
But I am not sure how that would work.

Any ideas?

gmayor · #2 11-13-2023, 02:27 AM

The following should work. You will need to add a few lines to your macro and then call it from an autoexec macro in the same template.
Basically it checks whether the time is before 11 am when Word is opened. If it is, it then checks whether a custom document property exists and if it doesn't contain today's date it runs your macro, which updates the document property and saves the template.

Code:

Option Explicit

Sub AutoExec()
Dim oCP As DocumentProperty
Dim bProp As Boolean
    If Val(Format(Now, "hhmm")) < "1100" Then
        For Each oCP In ThisDocument.CustomDocumentProperties
            If oCP.Name = "AutoRunDate" Then
                If oCP.value = Date Then
                    bProp = True
                    Exit For
                End If
            End If
        Next oCP
        If bProp = False Then Call MyMacro
    End If
End Sub


Sub MyMacro()
Dim oCP As DocumentProperty
Dim bProp As Boolean
For Each oCP In ThisDocument.CustomDocumentProperties
    If oCP.Name = "AutoRunDate" Then
        oCP.value = Date
        bProp = True
        Exit For
    End If
Next oCP
If bProp = False Then
    ThisDocument.CustomDocumentProperties.Add Name:="AutoRunDate", _
        LinkToContent:=False, _
        value:=Date, _
        Type:=3
End If
ThisDocument.Save
MsgBox "Test"  'the rest of your macro code
End Sub

gmaxey · #3 11-13-2023, 07:22 AM

Graham shows you a method to run you macro if you start word before 1100 a.m.

Here is a method to run your macro once per day. Regardless of the the time you start word. It uses the registry to store variables.

Code:

Option Explicit
Sub AutoExec()
  If GetSetting("DailyAutoRun", "Settings", "Date") = vbNullString Then SaveSetting "DailyAutoRun", "Settings", "Date", Format(Date, "MM/dd/yyyy")
  If CLng(CDate(GetSetting("DailyAutoRun", "Settings", "Date"))) <= CLng(Date) Then
    SaveSetting "DailyAutoRun", "Settings", "Date", Format(DateAdd("d", 1, Date), "MM/dd/yyyy")
    MyMacro
  End If
lbl_Exit:
  Exit Sub
End Sub
Sub MyMacro()
  MsgBox "Test"
lbl_Exit:
  Exit Sub
End Sub

acmuller · #4 11-13-2023, 08:45 PM

Graham and Greg; Thanks for this code. I'll get back to you again after I give it a try.-- Charles

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Macro to navigate to specific point in line (Word 97)	Genericname1111	Word	13	10-06-2019 08:54 PM
Macro to select multiple sentences that contain a specific word	MauiTruss	Word VBA	7	10-03-2018 03:46 PM
Executing a Word macro only after a certain date & time	rdross51	Word VBA	4	07-07-2017 06:29 AM
Help with Macro to Save Word File as PDF in Specific Location	ekimisme	Word VBA	1	06-07-2017 10:40 PM
Excel Macro finding a specific word	ducky831	Excel Programming	3	09-17-2015 01:36 PM