PowerShell中的Excel自动化问题 [英] Problems with Excel automation in PowerShell

问题描述

我正在尝试在Excel报表中自动刷新数据。这里的脚本：

  $ ExcelApp = new-object -ComObject Excel.Application 
 $ ExcelApp.Visible = $ false 
 $ ExcelApp.DisplayAlerts = $ false 
 
 $ Workbook = $ ExcelApp.Workbooks.Open（RefreshTest.xlsx，3，$ false，5，$ null，$ null，$ true ）
 $ Workbook.RefreshAll（）
 $ Workbook.Save（）
 
 $ ExcelApp.Quit（）
  

脚本很好，但是当我尝试直接运行它时，会显示以下错误：

 方法调用失败，因为[System .__ ComObject]不包含名为保存的方法。 
行：7 char：15 
 + $ Workbook.Save<<<< （）
 + CategoryInfo：InvalidOperation：（Save：String）[]，RuntimeException 
 + FullyQualifiedErrorId：MethodNotFound 
  

好奇的是，如果我只运行脚本的顶部（在Save方法之前），然后调用Save方法，它的工作正常。

<你能帮我吗它是针对PowerShell V1和PowerShell V2（与Win7 Beta安装的位）进行了测试。

最好的问候，
Rafa

解决方案

你的逻辑似乎很好，这对我来说很有用（XP SP2和PowerShell v2）。

在你打电话给Save（）之前，只是为了好玩，添加一行来看看打印的内容：

  $ Workbook | Get - 会员*保存* 
  

我尝试了一个空白的文件。你的文件大吗我想知道你是否有一个大文件，也许需要添加一个Start-Sleep -sec 5可能...

I’m trying to automate the refreshing of data in my Excel Reports. Here the script:

$ExcelApp = new-object -ComObject Excel.Application
$ExcelApp.Visible = $false
$ExcelApp.DisplayAlerts = $false

$Workbook = $ExcelApp.Workbooks.Open("RefreshTest.xlsx", 3, $false, 5, $null, $null, $true)
$Workbook.RefreshAll()
$Workbook.Save()

$ExcelApp.Quit()

The script is fine, but when I try to run it directly it shows me the following error:

Method invocation failed because [System.__ComObject] doesn't contain a method named 'Save'.
At line:7 char:15
+ $Workbook.Save <<<< ()
    + CategoryInfo          : InvalidOperation: (Save:String) [], RuntimeException
    + FullyQualifiedErrorId : MethodNotFound

Curiously, if I run just the top part of the script (before the Save method), then call the Save method after, it works fine.

Could you help me? It was tested against PowerShell V1 and PowerShell V2 (the bits installed with Win7 Beta).

Best regards, Rafa

解决方案

Your logic seems to be fine, and this works for me (XP SP2 and PowerShell v2).

Just before you call Save(), just for fun, add this line to see what gets printed:

$Workbook|Get-Member *Save*

I tried with a blank file. Is your file big perhaps? I wonder if you have a large file, and maybe need to add a "Start-Sleep -sec 5" maybe...

这篇关于PowerShell中的Excel自动化问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！