VBScript in Windows 7 64bit Scheduled Task - Works with only run when logged in

Question

So basically I have this VBScript that opens up an Excel 2010 instance, run a macro in it and close the spreadsheet. I am calling it using Scheduled Task to automate it. It ran beautifully when I was on Windows XP, but recently my company upgraded to Windows 7 and when I tried to schedule it using the new Task Scheduler in Windows 7, the task was stuck at running status but nothing was ever run if I choose Run whether user is logged in or not, with or without run with highest privileges checked. However if I use Run only when user is logged on, everything runs fine.

Here are a few things I've tried to test/verify the issue:

1. The same script could be scheduled in Windows XP with the scheduled task, without the need for user to log on. The script itself, of course, would work when I double-clicked it.

2. Another script that does not use Excel instance could be scheduled without any issue.

3. I have created a .bat file to call the VBS script and use Windows task scheduler to schedule the bat file with no luck, the bat file is one line of code:

   C:\Windows\System32\CScript.exe //Nologo //B "C:\myscript.vbs"

   When I ran it, it showed the task was completed but nothing was ever launched nor run.

   Other things I have tried include using CMD as the program./script and /c start "" "C:\myscript.vbs" as the argument, or using C:\Windows\System32\CScript.exe as the program/script and //Nologo //B "C:\myscript.vbs" as the argument. All with no luck.

4. Excel was not even launched in this case as I can see there is no additional Excel process in task manager.

5. There seems to be an option to schedule the task in compatibility mode with Windows XP but when I tried to right click and select Properties, under General tab, the only thing I can see in Configure for: is Windows 7 and server 2008 R2, second option is Windows Vista and server 2008.

6. My account has admin privileges on the PC.

Therefore, is there any way I can schedule this script somehow to run without the need of me to log on? I am open to any options. The PC is on Windows 7 Enterprise 64 bit with Excel 2010 installed.

Thanks in advance for your help!

Answer 1

Fixing Group Policy problems by using log files

Enable Logging for Group Policy Object Editor Client Side Extensions

[HKEY_LOCAL_MACHINE\Software\Microsoft\Windows NT\CurrentVersion\Winlogon]
GPTextDebugLevel REG_DWORD 0x30002

0/ see logs:

 %windir%\debug\usermode\gptext.log

1/ 32-bit run vbscript:

%windir%\syswow64\cscript.exe "C:\myscript.vbs"

2/ Programs may be unable to access some network locations after you turn on User Account Control in Windows Vista or in Windows 7

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Policies\System]
"EnableLinkedConnections" REG_DWORD 1

3/ Disable UAC

command line:

vbs-32.cmd ExcelSheetName.vbs ExcelSrc.xls

vbs-32.cmd:

SET WSH32=%windir%\SysWOW64\cscript.exe
%WSH32% %*

SheetName.vbs:

'Microsoft ActiveX Data Object 2.8 Library
'Microsoft ADO Ext. 2.8 for DDL And Security

Const ArrSize = 100
Dim ArrSheetName()
ReDim ArrSheetName(ArrSize)
IndexArr = 0

IF WScript.Arguments.Count = 0 Then
        WScript.Echo "Script Usage:"
        WScript.Echo "%windir%\SysWOW64\cscript.exe ExcelSheetName.vbs [Excel file full path]"
        WScript.Echo "%windir%\SysWOW64\cscript.exe ExcelSheetName.vbs D:\Office\ExcelSrc.xls"
       Wscript.Quit(10)
End If

Dim ExcelFileName
ExcelFileName=WScript.Arguments(0)

Dim ADOCatalog, ADOTable, ADODBConnection

Set ADOCatalog      = Createobject("ADOX.Catalog")
Set ADOTable        = Createobject("ADOX.Table")
Set ADODBConnection = CreateObject("ADODB.Connection")

Dim strConnString, strSheetName

strConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & ExcelFileName & ";Extended Properties=Excel 8.0;"

ADODBConnection.Open strConnString
Set ADOCatalog.ActiveConnection = ADODBConnection

For Each ADOTable In ADOCatalog.Tables
strSheetName = ADOTable.Name
strSheetName = Replace(strSheetName, "'", "")
strSheetName = Left(strSheetName, InStr(1, strSheetName,"$", 1)-1)
'Wscript.Echo strSheetName
ArrSheetName(IndexArr)=strSheetName
IndexArr=IndexArr+1

Next
ReDim Preserve ArrSheetName(IndexArr-1)

ADODBConnection.Close

Set ADOCatalog      = Nothing
Set ADOTable        = Nothing
Set ADODBConnection = Nothing

For Each ArrValue in ArrSheetName
    Wscript.Echo ArrValue
Next

Answer 2

I had the same issue and fought with it for a few months. Finally resorted to calling Microsoft for some assistance. The fix isn't something I would have ever found, so hopefully this helps others too. The script would run fine when I launched it in Powershell (and command line) but when it launched as a scheduled task it would run but not with expected results. Microsoft informs me that my issue is with running Excel in "non-interactive mode", you require two directories to be created.

• You have to create a folder (or two on a 64bit-windows):

(32Bit, always) C:\Windows\System32\config\systemprofile\Desktop

(64Bit) C:\Windows\SysWOW64\config\systemprofile\Desktop

The MS Excel when you are trying to run in non-interactive mode will look for this, if it is not present it will not open in the non-interactive mode.

once these were created the scheduled task runs flawlessly.