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

Question

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

Not sure if this applies here, but if there is a clear time (hour) when the book will be manually opened and when the task scheduler will run, you can add a check on the current time. For instance, if your task scheduler is running in the middle of the night may be safe to assume nobody will be opening the book manually in this time. Therefore, you can run a check like ... IF NOW ~ 1 AM THEN EXECUTE — urdearboy
– urdearboy, Commented Sep 23, 2019 at 20:53
You could add a UserForm with a cancel button and a timeout. — chris neilsen
– chris neilsen, Commented Sep 23, 2019 at 20:54
@urdearboy I have the task scheduled for 12PM every day and it doesn't run for long. This could be an easy solution. — MyNameHere
– MyNameHere, Commented Sep 23, 2019 at 21:00
@chrisneilsen This sounds like a good option as well. This might be the better option functionally since I don't have to block out a certain period of time. — MyNameHere
– MyNameHere, Commented Sep 23, 2019 at 21:03

Dick Kusleika · Accepted Answer · 2019-09-23 20:56:55Z

0

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.

answered Sep 23, 2019 at 20:56

Dick Kusleika

33.3k4 gold badges56 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

MyNameHere Over a year ago

I don't think this would work very well since the file is about 17MB with a lot of formulas. It takes a while to save and load already, I would think making a separate file and deleting it would add quite a bit of extra time.

Dick Kusleika Over a year ago

The file you would create would be about 0k in size. It's just an empty text file that serves as a marker that your scheduled job started. In essence using the file system as a database. If you don't want to use the file system as a database, have the scheduled task write to the registry and have your code look for a value in a registry entry. Or have an actual database, like Jet or SQLite, that the scheduled task writes to and your code checks.

MyNameHere · Accepted Answer · 2019-09-23 22:25:40Z

0

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

answered Sep 23, 2019 at 22:25

MyNameHere

3371 gold badge3 silver badges17 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related