CODEWITHSUNDEEP
excelvba
MyNameHere
MyNameHere

Reputation: 305

How to run Excel macro on open with Task Scheduler but not on manual open?

Sorry about the title.

I have an excel workbook that I want to update daily so I created the following code. I also created a task so that it will update off peak hours which is great. The workbook will open, refresh, save, and close without having me to do anything.

The issue I can see with this is when I want to manually open the workbook, it's going to run this code when I don't want it to. I only want this to run with the scheduled task and now when it's manually opened. I thought about putting in a message box but then the task scheduler won't get past it.

How can I make sure the workbook is updated automatically with task scheduler, but not updated when I open the workbook manually?

Sub RefreshAndClose_Open()

ThisWorkbook.RefreshAll
DoEvents
ThisWorkbook.Save
DoEvents
Application.Quit

End Sub

Upvotes: 0

Views: 508

Answers (2)

MyNameHere
MyNameHere

Reputation: 305

Per @urdearboy I went with a simple time if... then statement. One hour blocked off isn't a lot since it's hardly opened. I'll look into @chrisneilsen suggestion of a countdown timer form.

Sub RefreshAndClose()

If Hour(Now) >= 12 And Hour(Now) <= 13 Then
ThisWorkbook.RefreshAll
DoEvents
ThisWorkbook.Save
DoEvents
Application.Quit
End If

End Sub

Upvotes: 0

Dick Kusleika
Dick Kusleika

Reputation: 33165

I would change the scheduled batch job to create a file in the same directory as the workbook named "runmacro.txt", open the workbook, then delete the file. Then in your macro:

If Len(Dir(ThisWorkbook.Path & Application.PathSeparator & "runmacro.txt")) > 0 Then

    'Your code

End if

The file won't be there except when opened by the scheduled task.

Upvotes: 0

Related Questions