如何在Powershell中捕获Excel文件的宏错误?

问题描述

我有一个计划的PowerShell脚本，可以打开Excel，打开文件并运行宏.如果出现错误，我需要在外壳中显示它并中断脚本.(我无法让脚本在等待用户输入时挂起)

I have a scheduled PowerShell script that opens Excel, opens a file and runs a macro.If an error arises, I need to display it in the shell and interrupt the script. (I can not afford the script to hang while waiting for user input)

这是我当前的脚本(在脚本的前面定义了$ excelDataPath)

Here is my current script ($excelDataPath is defined earlier in the script)

$Excel = New-Object -ComObject "Excel.Application"
$Excel.DisplayAlerts = $false
$Excel.AskToUpdateLinks = $false
$Excel.Visible = $true

$Workbook = $Excel.Workbooks.Open($excelDataPath)
$Excel.Run("Macro1")
$Workbook.Save()
$Workbook.Close($true)

谢谢

推荐答案

我认为从PowerShell的角度来看，这将捕获并显示已发生的异常:

I think from the PowerShell point of view, this will catch and display the exception that has occurred:

try {
    $Excel = New-Object -ComObject "Excel.Application"
    $Excel.DisplayAlerts = $false
    $Excel.AskToUpdateLinks = $false
    $Excel.Visible = $true

    $Workbook = $Excel.Workbooks.Open($excelDataPath)
    $Excel.Run("Macro1")
    $Workbook.Save()
    $Workbook.Close($true)
    $Excel.Close()
}
catch {
    $exception = $_.Exception
    while ($exception.InnerException) {
        $exception = $exception.InnerException
    }
    # Throw a terminating error.
    throw $exception
}
finally {
    # IMPORTANT! Always release the comobjects used from memory when done
    if ($Workbook) { [System.Runtime.Interopservices.Marshal]::ReleaseComObject($Workbook) | Out-Null }
    if ($Excel)    { [System.Runtime.Interopservices.Marshal]::ReleaseComObject($Excel) | Out-Null }
    [System.GC]::Collect()
    [System.GC]::WaitForPendingFinalizers()
}

但是，对于您未显示给我们的VBA宏代码，您将必须通过 VBA错误声明.尤其是 Err.Raise 和 Throw 语句.

However, as for the VBA macro code you did not show us, you will have to check to see what it does wit any errors via VBA On Error Statement. Especially the Err.Raise and Throw statements.

这篇关于如何在Powershell中捕获Excel文件的宏错误?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！