Run Excel from scheduled task when user is logged off

[Shalih]

Hi,

I have to run a macro in an excel sheet when the user is logged off... When i scheduled it now, i can see that the job never finishes and contine running for a long time.
Can you suggest a path forward?

I am using the command line instruction to run currently:
start /wait "%EXCELPATH%" "%FOLDER%\%EXCELJOB%"

SHaLih

 

[Derek Brown]

You did not say which operating system is being used. Windows 7 Task Scheduler gives you the option to run a task when the user is not logged in - but it may not always work as expected.

 

[Shalih]

HI Derek,

Thanks for the quick response...
i am accessing a virtual server through CITRIX/RDC. It runs on Windows NT. I was running Excel 2003.
BTW, i think i figured out the option. The issue was when the job kept 'running' to an infinite time and didnt fetch any result. After some R&D i found that if at all there is no scope for the batch script to fail, then while scheduling the task, minimize the time run to minutes(say, i minimized the time run to 4 min). This would end the task run by 4 min and post that a mail would be fired or watever action expected would be performed!

Very simple, worked like a charm....

If you have any other options, please do let me know.. I am still on the L curve....

 

[Shalih]

Well an Update... As i mentioned above, i had reduced the run time of the schedules task for 4 minutes... Ppl please make sure that it run for a while else the control may nt be returned to the script you call.. even Application.Quit cannot kill the excel.exe in the process manager...

 

[Derek Brown]

I run the Excel macro using a slightly different method.
Remember that I am currently using Windows 7 and Excel 2007:
1. Windows 7 Task Scheduler calls ".exe" code (written in VB.net - see below).
2. The .exe code starts Excel, calls the macro and then tidies the environment using Garbage Collector (GC)

Imports System.IO

    Public Sub Main()

        RunScheduledTask()

    End Sub

    Sub RunScheduledTask()
        Const strControlFileName As String = "X:\Folder\Task01.xlsm"
        Dim oExcel As Microsoft.Office.Interop.Excel.Application
        Dim oBook As Microsoft.Office.Interop.Excel.Workbook
        Dim oBooks As Microsoft.Office.Interop.Excel.Workbooks
        Dim strFilenameCheck As String
        Dim strMacroName As String
        Try
            strMacroName = "RunTask01"
            oExcel = CType(CreateObject("Excel.Application"), Microsoft.Office.Interop.Excel.Application)
            oExcel.Visible = False
            oBooks = CType(oExcel.Workbooks(), Microsoft.Office.Interop.Excel.Workbooks)
            strFilenameCheck = Dir(strControlFileName)
            If strFilenameCheck <> "" Then
                oBook = CType(oBooks.Open(strControlFileName), Microsoft.Office.Interop.Excel.Workbook)
                oExcel.DisplayAlerts = False
                oExcel.Run(strMacroName)
                oExcel.DisplayAlerts = True
            Else
                Dim sw As New StreamWriter(Application.StartupPath & "\ScheduledTask_error.log", True)
                sw.WriteLine(Now() & " - '" & strControlFileName & "' could not be accessed.")
                sw.Close()
                End
            End If
            '
        Catch ex As Exception
            Dim sw As New StreamWriter(Application.StartupPath & "\ScheduledTask_Error.log", True)
            sw.WriteLine(Now() & " - " & ex.Message)
            sw.Close()
        Finally
            oBook.Close(False)
            System.Runtime.InteropServices.Marshal.ReleaseComObject(oBook)
            oBook = Nothing
            System.Runtime.InteropServices.Marshal.ReleaseComObject(oBooks)
            oBooks = Nothing
            oExcel.Quit()
            System.Runtime.InteropServices.Marshal.ReleaseComObject(oExcel)
            oExcel = Nothing
            GC.Collect()
        End Try
    End Sub
End Module

I never have a problem and can kill the processes if I need to.
Note that references need to be set to Excel, in the VB code.