Executing vbs with task scheduler to automatically refresh data save excel file failed RRS feed

  • Question

  • The manual execution is successful. If it is placed in the scheduled task, the operation fails. It is also successful to schedule the task to operate a single file. Can you help me to see if there is a problem with the code or where?

    Use is: automatically refresh all excel external data of the specified folder, and save as file name + yesterday date

    Dim date1, date2, xlApp, xlBook 
    date1 = DateAdd("d",-1,date)
    date2 = DatePart("yyyy",date1) & Right("0" & DatePart("m",date1), 2)  & Right("0" & DatePart("d",date1),2)
    Set xlApp = CreateObject("Excel.Application") 
    xlApp.Visible = true
    Dim path 
    path = "C:\Users\admin\office365\BI\everyday" 
    Dim fso, fle , Name , Format 
    Set fso = CreateObject("Scripting.FileSystemObject") 
    For Each fle In fso.GetFolder(path).Files 
        Set xlBook = xlApp.Workbooks.Open(fle.Path)
        Name = Mid(,1,instrrev(,".")-1) 
        wscript.sleep 2*60*100
        NewFName = "C:\Users\admin\office365\BI\" & Name&date2&".xlsx"
        xlBook.SaveAs NewFName

    • Edited by go28 Thursday, August 29, 2019 10:08 AM
    Thursday, August 29, 2019 9:57 AM

All replies