excelvbawindows-task-scheduler

Run Excel VBA only if Windows task scheduler opened the file


I have a small Excel table, which updated daily by users. It contains a date column.

I put a little VBA snippet which send an e-mail alert when the date column contain date which equal with the current date, and than close the file.

This Excel is called from task scheduler.

This works well, but now every time when a user open the excel, the vba send an e-mail and close the file.

Is it possible to run the VBA only when excel opened via task scheduler? (or if the file opened by specific user (eg. administrator).


Solution

  • As Dave mentioned you could use an environmental variable. But I personally wouldn't use the 'username' or any other account approach. If you use an environment variable I recommend you just use something like "scheduled" or whatever and Excel simply looks to see if the variable exists. You obviously have to have something that sets the environment flag in the first place when invoking from the task scheduler. So, for example if the scheduler invokes a batch file, you set the environment in the batch file before executing the Excel.exe command.

    However, you should also be able to pass a parameter in the Invokation of Excel command line, and in the VBA when it opens, look to see if the parameter exists. If I remember right, you can invoke something like this:

    "C:\Program Files (x86)\Microsoft Office\Office14\Excel.exe" SomeBook.xlsm /e
    /SomeParameter/
    

    And in the workbook Open even, you need to extract the command line parameter using an API. I think it this one:

    Declare Function GetCommandLine Lib "kernel32" Alias "GetCommandLineW" () As Long