时间戳

首页 > 解决方案 > Powershell选择已打开的excel工作表的整行

excel - Powershell选择已打开的excel工作表的整行

问题描述

我正在编写一个小 GUI 来简化一些 excel 文档的工作。它有一个按钮可以启动此功能以打开 excel 文件并选择所需的行。

Function open_bible_file
{


$Excel = New-Object -ComObject excel.application

$Excel.WindowState= "xlMaximized"

$Excel.visible = $true

$WorkBook = $Excel.Workbooks.Open($SCOMBibleFile)

$Worksheet = $Workbook.WorkSheets.item("(1) Alerts")

$worksheet.activate()

$Range = $Worksheet.Cells.Item($excelrow,1).EntireRow

[void]$Range.Select()
}

}

它打开文件并选择应该的行。但是当我再次使用此按钮时，它只会再次打开 excel 并再次选择另一行。当我尝试做另一个按钮来选择行时，它对已打开的工作表一无所知。我怎样才能绕过它？

标签： excelpowershell

解决方案

代码应检查 Excel 是否已在运行，如果是，则检查工作簿（文件$SCOMBibleFile）是否存在。如果是这种情况，请重新激活 Excel，否则启动一个新实例。

这应该有效：

function open_bible_file {
    [CmdletBinding()]
    Param(
        [Parameter(Mandatory = $true, Position = 0)]
        [string]$Path,

        [Parameter(Mandatory = $false, Position = 1)]
        [int]$RowToSelect = 1
    )

    $WorkBook = $null
    # check if Excel is already open
    try {
        # Note: this only gets the excel instances that were started 
        #       by the same user that runs this powershell function.
        $Excel = [Runtime.Interopservices.Marshal]::GetActiveObject('Excel.Application')
        # test if the $Path workbook is present in this Excel instance
        foreach ($wb in $Excel.Workbooks) {
            if ($wb.FullName -match [regex]::Escape($Path)) {
                $WorkBook = $wb
                break
             }
        }
    }
    catch {
        # Excel wasn't opened yet, create a new instance
        $Excel = New-Object -ComObject Excel.Application
    }

    if (!($Excel))    { Write-Error "Error opening Excel"; return }

    if (!($WorkBook)) { 
        $WorkBook = $Excel.Workbooks.Open($Path)
    }

    # see https://docs.microsoft.com/en-us/office/vba/api/excel.xlwindowstate
    $xlMaximized = -4137

    $Excel.Visible = $true
    $Excel.WindowState = $xlMaximized
    $Excel.ActiveWindow.Activate()

    $Worksheet = $Workbook.WorkSheets.item("(1) Alerts")
    $worksheet.activate()
    $Range = $Worksheet.Cells.Item($RowToSelect, 1).EntireRow
    [void]$Range.Select()
}


$SCOMBibleFile = '<PATH TO YOUR .xlsx FILE>'
open_bible_file -Path $SCOMBibleFile -RowToSelect 3

如您所见，我已将open_bible_file函数更改为获取参数。第一个 ( -Path) 是您为其指定要打开的文件名的位置。第二个 ( -RowToSelect) 是您要选择的行号。

希望这可以帮助

推荐阅读