none
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(fle.name,1,instrrev(fle.name,".")-1) 
        xlBook.RefreshAll  
    
        wscript.sleep 2*60*100
    
        NewFName = "C:\Users\admin\office365\BI\" & Name&date2&".xlsx"
    
        xlBook.SaveAs NewFName
    
        xlBook.Close 
    
    Next 
    
    
    xlApp.Quit


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

All replies