App Store Wiki

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== Automating Excel Reports ======

To use the excel add-in to periodically produce reports you can use a power shell script and a scheduled task to open an Excel file, recalulate the formulae and save the result as PDF.

You will need:
  * A copy of the add-in XLL
  * A Excel file you want to calulate periodically
  * Power shell (you can install the latest version of Power Shell here: [[https://learn.microsoft.com/en-us/powershell/scripting/install/installing-powershell-on-windows?view=powershell-7.4]])

The Excel file you use should use relative time ranges (e.g. "*", "*-10d") or use excel functions like TODAY() ad inputs to data requests. If you were to use absolute time stamps the resulting report would always be the same.

===== Script =====

You can see and example script below. This script expects the Excel add-in XLL (add-in.xll) and Excel file (Report.xlsx) to be in the scripts working directory. The PDF otuput will be saved as "output/Exported_{current_time}.pdf"

<code>
# Path to the Excel file you want to open
$excelFilePath = "$PSScriptRoot\Report.xlsx"

# Output directory for the PDF
$outputDirectory = "$PSScriptRoot\output"

$addInPath = "$PSScriptRoot\add-in.xll"

# Ensure the output directory exists
If (-Not (Test-Path $outputDirectory)) {
    New-Item -ItemType Directory -Force -Path $outputDirectory
}

# Create an instance of Excel
$excel = New-Object -ComObject Excel.Application
$excel.Visible = $true # You can change this to $true if you want to see Excel

try {
    # Open the workbook
    $workbook = $excel.Workbooks.Open($excelFilePath)

    # Load the .xll add-in
    $loadResult = $excel.RegisterXLL($addInPath)

    # Check if the add-in was loaded successfully
    if ($loadResult -eq $true) {
        Write-Host "Add-in loaded successfully."
    } else {
        Write-Host "Failed to load add-in."
    }


    # Recalculate all formulas in the workbook
    $excel.CalculateFullRebuild()

    $excel.Calculate()

    # Give time for fomulae to be recalulated
    Start-Sleep -Seconds 20

    # Generate a file name with the current timestamp
    $timestamp = Get-Date -Format "yyyy-MM-dd_HH-mm-ss"
    $pdfFileName = "Exported_$timestamp.pdf"
    $pdfFilePath = Join-Path -Path $outputDirectory -ChildPath $pdfFileName

    # Save the workbook as PDF
    $workbook.ExportAsFixedFormat(0, $pdfFilePath)

    # Close the workbook without saving changes
    $workbook.Close($false)
}
finally {
    # Quit Excel application
    $excel.Quit()

    # Release COM objects
    [System.Runtime.InteropServices.Marshal]::ReleaseComObject($workbook) | Out-Null
    [System.Runtime.InteropServices.Marshal]::ReleaseComObject($excel) | Out-Null
    [System.GC]::Collect()
    [System.GC]::WaitForPendingFinalizers()
}

Write-Host "Excel file has been successfully converted to PDF: $pdfFilePath"

</code>

===== Scheduled Task =====

You can schedule the script to be run periodically (e.g. daily) using the Windows Task Scheduler.

Use the start menu to search for "Task Scheduler"
{{ :excel:search.png?400 |}}

Click "Create Task" in the right panel.

{{ :excel:create-task.png?400 |}}

Enter a name for the task. Select the "Triggers" tab the click "New" to setup when the report will be run.

{{ :excel:triggers.png?400 |}}


Then select the "Actions" tab.

{{:excel:example-action.png?400 |}}

Click "New". Make sure "Start a Program" is selected for "Action".
In the Program/Script field enter the path to the Power shell exe on your system.
<code>
C:\Program Files\PowerShell\7\pwsh.exe
</code>

Set the arguments to the path to the script e.g.
<code>
C:\dev\excel-report\export-pdf.ps1
</code>

Set the "start in" field to the wolder with the script, add-in and excel file. e.g.
<code>
C:\dev\excel-report
</code>