locked
PowerShell script doesn't work in Task Scheduler in Windows Server 2016 RRS feed

  • Question

  • 1) I have seen a lot of topics like that, but don't find the solution.

    I need to start powersheel script every 5 minutes from Administrator with highest privileges:

    Action is 

    Start program

    C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe

    -File "C:\Work\ConvertAndUpload.ps1"

    In the history - task has been done. But I see that script have not been work. Screenshot from history:

    2) As a second way to do this i have tried to create .bat file with start this powershell script and start this .bat from Task Scheduler. It doesn't work too (without errors, just ps-script not really starting).

    3) I have tried to create new task from powershell console (to do from SYSTEM):

    It is working without errors too, but still doesn't really do powersheel script.

    Powershell script is very easy - it converts .xls file to .csv and send it ftp. Code of this script:

    $source = 'C:\Work\Test'

    Function ExcelCSV ($File)
    {
        $Excel = New-Object -ComObject Excel.Application
        $Excel.Visible = $false
        $Excel.DisplayAlerts = $false
        $wb = $Excel.Workbooks.Open($File.FullName)

    foreach ($ws in $wb.Worksheets)
            {
            $ws.SaveAs(($Files.FullName -replace ".xls$","") + ".csv", 6)
            }
            $wb = $Excel.Workbooks.Close()
            $Excel.Quit()
    }

     Foreach ($Files in (Get-ChildItem -Path $source -Filter "*.xls"))
    {
        ExcelCSV($Files)
    }
    $FileToUpload = "C:\Work\Test\test.csv"
    $ftp = 'ftp://login:password@ftpaddress:2225/test.csv'
    $webclient = New-Object System.Net.WebClient
    $uri = New-Object System.Uri($ftp)

    $webclient.UploadFile($uri, $FileToUpload)

    exit 0

    Please, help, I just don't know what to do more.

    P.S. It is new account here, so I can't contain images in body, so I will try put them in comments.


    • Edited by Vjik_Zinger Saturday, August 10, 2019 2:58 PM
    Saturday, August 10, 2019 11:49 AM

Answers

  • The following would be easier for you to understand and debug:

    $source = 'C:\Work\Test'
    $webclient = New-Object System.Net.WebClient
    $excel = New-Object -ComObject Excel.Application
    $excel.Visible = $false
    $excel.DisplayAlerts = $false
    
    Try{
        $xlsfiles = Get-ChildItem -Path $source -Filter *.xls
        Foreach ($file in $xlsfiles) {
            $wb = $excel.Workbooks.Open($file.FullName)
            $outpath = Split-Path $file -Parent
            foreach ($ws in $wb.Worksheets) {
                $csvname = $ws.Name + '.csv'
                $saveas = Join-Path $outpath $csvname
                $ws.SaveAs($saveas)
                $uri = 'ftp://login:password@ftpaddress:2225/' + $csvname
                $webclient.UploadFile($uri, $saveas)
            }
            $wb.Close()
        }
        $excel.Quit()
    }
    Catch{
        exit 99
    }
    

    Notice that it is clean and simple.  ALl scope issues are gone and the variables are easier to identify.

    Once this works you can alter it to make it fancy if you like.  Changes you make will be easier to fix when they fail.


    \_(ツ)_/

    Saturday, August 10, 2019 6:24 PM

All replies

  • This doesn't work from task scheduler, but does it work when you run it manually?

    You don't seem to have a command to actually upload the file.  For example:

    https://docs.microsoft.com/en-us/dotnet/api/system.net.webclient.uploadfile?view=netframework-4.8

    Saturday, August 10, 2019 12:52 PM
  • Yes, it works with manual launch from administrator. I forgot to copy here last 2 strings of script:

    $webclient.UploadFile($uri, $FileToUpload)
    exit 0

    Saturday, August 10, 2019 2:57 PM
  • Your code has numerous errors and only ever uploads one file.  You also have no error checking so errors will be lost.

    If you post code correctly and properly format the code some of the errors will become obvious.

    CmdLets are NOT called with ().

    The following is wrong even if it seems to work.

    ExcelCSV($Files)

    PS does not call functions this way.  Use this:

    ExcelCSV $Files

    There is no point of placing "exit 0" at the end of a script.

    You also failed to post what is not working. Just saying it doesn't work is not helpful.

    Here is what your code looks like.  Note that it never uploads the converted files and that any errors will likely crash the script.

    $source = 'C:\Work\Test'
    
    Function ExcelCSV ($File) {
        $Excel = New-Object -ComObject Excel.Application
        $Excel.Visible = $false
        $Excel.DisplayAlerts = $false
        $wb = $Excel.Workbooks.Open($File.FullName)
        
        foreach ($ws in $wb.Worksheets) {
            $ws.SaveAs(($Files.FullName -replace ".xls$", "") + ".csv", 6)
        }
        $wb = $Excel.Workbooks.Close()
        $Excel.Quit()
    }
    
    Foreach ($Files in (Get-ChildItem -Path $source -Filter "*.xls")) {
        ExcelCSV($Files)
    }
    
    $FileToUpload = "C:\Work\Test\test.csv"
    $ftp = 'ftp://login:password@ftpaddress:2225/test.csv'
    $webclient = New-Object System.Net.WebClient
    $uri = New-Object System.Uri($ftp)
    
    $webclient.UploadFile($uri, $FileToUpload)
    
    exit 0
    


    \_(ツ)_/

    Saturday, August 10, 2019 6:09 PM
  • Looking deeper it appears that you copied much of the code but have edited it badly. YOu use "$file" in some places and "$files in others.

    I recommend removing the function and just calling the code directly.  This will help you see the mistakes at a prompt before trying to schedule this.


    \_(ツ)_/

    Saturday, August 10, 2019 6:16 PM
  • The following would be easier for you to understand and debug:

    $source = 'C:\Work\Test'
    $webclient = New-Object System.Net.WebClient
    $excel = New-Object -ComObject Excel.Application
    $excel.Visible = $false
    $excel.DisplayAlerts = $false
    
    Try{
        $xlsfiles = Get-ChildItem -Path $source -Filter *.xls
        Foreach ($file in $xlsfiles) {
            $wb = $excel.Workbooks.Open($file.FullName)
            $outpath = Split-Path $file -Parent
            foreach ($ws in $wb.Worksheets) {
                $csvname = $ws.Name + '.csv'
                $saveas = Join-Path $outpath $csvname
                $ws.SaveAs($saveas)
                $uri = 'ftp://login:password@ftpaddress:2225/' + $csvname
                $webclient.UploadFile($uri, $saveas)
            }
            $wb.Close()
        }
        $excel.Quit()
    }
    Catch{
        exit 99
    }
    

    Notice that it is clean and simple.  ALl scope issues are gone and the variables are easier to identify.

    Once this works you can alter it to make it fancy if you like.  Changes you make will be easier to fix when they fail.


    \_(ツ)_/

    Saturday, August 10, 2019 6:24 PM
  • Note also that starting and quitting Excel in a loop is a very bad idea and will lead to potentially hundreds of copies of Excel stuck in memory.

    Office products are not supported when run as tasks, services orr without a full interactive session.  They do not behave correctly and may cause system issues like running out of memory.  Microsoft explicitly warns about this and has since the earliest days of office.

    See: https://support.microsoft.com/en-us/help/257757/considerations-for-server-side-automation-of-office

    When Office is run under Task Scheduler it is running under an "NT Service" which is specifically disallowed for this product.  Use it at your own risk.


    \_(ツ)_/

    Saturday, August 10, 2019 6:30 PM
  • Hi,

    Was your issue resolved?

    If you resolved it using our solution, please "mark it as answer" to help other community members find the helpful reply quickly.

    If you resolve it using your own solution, please share your experience and solution here. It will be very beneficial for other community members who have similar questions.

    If no, please reply and tell us the current situation in order to provide further help.

    Best Regards,

    Lee


    Just do it.

    Friday, September 6, 2019 12:29 PM