Refreshing Excel connections with Powershell

Welcome Forums General PowerShell Q&A Refreshing Excel connections with Powershell

This topic contains 1 reply, has 2 voices, and was last updated by

 
Keymaster
6 months, 1 week ago.

  • Author
    Posts
  • #102128

    Participant
    Points: 1
    Rank: Member

    I have a couple questions about a script I am working on (see below). In short, the script updates connections in all Excel files in a given directory, with a couple exceptions.

    First, it seems that when Powershell executes a command to open, refresh, save, etc a workbook, it doesn't necessarily wait for that task to complete before moving on to the next task. I have some Start-Sleep commands inserted to add delays, but it doesn't seem to work across the board, as some files are still throwing some errors. At the least, Start-Sleep needs to be set to a high delay to accommodate the larger files, which adds a lot of unnecessary delay to the smaller files, subsequently causing the script to take a lot longer to run. For the refresh task, I added "While ($ExcelWorkbook.ODBCConnection.Refreshing) {Start-Sleep -Seconds 1}", which might work for other tasks, but I'm not sure what the syntax would need to be for other tasks, such as opening, saving, etc.

    Secondly, I occasionally see some errors like the following, but I'm not entirely sure why. They don't always show up every time I run the script, and are not always on the same files. I'm guessing some of them might be due to Powershell performing a task before Excel has finished the previous task, but I'm not sure, as I am less familiar with working with Excel in Powershell. If it makes any difference, they seem to start showing up after the script has gone through a few dozen files successfully, so maybe something is being left open from each file and taxing the system?

    Thanks in advance for the help,

    Andrew

    ********************************************************************************************************************
    Method invocation failed because [System.__ComObject] does not contain a method named 'Save'.
    At C:\Users\administrator.NHFM\Desktop\Refresh-ExcelConnections.ps1:44 char:9
    + $ExcelWorkbook.Save()
    + ~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : InvalidOperation: (Save:String) [], RuntimeException
    + FullyQualifiedErrorId : MethodNotFound
    _____________________________________________________________________________________________
    You cannot call a method on a null-valued expression.
    At C:\Users\administrator.NHFM\Desktop\Refresh-ExcelConnections.ps1:59 char:9
    + $Excel.Workbooks.Close()
    + ~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull
    _____________________________________________________________________________________________
    Call was rejected by callee. (Exception from HRESULT: 0x80010001 (RPC_E_CALL_REJECTED))
    At C:\Users\administrator.NHFM\Desktop\Refresh-ExcelConnections.ps1:44 char:9
    + $ExcelWorkbook.Save()
    + ~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : OperationStopped: (:) [], COMException
    + FullyQualifiedErrorId : System.Runtime.InteropServices.COMException
    _____________________________________________________________________________________________
    Exception calling "Quit" with "0" argument(s): "Call was rejected by callee. (Exception from HRESULT: 0x80010001
    (RPC_E_CALL_REJECTED))"
    At C:\Users\administrator.NHFM\Desktop\Refresh-ExcelConnections.ps1:63 char:5
    + $Excel.Quit()
    + ~~~~~~~~~~~~~
    + CategoryInfo : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : COMException

    ********************************************************************************************************************
    # Set library path variable
    $LibraryPath = “\\server\path”

    # Open Excel
    $Excel = New-Object -ComObject Excel.Application
    Start-Sleep -Seconds 5

    # Set visibility of Excel and alerts
    $Excel.Visible = $False
    $Excel.DisplayAlerts = $False

    # Get Excel files
    $AllExcelFiles = Get-ChildItem $LibraryPath | Where {($_.Name -like "*.xls*") -and ($_.Name -notlike "TEST*")}

    # Settings for PDF Export
    $ExportPDFList = Join-Path -Path $LibraryPath -ChildPath "PDF Exports\~ExportPDFList.txt"
    $xlFixedFormat = "Microsoft.Office.Interop.Excel.xlFixedFormatType" -as [type]

    # Refresh each Excel file in $LibraryPath
    foreach ($File in $AllExcelFiles)
    {
    Write-Host $File.Name -ForegroundColor Green
    $WorkbookPath = $File.Fullname

    # Open the workbook
    $Timestamp = Get-Date -DisplayHint Time
    Write-Host " "$($Timestamp) "- Opening..."
    $ExcelWorkbook = $Excel.Workbooks.Open($WorkbookPath)
    Start-Sleep -Seconds 10

    # Refresh All the pivot tables data
    $Timestamp = Get-Date -DisplayHint Time
    Write-Host " "$Timestamp "- Refreshing..."
    $ExcelWorkbook.RefreshAll()
    While ($ExcelWorkbook.ODBCConnection.Refreshing) {Start-Sleep -Seconds 1}

    # Save the file
    $Timestamp = Get-Date -DisplayHint Time
    Write-Host " "$Timestamp "- Saving..."
    $ExcelWorkbook.Save()

    # Export PDF
    $Export = Select-String -Path $ExportPDFList -Pattern $File.Name
    if ($Export -ne $null)
    {
    $Timestamp = Get-Date -DisplayHint Time
    Write-Host " "$Timestamp "- Exporting PDF..."
    $PDFPath = Join-Path -Path $LibraryPath -ChildPath ("PDF Exports\$($File.BaseName)" + ".pdf")
    $ExcelWorkbook.ExportAsFixedFormat($xlFixedFormat::xlTypePDF, $PDFPath)
    }

    # Close file
    $Timestamp = Get-Date -DisplayHint Time
    Write-Host " "$Timestamp "- Closing..."
    $Excel.Workbooks.Close()
    }

    # Close Excel
    $Excel.Quit()
    Get-Process -Name "*Excel*" | Stop-Process

    #Release Excel object from memory
    [System.Runtime.Interopservices.Marshal]::ReleaseComObject($Excel)

  • #102170

    Keymaster
    Points: 1,704
    Helping HandTeam Member
    Rank: Community Hero

    Your assumptions are more or less correct. Unfortunately, that COM object is -ancient-, and wrapping it in .NET in 2000 or so didn't do much to make it less weird. It -ought- to implement callbacks for long-running tasks, but doesn't, and the whole thing is entirely deprecated so we're not likely to see any improvements. You're unfortunately stuck with whatever goofy workarounds you can come up with to make it less ornery.

The topic ‘Refreshing Excel connections with Powershell’ is closed to new replies.