1

I have set up a task in task manager that calls a single .bat file (which then calls a .vbs) to tackle 4 easy steps:

.bat file:

Step 1: Grabs a handful of .xlsx files from over the network and copies them locally with a datestamp

COPY "\\prime\d$\Cognos Reports\eMoney\Client_Ethic.xlsx" "D:\Batch\%DATE:~4,2%%DATE:~7,2%%DATE:~-4%_Client_Ethic.xlsx"
COPY "\\prime\d$\Cognos Reports\eMoney\InvestmentDepositAccounts_Ethic.xlsx" "D:\Batch\%DATE:~4,2%%DATE:~7,2%%DATE:~-4%_InvestmentDepositAccounts_Ethic.xlsx"
COPY "\\prime\d$\Cognos Reports\eMoney\LiabilityAccounts_Ethic.xlsx" "D:\Batch\%DATE:~4,2%%DATE:~7,2%%DATE:~-4%_LiabilityAccounts_Ethic.xlsx"
COPY "\\prime\d$\Cognos Reports\eMoney\Relationship_Ethic.xlsx" "D:\Batch\%DATE:~4,2%%DATE:~7,2%%DATE:~-4%_Relationship_Ethic.xlsx"

Step 2: Calls the .vbs file to convert all .xlsx files in the dir to .csv

The call: FOR /f "delims=" %%i IN ('DIR .xlsx /b') DO to-csv.vbs "%%i" "%%~ni.csv"

if WScript.Arguments.Count < 2 Then
    WScript.Echo "Please specify the source and the destination files. Usage: ExcelToCsv <xls/xlsx source file> <csv destination file>"
    Wscript.Quit
End If

csv_format = 6

Set objFSO = CreateObject("Scripting.FileSystemObject")

src_file = objFSO.GetAbsolutePathName(Wscript.Arguments.Item(0))
dest_file = objFSO.GetAbsolutePathName(WScript.Arguments.Item(1))

Dim oExcel
Set oExcel = CreateObject("Excel.Application")
oExcel.DisplayAlerts = False

Dim oBook
Set oBook = oExcel.Workbooks.Open(src_file)

oBook.SaveAs dest_file, csv_format

oBook.Close False
oExcel.Quit

Step 3: Sends the .csv files via WS SFTP Pro and move the sent .csv files to an archive folder

"C:\Program Files (x86)\Ipswitch\WS_FTP 12\wsftppro.exe" -s "local:D:\Batch\%DATE:~4,2%%DATE:~7,2%%DATE:~-4%_*.csv" -d "eMoney:/Ethic:/" -move "D:\Cognos Reports\FTPOUT\eMoney Archive\" -quiet

Step 4: Delete the .xlsx files

DEL "D:\Batch\%DATE:~4,2%%DATE:~7,2%%DATE:~-4%_Client_Ethic.xlsx"
DEL "D:\Batch\%DATE:~4,2%%DATE:~7,2%%DATE:~-4%_InvestmentDepositAccounts_Ethic.xlsx"
DEL "D:\Batch\%DATE:~4,2%%DATE:~7,2%%DATE:~-4%_LiabilityAccounts_Ethic.xlsx"
DEL "D:\Batch\%DATE:~4,2%%DATE:~7,2%%DATE:~-4%_Relationship_Ethic.xlsx"

When I run the process manually by running the .bat as admin it works just fine (so the code shouldn't be the issue). However, when I try to run it from Task Scheduler with the same admin account it completes step 1 and then just hangs in Running status with no errors. So I can see the xlsx files were pulled over, but there are no csv files converted.

I've even tried splitting the task into 4x separate bat files so it's guaranteed to finish each step before attempting the next... so it's not trying to convert before the files transfer over the network. I've also tried changing the task to run cmd and provide /c as the parameter. Same result.

We've had several issues with the Office install on this box and have tried several fresh installs, but it still isn't working via task scheduler. We've tried both Excel 2016 and Office 365 with disabling automatic updates.

Server is 2012R2 if that matters. I'm scratching my head on this one...

6
  • Please edit and include all the code you are using.
    – DavidPostill
    Aug 14, 2020 at 15:23
  • Is it possible that Task Scheduler is not allowed to launch Excel? if so, is there a way to correct this?
    – edanowski
    Aug 14, 2020 at 19:11
  • 2
    Files with extensions (like VBS and BAT) are handled through the explorer with a environment variable called PATHEXT. PATHEXT implies that the file extensions on the list can be launched with the associated application in the registry. Try using cmd.exe as your application and '/c "batch_file_path' as your parameters to launch this whole thing and you might have more luck. .bat and .vbs files ARE NOT technically executable.. they require a host (cmd or windows scripting host) to run. Aug 14, 2020 at 20:00
  • @SeñorCMasMas This is actually how I have it set up currently. I have the task scheduler calling cmd.exe with "/c <batch file>" and starting in <batch file folder>. I also have the batch file split into 4x seperate sequential files so it is guaranteed to run in order. Same result. It grabs the files over the network and then hangs without converting to CSV. It works perfectly if I right click and run the bat file, but Task Scheduler hangs.
    – edanowski
    Aug 14, 2020 at 20:27
  • OK.. well then all I can suggest is to narrow your field (move it all to windows scripting host) and log your way to figure out what is wrong. I have had to do this when debugging the task scheduler. It sucks.. but it's the only way I know. Aug 16, 2020 at 22:39

1 Answer 1

1

So this is apparently a known issue with console permissions when trying to execute certain versions of MS Office applications via Task Scheduler using certain versions of MS Windows. Apparently our server fit that bill running 2012 R2 and trying both Office 2016 and 365.

The resolution is to add the following folder: c:\windows\syswow64\config\systemprofile\desktop and it will work perfectly.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.