2

I am experiencing an issue with my VBScript.

I have below code working fine when I run it directly (by double clicking on it, it's triggering wscript). I have some 1000 .xls files in FilePath.

FilePath = "c:\test"

Set SourceFolder = objFSO.GetFolder(FilePath+"\")

For Each file In SourceFolder.Files
    If Right(LCase(file.Name), 4)=".xls" Then
        OutFile = OutFilePath + "\" + Left(file.Name, Len(file.Name)-4) + ".csv"
        Set ExcelObject = oExcel.Workbooks.Open(file.Path)
        If Err.Number <> 0 Then objLogFile.WriteLine "Exception occured(1): " + Err.Decscription

        RowCount = oExcel.ActiveWorkbook.Sheets(1).UsedRange.Rows.Count
        ColumnCount = oExcel.ActiveWorkbook.Sheets(1).UsedRange.Columns.Count

        For i=1 To RowCount
            For j=1 To ColumnCount
                inText = ExcelObject.Sheets(1).Cells(i,j).Value
                inText = Replace(inText, vbCr, " ")
                inText = Replace(inText, vbLf, " ")
                inText = Replace(inText, ",", " ")
                ExcelObject.Sheets(1).Cells(i,j).Value = inText
            Next
        Next
        ExcelObject.SaveAs OutFile, 6
        ExcelObject.Close False
    End If
Next

If I schedule the same script in the Windows Task Scheduler, it is not throwing any error also, its just running (I can see wscript running in the Task Manager).

I tried to capture what error it is throwing, but I couldn't capture. After writing some log next to every line, I got to know that it is giving error in the below line.

Set ExcelObject = oExcel.Workbooks.Open(file.path)

I tried to capture error by putting the statements On Error Resume Next and below If condition (handled all the ObjLogFile object related code)

If Err.Number <> 0 Then objLogFile.WriteLine "Exception occured(1): " + Err.Decscription

Still it is not capturing the error.

Please help me, how to proceed with the error with the following line. Though it is working fine when I run directly, it's not working when I schedule.

Set ExcelObject = oExcel.Workbooks.Open(file.path)
Ansgar Wiechers
  • 193,178
  • 25
  • 254
  • 328
Vamsi
  • 31
  • 3
  • 1
    Off hand my guess would be that it is trying to open a window (for excel) but it is not allowed to because it's a scheduled task. – RBarryYoung Jul 09 '16 at 14:05
  • You might find this answer helpful, http://stackoverflow.com/questions/32194129/vbscript-to-open-an-excel-file-in-windows-10-via-task-scheduler?rq=1 – RBarryYoung Jul 09 '16 at 14:08
  • Thank you RBarryYoung, I tried with Cscript also, It didn't work. If I see the process in Task Manager, it is running with my user ID, but not showing until I select show process for all users. May be it is running with another user?? there Excel not installed??, how to include, excel run time libraries to my script? – Vamsi Jul 09 '16 at 15:30
  • Show your scheduled task settings. Also, your code doesn't show how you instantiate `oExcel`. Did you set `oExcel.DisplayAlerts = False`? – Ansgar Wiechers Jul 09 '16 at 15:33
  • Set oExcel = CreateObject("Excel.Application") I have used this to instantiate OExcel – Vamsi Jul 09 '16 at 15:54
  • Yes, it's probably running under another username. You can add columns to Task Manager to see which. Not sure, but you should be able to tell The Scheduler what account to run under. – RBarryYoung Jul 09 '16 at 20:37

1 Answers1

1

Since I am using Excel Objects, which are interactive, I cann't give the option in the scheduler "Run whether user is logged on or not"

I changed this option to "Run only when user is logged on"

Its working now. Thank you all for your suggestions.

Vamsi
  • 31
  • 3