Powershell script wont work when i run using windows schduler

PowerShell Help
1

The actual code loads up a excel spread sheet and then maniputes it with the data that it pulls via the Rest API call, The script works comepletly fine when i run in the ISE, UI or as a batchfile, but when i try to execute the same script via the windows schduler,

  1. If the windows schduler calls the powershell scripts directly - The powershell process starts, but i dont see excel process in the memory, the script fails while opening the excel spreadhseet, Error exception dosent seem to report what the exception is
  2. If the windows schduler calls the batch file - The powershell process starts, i do see the excel loading in the memory, but still fails while loading the spreadsheet, Error exception dosent seem to report what the exception is.

Would appreciate any help on this, Thanks

##############################################################################################################
#Function Name: ErrorHandler
#Inputs:        Error Type(Info, Warning or Error), Error String
#Output:        None
#Description:   If the error type is 'Error' then captures the last known exceptions reported and appends to the file.
#               If the error type is something else then the error message is appended to the log file.
##############################################################################################################
Function ErrorHandler ($errType, $ErrorString)
{
    $timestamp = Get-Date -Format o
    if ($errType -eq 'Error')
    {
        $errorstr = $timestamp + ' : ' + $errType + $ErrorString + $Error[0].Exception 
    }
    else
    {
        $errorstr = $timestamp + ' : ' + $errType + ' - ' + $ErrorString
    }
    $errcounter = 0
    $maxerrcounter = 5
    do
    {
        try
        {
            $errorstr | Out-File $ErrFile -Append
            break
        }
        catch
        {
            Start-Sleep 10
            $errcounter++
        }
    }while ($errcounter -le $maxerrcounter)
}


#########################################
#Initialization
#########################################
#Folder Name
$Fldr = 'C:\RPA\LRP_Report_Automation\'
#Error file name
$ErrFile = $Fldr + 'LPR.log'
#LPR Report master spreadsheet
$excelFile = $Fldr + "\Report\LRP_Empty.xlsb"
$xl_file_password = "test123"
$dirForRAW_CSVFiles = $Fldr + 'Report\RawData\'
#creating a hash table to store the relationship betwen the API columns and the Excel columns
$excelparamhash=@{}
ErrorHandler 'Info' "Starting the Module Import."

#########################################
#Intialize Excel object
#########################################

foreach ($user in (Get-ChildItem -Path C:\Users)) {
    $Env:PSModulePath += ";$($user.FullName)\Documents\WindowsPowerShell\Modules"
}

#Get-Module "C:\Program Files\WindowsPowerShell\Modules\ImportExcel" -ListAvailable | Import-Module -Force #-Verbose
#Get-Module ImportExcel -ListAvailable | Import-Module -Force #-Verbose
Import-Module -Name ImportExcel -Force
# Load the Excel COM object
$Targetexcel = New-Object -ComObject "Excel.Application"
$Targetexcel.Visible = $false
$Targetexcel.DisplayAlerts = $false

# Open the LPR report master file
try
{
    $workbook = $Targetexcel.Workbooks.Open($excelFile, $false, $false, [Type]::Missing, $xl_file_password, $xl_file_password, $true)
}
catch
{
    ErrorHandler 'Error ' "Finished the Module Import."
}
#save the LRP report workbook
$workbook.Save()
#close the LRP workbook
$workbook.Close()
$Targetexcel.Quit()
2

Daaku,
Welcome to the forum. :wave:t3:

Using Office components from a scheduled task will not work. The Office Apps need an interactive session.

But there’s an easy solution. Use the great module from Doug Finke

With that you don’t even need an installed Excel. :point_up_2:t3:

Here you can see some things you can do with it:

1 Like
3

Thanks for the information.
The spreadsheet is in the binary format (it is a very large spreadsheet), Import-Excel and the Export-Excel seems to be having issue working with the XSLB file formation. Also another challenge that i see with Export Excel is i can onlt clear a complete sheet not a section, When i am trying to move content from one xslb file to another on a day to day basis the row counts change based on the data, One day it could be 10K and the next day it could be 9K, I dont see an option with Export excel to clear data from the unwanted rows.

4

If it’s that big and complex Excel might be the wrong tool for the job. But since using the Office apps (Excel) headless is not supported by MSFT you’re pretty much out of luck, I’d say. :man_shrugging:t3:

How about converting it to default XLSX? What do you do actually with this sheet? What’s the purpose of it? How it is used when you changed it with your script?

I actually don’t have that much experience with that module. So I actually don’t know if that’s the case. But how about removing the worksheet and recreating it with the new data? :man_shrugging:t3:

5

The spreadhseet is for the executive team, The spread sheet has about 10 sheets, The data is gathered across many systems, powershell grabs the data and updates some columns on five sheets, The worksheet has tonns of formula and Pivot charts, powershell suppose to run everyday and update certian columns with the data that we gather using the API calls, and then se upload the spreasheet to the sharpooint which the executive team has access to. The spreadsheet is also macro heavy, The size of the when i save it as xsls is around 500MB, if i save it in the binary format, it is around 160MB.
Not sure if i should look into Python or anyother automation to run this under the windows schduler

6

Hmmm … ok … that sounds for me like you’re using the wrong tool for the job. :man_shrugging:t3:

How about using Excels ability to import data directly from other sources. In the simplest case that could be CSV files you update with PowerShell. This way you can keep all your formatting, Pivot charts, formulas and so on and the data would update themselfs with the current data when the executive team members open the file.

1 Like
7

If possible, another route would be to have Powershell managing data source (e.g. SQL, Azure, etc.) and the spreadsheet is using that to populate data. Trying to manage data population in a file that someone could even have open is bound to have issues. There are multiple options for getting external data in the Data tab in Excel. As Olaf stated, you could manage external CSV flat files or if the spreadsheet could be opened off-network you may need to see if a cloud source is viable.

2 Likes