excel - 令人费解的 Excel VBA 和 Powershell 未使用 Call 命令运行

Question

所以我有几个脚本要运行，使用 VBA 命令 CALL 调用它们

脚本如下所示：

Sub RUN_ALL()

Application.EnableCancelKey = xlInterrupt

Call RUN_LOGIN_SAVE_TO_FILE 'Macro29 PS1 - Stores username and password input by user for later reference.
    Application.Wait Now + TimeSerial(0, 0, 25)
    
    Application.Goto (ActiveWorkbook.Sheets("Merge1").Range("A1"))
Call BID_1_TextOut 'Macro1 - Exports rows from the Excel FETCH tab to a text file to be used to set "Send-On-Behalf"-rights
    Application.Wait Now + TimeSerial(0, 0, 0.6)
    Application.Goto (ActiveWorkbook.Sheets("Merge2").Range("A1"))
Call BID_2_TextOut 'Macro1 - Exports 23 rows from the Excel FETCH tab to a text file to be used to set "Send-On-Behalf"-rights
    Application.Wait Now + TimeSerial(0, 0, 0.6)
    Application.Goto (ActiveWorkbook.Sheets("Merge3").Range("A1"))
Call BID_3_TextOut 'Macro1 - Exports 23 rows from the Excel FETCH tab to a text file to be used to set "Send-On-Behalf"-rights
    Application.Wait Now + TimeSerial(0, 0, 0.6)
    Application.Goto (ActiveWorkbook.Sheets("Merge4").Range("A1"))
Call BID_4_TextOut 'Macro1 - Exports 23 rows from the Excel FETCH tab to a text file to be used to set "Send-On-Behalf"-rights
    Application.Wait Now + TimeSerial(0, 0, 0.6)
    Application.Goto (ActiveWorkbook.Sheets("Merge5").Range("A1"))
Call BID_5_TextOut 'Macro1 - Exports 23 rows from the Excel FETCH tab to a text file to be used to set "Send-On-Behalf"-rights
    Application.Wait Now + TimeSerial(0, 0, 0.6)
    Application.Goto (ActiveWorkbook.Sheets("Merge6").Range("A1"))
Call BID_6_TextOut 'Macro1 - Exports 23 rows from the Excel FETCH tab to a text file to be used to set "Send-On-Behalf"-rights
    Application.Wait Now + TimeSerial(0, 0, 0.6)
    
    Application.Goto (ActiveWorkbook.Sheets("Fetch").Range("A1"))
    
Call BID_MergeALL_TextOut 'Macro MergeALL - Merges all BID-TEXTOUT files to 1 file, Create-String-For-Email-Fetch_ALLMerge.txt, converts to Create-String-For-Email-Fetch.txt,replace empty parts of "domain/users/0|" with "nothing" and saves back as Create-String-For-Email-FetchTEMP.txt
    Application.Wait Now + TimeSerial(0, 0, 6)
    
Call BID_XCleanup 'Macro MergeXCleanup - This script replace empty parts of "domain/users/0|" with "nothing"
    Application.Wait Now + TimeSerial(0, 0, 2)
    

最后一部分

Call BID_XCleanup 'Macro MergeXCleanup - This script replace empty parts of "domain/users/0|" with "nothing"
    Application.Wait Now + TimeSerial(0, 0, 2)

如果我使用其余的调用命令运行它，则无法正常运行。我在脚本上放置了一个“-noexit”，所以powershell窗口保持不变，所以我知道调用命令获取脚本的启动，即：

'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_XCleanup()

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

End Sub

当我使用上述命令运行上述 REPLACE_IN_EmptyDomainUsersPartCOM.ps1 时，由 VBA 脚本与其他调用命令一起调用，该脚本实际上并没有做它应该做的事情......有时。有时在极少数情况下它会正确触发，例如如果我运行 Call 脚本 2 次它可以工作，但随后它无法在第三次写入最后一个文本文件，之后进行任何其他尝试。

这是 Powershell 脚本：

#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.

#The next code section will open the CURRENTLY open Excel file's sheet, instead of opening the whole workbook, which is why i use Marshal runtime. 
#Fully qualified path to target workbook to match against name. 
$targetWbFullPath = 'H:\Temp\PublicFolder-powershell\Create-String-For-Email-Fetch.xlsm'
#Stores the name of target workbook to match in running processes into variable $targetWbName
$targetWbName = Split-Path $targetWbFullPath -Leaf
#Attempts to access the process of open workbook with found name. 
$xl = [Runtime.Interopservices.Marshal]::GetActiveObject('Excel.Application')
#Get the list of names of open workbooks
$wbList = $xl.Workbooks | ForEach-Object {$_.Name}
#If target workbook is in the list…
$targetWb = if ($wbList -contains $targetWbName) {
 #…get it
 $xl.Workbooks.Item($targetWbName)
} else {
 # if your target workbook is not in the list…
 # exit the script, or…
 return
 # open it - Code if you want to open workbook as option but not used here.
 # $xl.Workbooks.Open($targetWbFullPath)
}
#Get the sheet to modify or collect data from. 
$WorkSheet = $targetWb.Sheets.Item('Fetch')
#Fetches "domain/users/" from J29 in excel sheet (Row 29, Column 10)
$domain = $WorkSheet.Rows.Item(29).Columns.Item(10).Text 
#Stores last part of domain string after the domain/user part to match with into variable $leftoverdomain. 
$leftoverdomain = '0|'   
#Joines the above variables together to form "domain/users/0|" as search tag to replace as leftover bits.
$joinedvariables = -join($domain,$leftoverdomain,"") 

#Opens text file to replace text in.
$b = Get-Content -Encoding Unicode "H:\Temp\PublicFolder-powershell\Create-String-For-Email-Fetch.txt" 
#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 -Encoding Unicode "H:\Temp\PublicFolder-powershell\Create-String-For-Email-FetchTEMP.txt" -NoNewline


### 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.

如果我从 Powershell ISE 或通过手动单击 Excel 中的执行 VBA 宏运行上述 powershell 脚本，它会触发并按预期运行没有问题。

所以我很困惑。为什么它不和其他人一起运行......每次......

起初我认为它可能会在内存中保存一个变量或数组引用，但是更改变量的名称并再次运行脚本并没有改变任何东西。

是否可以在单独的进程中运行 2 个 VBA 调用脚本？愚弄 Excel 以为我手动点击了 powershell 脚本，而实际上它是从调用中运行的？

因此，脚本单独按预期工作，但当与其余脚本一起运行时，powershell 脚本似乎无法正常工作。

我尝试在代码中包含“Write-Host”Hello World”，将代码分开以查看它在失败之前能走多远，因为我知道代码是单独运行的。

带写主机

#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.

#The next code section will open the CURRENTLY open Excel file's sheet, instead of opening the whole workbook, which is why i use Marshal runtime. 
#Fully qualified path to target workbook to match against name. 
$targetWbFullPath = 'H:\Temp\PublicFolder-powershell\Create-String-For-Email-Fetch.xlsm'
Write-Host "Hello worldA"
#Stores the name of target workbook to match in running processes into variable $targetWbName
$targetWbName = Split-Path $targetWbFullPath -Leaf
Write-Host "Hello worldB"
#Writes name of document
Write-Host $targetWbName
Write-Host "...is name of document in memory"
#Attempts to access the process of open workbook with found name. 
$xl = [Runtime.Interopservices.Marshal]::GetActiveObject('Excel.Application')
Write-Host "Hello world1"
#Get the list of names of open workbooks
$wbList = $xl.Workbooks | ForEach-Object {$_.Name}
Write-Host "Hello worldC"
#Writes out list of what has been fetched with Marshal. 
Write-host $wbList
Write-host "...is list in wbList variable"
#If target workbook is in the list…
Write-Host "If target workbook is in the list…"
$targetWb = if ($wbList -contains $targetWbName) {
Write-Host $targetWbName
 #…get it
 $xl.Workbooks.Item($targetWbName)
} else {
 # if your target workbook is not in the list…
 # exit the script, or…
 return
 # open it - Code if you want to open workbook as option but not used here.
 # $xl.Workbooks.Open($targetWbFullPath)
}
#Get the sheet to modify or collect data from. 
$WorkSheet = $targetWb.Sheets.Item('Fetch')
Write-Host "Hello world3"
Write-Host $WorkSheet
#Fetches "domain/users/" from J29 in excel sheet (Row 29, Column 10)
$domain = $WorkSheet.Rows.Item(29).Columns.Item(10).Text 
Write Host $domain
Write-Host "Hello world4"
#Stores last part of domain string after the domain/user part to match with into variable $leftoverdomain. 
$leftoverdomain = '0|'   

Write-Host "Hello world5"
#Joines the above variables together to form "domain/users/0|" as search tag to replace as leftover bits.
$joinedvariables = -join($domain,$leftoverdomain,"") 
Write-Host "Hello world6"

#Opens text file to replace text in.
$b = Get-Content -Encoding Unicode "H:\Temp\PublicFolder-powershell\Create-String-For-Email-Fetch.txt" 
Write-Host "Hello world7"
#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 -Encoding Unicode "H:\Temp\PublicFolder-powershell\Create-String-For-Email-FetchTEMP.txt" -NoNewline
Write-Host "Hello world8"


### 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.

手动运行 Powershell 脚本的 Powershell ISE 的输出：

Hello worldA
Hello worldB
Create-String-For-Email-Fetch.xlsm
...is name of document in memory
Hello world1
Hello worldC
Create-String-For-Email-Fetch.xlsm
...is list in wbList variable
If target workbook is in the list…
Create-String-For-Email-Fetch.xlsm
Hello world3
System.__ComObject
Host
domain/Users/
Hello world4
Hello world5
Hello world6
Hello world7
Hello world8
**<END Result>**

使用 VBA CALL 命令输出在 VBA 代码中运行与其余部分相同的脚本：

Hello worldA
Hello worldB
Create-String-For-Email-Fetch.xlsm
...is name of document in memory
Hello world1
Hello worldC

...is list in wbList variable
If target workbook is in the list…
**<END result>**

结果在 Excel 中手动将 Powershell 脚本作为 MACRO 运行：

Hello worldA
Hello worldB
Create-String-For-Email-Fetch.xlsm
...is name of document in memory
Hello world1
Hello worldC
Create-String-For-Email-Fetch.xlsm
...is list in wbList variable
If target workbook is in the list…
Create-String-For-Email-Fetch.xlsm
Hello world3
System.__ComObject
Host
domain/Users/
Hello world4
Hello world5
Hello world6
Hello world7
Hello world8
**<END Result>**

据我所知，当使用 CALL VBA 命令自动运行时，脚本中的信息似乎不仅仅是无法从“System.__ComObject”中提取信息

我还注意到，如果实际 Excel 文件的保存/修改时间戳比 1KB“打开”文件的名称几乎完全相同，那么脚本通常会失败。如果我重新保存 Excel 文件，它通常似乎工作得更好、更频繁。如果我然后关闭 Excel 并再次打开 Excel 工作表，因此 1KB 文件获得更新的时间戳，那么脚本似乎再次失败。

所以我认为可能是脚本试图从 1KB 文件中获取信息，而不是实际的 excel 文件，因为这两个文件都被 Excel 进程锁定，但是当我“共享”excel 文档时，以防止 Excel 写入1KB 文件，它仍然在脚本失败之后仍然失败。

我只是无法理解为什么脚本有时无法正确执行，而其他时候却可以正常执行。

我有一种强烈的感觉，它与 Marshall 部分有关，脚本试图访问在内存中运行的正确的 excel 进程，

#If target workbook is in the list…
$targetWb = if ($wbList -contains $targetWbName) {
 #…get it
 $xl.Workbooks.Item($targetWbName)

...并且无法收集到数据或获取正确的过程。

我必须使用对已打开的现有文档的引用，因为我尝试“打开”该文档，并且当我之后关闭 excel 时，它不断给出数百万条消息“您的文档现在可以访问”，脚本运行，但是错误消息非常烦人，这就是为什么我使用 Marshal 来获取已经打开的 Excel 文件的工作表并从中提取数据。

任何人都可以帮助我与其他人一起使用 CALL 脚本吗？

谢谢

诗人

标签： excelvbapowershellcall