Non-Interactive Windows Scheduled Batch Task Executes but Unable to Initiate Workbook_Open Event in Excel Unless User is Logged On

Question

Hi, 

I've been trying to isolate the cause of why my windows scheduled task will only successfully execute my workbook_open event when i'm logged onto the desktop even though my scheduled task is setup to "Run whether user is logged on or not".

Below is the Code of Both the Workbook Open Event Where I've Added Logging, as well as my batch file.

The below code works on the interactive session at all times, on demand, or within the scheduled task schedule

Please help

Thanks

******************************************************

' Open Event for File "C:\Dev\Report.xlsm"

Option Explicit

Option Base 0

Private Sub Workbook_Open()

    Call Runtime_Logger("Workbook_Open", "Got this Far.")

On Error GoTo err_Handler

    Call Runtime_Logger("Workbook_Open", "Past Handler.")

    #If VBA7 And Win64 Then

        Call Runtime_Logger("Workbook_Open", "Not Sure how it ended up here.")

    #Else

        Dim i As Integer

        Call Runtime_Logger("Workbook_Open", "To the Loop we go.")

        For i = 1 To 255

            If InStr(1, Environ$(i), "InBatch=TRUE", vbBinaryCompare) > 0 Then

                ' - Found Batch - '

                Call Runtime_Logger("Workbook_Open", "The Automated Run has Successfully Completed")

                Exit Sub

            End If

        Next i

        Call Runtime_Logger("Workbook_Open", "Out the Loop But Couldn't Find Property.")

    #End If

    Call Runtime_Logger("Workbook_Open", "The Workbook Open Event Has Successfully Completed.")

Exit Sub

err_Handler:

    Call Access_Error_Handler("Workbook_Open")

End Sub

'========================================================================================

' - Controls All Run Error Handling  -

'========================================================================================

Public Sub Runtime_Logger(ByVal sModule As String, Optional sMessage As String)

Dim oFileScript As Object

Dim oFile As Object

Dim sLogFilePath As String: sLogFilePath = "C:\Dev\Logs\LogFile.txt"

Set oFileScript = CreateObject("Scripting.FileSystemObject")

If Len(Dir(sLogFilePath)) > 0 Then

    Set oFile = oFileScript.OpenTextFile(sLogFilePath, iTextFileSetting, True)

Else

    oFile.CreateTextFile sLogFilePath

End If

With oFile

    .WriteLine ("==================================================================")

    .WriteLine ("= Message Log For=" & Now & "=====")

    .WriteLine ("= The Runtime is Currently in=" & sModule & "=====")

    If Not TypeName(sMessage) = "Null" Then

        If sMessage <> "" Then

        .WriteLine ("= The Provided Message has been =" & sMessage & ".")

        End If

    End If

    .WriteLine ("==================================================================")

    .Close

End With

Set oFile = Nothing

Set oFileScript = Nothing

End Sub

'========================================================================================

' - Controls All Run Error Handling  -

'========================================================================================

Public Sub Access_Error_Handler(ByVal sModule As String)

Dim oFileScript As Object

Dim oErrorFile As Object

Dim sErrorLogFilePath As String: sErrorLogFilePath = "C:\Dev\Logs\ErrorLogFile.txt"

Set oFileScript = CreateObject("Scripting.FileSystemObject")

If Len(Dir(sErrorLogFilePath)) > 0 Then

    Set oErrorFile = oFileScript.OpenTextFile(sErrorLogFilePath, iTextFileSetting, True)

Else

    oErrorFile.CreateTextFile sErrorLogFilePath

End If

With oErrorFile

    .WriteLine ("==================================================================")

    .WriteLine ("= Error Message Log For=" & Now & "=====")

    .WriteLine ("= The Error Occured in File=" & ThisWorkbook.Path & ThisWorkbook.Name & "=====")

    .WriteLine ("= The Error is under the description:" & Err.Description & "=====")

    .WriteLine ("= The with Error #:" & Err.Number & "=====")

    .WriteLine ("= The & Source of Error being:" & Err.Source & "=in module:" & sModule)

    .WriteLine ("==================================================================")

    .Close

End With

Set oErrorFile = Nothing

Set oFileScript = Nothing

End Sub

******************************************************

******************************************************

:: Begin Batch File

PUSHD C:\

SET dDIR=C:\Dev

DO IF NOT %CD% == %dDIR% CD %dDIR% 

SETLOCAL EnableExtensions

set EXE=EXCEL.EXE

FOR /F %%x IN ('tasklist /NH /FI "IMAGENAME eq %EXE%"') DO IF %%x == %EXE% goto EKILL

echo Not Currently running Loading Up Fresh Session

goto ERUN

:EKILL

echo Running Will Kill All Tasks

taskkill /f /IM %EXE%

:ERUN

set InBatch=TRUE

start %EXE% /x "C:\Dev\Report.xlsm"

SET EXE=OUTLOOK.EXE

FOR /F %%x IN ('tasklist /NH /FI "IMAGENAME eq %EXE%"') DO IF %%x == %EXE% goto OKILL

echo Not Currently running Loading Up Fresh Session

goto ORUN

:OKILL

echo Running Will Kill All Tasks

taskkill /f /IM %EXE%

:ORUN

::start %EXE% 

SET EXE=AcroRd32.exe

FOR /F %%x IN ('tasklist /NH /FI "IMAGENAME eq %EXE%"') DO IF %%x == %EXE% goto PDFKILL

echo Not Currently running Loading Up Fresh Session

goto PDFRUN

:PDFKILL

echo Running Will Kill All Tasks

taskkill /f /IM %EXE%

:PDFRUN

start %EXE% 

ECHO Batch file return code: %ERRORLEVEL% >> %temp%\MyBatch.log

******************************************************

Answer 1

Hi Kyles,

Thank you for posting your query in Microsoft Community.

Your question is beyond the scope of what is typically answered in this consumer forum and would be better suited for the IT Pro audience on TechNet.

Please post your question in the TechNet Forums.