excel - 在事件的 Excel 文档中使用 VBA 从 powershell 连接到 Excel

Question

我有一个非常奇怪的问题。
我有一些名为 merge1-6 的 vba 脚本，它从不同的 Excel 表中提取数据。
这 6 个生成的文本文件使用另一个宏“MergeALL”合并为 1 个文本文件，因此所有内容都在一个字符串上。
到目前为止一切顺利，效果很好。
所以我想制作一个powershell脚本来访问我打开的当前excel文档，并获取J29单元格数据，并将这些数据与值“0 |”合并组合给定的字符串。
我必须拉取单元格数据的实际powershell脚本如下:
宏:调用 BID_MergeXCleanup_TextOut

#This script opens H:\Temp\PublicFolder-powershell\Create-String-For-Email-Fetch.txt and replace empty parts of "domain/users/0|" with "nothing"
#stripping away all entries not needed.  Out-File used as output, with -NoNewline to prevent carriage return inserting empty line.
#$workbook = $excel.Workbooks.Open("H:\Temp\PublicFolder-powershell\Create-String-For-Email-Fetch.xlsm") #open workbook

$xl = [Runtime.InteropServices.Marshal]::GetActiveObject(Excel.Application')
$workbook = $xl.workbooks | ?{$_.FullName -eq "H:\Temp\PublicFolder-powershell\Create-String-For-Email-Fetch.xlsm"}
$workbook.sheets.item(1).activate() #opens the sheet and activates it
$WorkbookTotal=$workbook.Worksheets.item(1) #Set sheet data to variable
$value = $WorkbookTotal.Cells.Item(29, 10)  # Fetches data in Column 10, Row 29 which is the cell reference J29 where domain is. 
$domain = $value.Text  #Fetches "domain/users/" from the Fetch-tab of the excel file, to match the correct domain in use and stores result in the $domain variable
$leftoverdomain = '0|'    #last part of domain string after the domain/user part
$joinedvariables = -join($domain,$leftoverdomain,"") #Joines the above variables together to form "domain/users/0|" as searchtag to replace as leftover bits.

$b = Get-Content -Path "H:\Temp\PublicFolder-powershell\Create-String-For-Email-Fetch.txt" #Opens text file to replace in.

#Next part uses the $joinedvariables variable of merged search parametres and replaces it with "nothing" and saves the file back.

@(ForEach ($a in $b) {$a.Replace($joinedvariables, '')}) | Out-File "H:\Temp\PublicFolder-powershell\Create-String-For-Email-Fetch.txt" -NoNewline
Start-Sleep -Seconds 0.3

### INFO ###
#I used a reference to the EXCEL cell here, instead of directly from the TXT-file as given what domain has been specified, the info in the TEXT file would vary
#But text in J29 would always equal the domain being used, and thus also correnspond with the one in the text file at all times. 
#This way it will automatically always use the correct domain.

因此，如果我使用对 excel 文档的独立引用，

$workbook = $excel.Workbooks.Open("H:\Temp\PublicFolder-powershell\Create-String-For-Email-Fetch.xlsm") 

...并直接打开工作簿，在powershell ISE中，命令成功。但是，如果我调用 PS1 脚本或从 excel 文件中手动运行它，则无法正确执行。这就是为什么我开始寻找如何访问当前的 excel session ，从而引导我使用下一个方法......
如果我使用

$xl = [Runtime.InteropServices.Marshal]::GetActiveObject('Excel.Application') 

要引用当前正在运行的应用程序，当我手动运行时，它从 ISE 和 Excel 内部都成功。
在这两种情况下，只要要修改的 TXT 文件存在并且其中包含要搜索的信息，这项工作就有效。
因此，鉴于我有一个“未修改的文件”，我想通过从另一个宏调用它们来启动这些宏。
这是在同一个 Excel 工作表中用于启动宏的 VBA 代码。

Call BID_1_TextOut 
    Application.Wait Now + TimeSerial(0, 0, 1)
Call BID_2_TextOut 
    Application.Wait Now + TimeSerial(0, 0, 1)
Call BID_3_TextOut 
    Application.Wait Now + TimeSerial(0, 0, 1)
Call BID_4_TextOut 
    Application.Wait Now + TimeSerial(0, 0, 1)
Call BID_5_TextOut 
    Application.Wait Now + TimeSerial(0, 0, 1)
Call BID_6_TextOut 
    Application.Wait Now + TimeSerial(0, 0, 1)
Call BID_MergeALL_TextOut 'Macro MergeALL - Merges all BID-TEXTOUT files to 1 file
    Application.Wait Now + TimeSerial(0, 0, 3)
Call BID_MergeXCleanup_TextOut 'Macro MergeXCleanup - This script replace empty parts of "domain/users/0|" with "nothing"
    Application.Wait Now + TimeSerial(0, 0, 1)

最后两个宏是重要的。
第二个最后一个宏准备要在“BID_MergeXCleanup”中使用的文件，这是我正在调用的 PS1 脚本。
我调用的宏代码如下:

' Script to merge all the BID-TEXT-OUT files into 1 string with UTF32LE encoding.

Sub BID_MergeALL_TextOut()

Set objShell = CreateObject("wscript.Shell")
objShell.Run ("powershell.exe H:\Temp\PublicFolder-powershell\MergeALL_UTF32.ps1")

End Sub

和

'This script opens H:\Temp\PublicFolder-powershell\Create-String-For-Email-Fetch.txt and replace empty parts of "domain/users/0|" with "nothing"
'stripping away all entries not needed.

Sub BID_MergeXCleanup_TextOut()

Set objShell = CreateObject("wscript.Shell")
objShell.Run ("powershell.exe -noexit H:\Temp\PublicFolder-powershell\REPLACE_IN_EmptyDomainUsersPart.ps1")

End Sub

奇怪的是，如果我使用“调用脚本”自动连续运行这些命令，最后一个脚本 总是 失败

如果我之后检查 TXT 文件，我看到只有 '0|' 变量已被删除，因为它似乎无法从 Excel 文档中获取 J29 信息。
如果我在 Excel 中手动逐个运行每个宏，那么它们会正确执行，并且脚本会按照我的意愿执行，不会出现错误
merge1-6-then-all-then-cleanup
因此，据我所知，我的 powershell 脚本能够调用事件的 Excel 工作簿、工作表、单元格、获取数据等等。
...但是我不能通过从另一个宏调用宏来做到这一点？在此示例中，如果我使用“Call BID_MergeXCleanup_TextOut”引用来访问宏，它将失败。
PS:我也尝试“共享”工作簿，以防是权限问题，但在运行 Call-VBA 脚本时仍然报错，但没有连续手动运行每个宏。
知道如何解决这个问题吗？
我有一种感觉，我可能不允许原始 PS1 文件退出 $NULL 表达式，但我不确定如何添加它。
第一个失败的代码行是

$workbook.sheets.item(1).activate() 

我试图在 Powershell ISE 中向它添加 $NULL ，然后它失败了

$null = $workbook.sheets.item(1).activate()     <<<--- Fails with *"You cannot call a method on a null-valued expression."*

而且我也看不到主脚本中有任何未清除的变量。
不确定如何解决这个问题。

Answer 1

我最终得到了什么:
所以 @postanote 在这里建议我使用 COM 而不是 InteropService.Marshal 来访问 Excel。
我最初认为这行不通，因为它会打开工作簿的一个新实例，但是 COM 版本有一种隐藏您运行的实例的方法

$objExcel.Visible = $false

现在，[Runtime.InteropServices.Marshal]::GetActiveObject 版本也有这个值，它似乎有所不同，因为如果我将代码更改为 COM，如下所示:

$objExcel = New-Object -ComObject Excel.Application
$objExcel.Visible = $false
$WorkBook = $objExcel.Workbooks.Open("H:\Temp\PublicFolder-powershell\Create-String-For-Email-Fetch.xlsm")
$WorkSheet = $WorkBook.sheets.item("Fetch")
$domain = $worksheet.Rows.Item(29).Columns.Item(10).Text
$leftoverdomain = '0|'    #last part of domain string after the domain/user part
$joinedvariables = -join($domain,$leftoverdomain,"") #Joines the above variables together to form "domain/users/0|" as searchtag to replace as leftover bits.

$b = Get-Content -Path "H:\Temp\PublicFolder-powershell\Create-String-For-Email-Fetch.txt" #Opens text file to replace in.

@(ForEach ($a in $b) {$a.Replace($joinedvariables, '')}) | Out-File "H:\Temp\PublicFolder-powershell\Create-String-For-Email-Fetch.txt" -NoNewline
Start-Sleep -Seconds 0.3

...从 VBA 调用脚本调用时，脚本执行没有错误...
另一方面，如果我尝试将不可见成员合并到原始代码中，如下所示:

$xl = [Runtime.InteropServices.Marshal]::GetActiveObject('Excel.Application') #opens an already open excel session
$xl.Visible = $false
$workbook = $xl.workbooks | ?{$_.FullName -eq "H:\Temp\PublicFolder-powershell\Create-String-For-Email-Fetch.xlsm"} 
$workbook.sheets.item(1).activate() 
$WorkbookTotal=$workbook.Worksheets.item(1) 
$value = $WorkbookTotal.Cells.Item(29, 10)  
$domain = $value.Text  
$leftoverdomain = '0|'   
$joinedvariables = -join($domain,$leftoverdomain,"") 

$b = Get-Content -Path "H:\Temp\PublicFolder-powershell\Create-String-For-Email-Fetch.txt" 

@(ForEach ($a in $b) {$a.Replace($joinedvariables, '')}) | Out-File "H:\Temp\PublicFolder-powershell\Create-String-For-Email-Fetch.txt" -NoNewline
Start-Sleep -Seconds 0.3

...然后它失败并显示以下更改的错误消息:

Call was rejected by callee. (Exception from HRESULT: 0x80010001 (RPC_E_CALL_REJECTED))

所以我想最终的结果是，只要你从 Windows 窗口处理程序中隐藏新的 excel 实例，COMs 就可以用相同的名称多次打开同一个文档。
但是......很高兴知道为什么原始代码在手动运行时有效，但在从脚本调用时无效，所以如果有人知道，我很想知道那个答案。