Automating Excel from a scheduled task when NOT logged into Windows?

[Shadowpheonix]

I seem to have encountered bit of a problem accomplishing a task my boss has asked of me...
The task is to automatically merge Excel documents that are dropped into a specific folder, and place the resulting combined document into a different folder. The folder must be checked for new files every 5 minutes.

I figured that would be simple enough with a bit of Excel automation via ComObject and Windows Task Scheduler to run it every 5 minutes, and I was indeed able to get this all working successfully.

However, my boss has also stated that this has to all be accomplished without leaving anyone logged into the server, and without storing any credentials in the script itself. This is where things are falling apart for me - I can only get my script to work if I am logged into the server and have the "Run only when user is logged on" option enabled on the task in Task Scheduler. If I switch to the "Run whether user is logged on or not" option on the task, the script stops working.

By adding FileAppend, %A_LineNumber%, ScriptLog.txt as every other line, I was able to determine that the script is stopping when it hits the oExcel := ComObjCreate("Excel.Application") line if the "Run whether user is logged on or not" option is used.

I assume it is something to do with the way Windows Task Scheduler is handling the user permissions, but I am not 100% certain of this and have no idea what to do about it either way.


Any assistance would be greatly appreciated.



Here's the AHK code I am currently using...

Spoiler

Code: Select all

#SingleInstance, Force

SourcePath = C:\Pending Files
ProcessedPath = C:\Processed Files
CombinedPath = C:\Combined Files

IfExist, %SourcePath%\*.xl*
{ 
	oExcel := ComObjCreate("Excel.Application")
	oExcel.Visible := False
	TargetWorkBook := oExcel.Workbooks.Add(-4167)
	FormatTime, TargetFile, , yyyy_MMM_dd-hhmmss_tt
	TargetFile := CombinedPath . "/" . TargetFile . ".xlsx"
	TargetWorkBook.SaveAs(TargetFile)
	Loop, %SourcePath%\*.xl*
	{
		If A_LoopFileExt not in XLS,XLSX
			Continue
		CurrentWorkBook := oExcel.Workbooks.Open(A_LoopFileLongPath)
		Loop, % CurrentWorkBook.Sheets.Count
		{
			CurrentWorkBook.Sheets(A_Index).Copy(,TargetWorkBook.Sheets(TargetWorkBook.Sheets.Count))
			TargetWorkBook.Sheets(TargetWorkBook.Sheets.Count).Name := A_LoopFileName . "-" . CurrentWorkBook.Sheets(A_Index).Name
		}
		CurrentWorkBook.Saved := True
		CurrentWorkBook.Close()
		CurrentWorkBook := ""
		FileMove, %A_LoopFileLongPath%, %ProcessedPath%
	}

	TargetWorkBook.Sheets(1).Delete
	TargetWorkBook.Save()
	TargetWorkBook.Saved := True
	TargetWorkBook.Close()
	TargetWorkBook := ""
	oExcel.Quit()
	oExcel := ""
}
ExitApp

[/spolier]

[Shadowpheonix]

Is this simply not possible?

[toralf]

I‘m not an expert in this field.
To my knowledge it is not possible with AHK.
AFAIK you would have to write a service that could be run on the server with a scheduler. Don‘t think this is possible with AHK.

[gwarble]

The problem is likely with excel and not ahk:

https://stackoverflow.com/questions/423 ... r-logs-off

For what its worth, a service is possible with ahk, but not needed with task scheduler tasks