vba Powershell跨多个工作表在excel工作簿中运行多个vba脚本

Powershell Run Multiple vba scripts in excel workbook across multiple worksheets

提问by Wynn

Hi I need to run different vba scripts on different worksheets in the same workbook. Basically, each worksheet has it's own vba script which triggers an ODBC connection then updates the worksheet from a database. I've been able to get one vba script to run on one sheet and save as... no problem, but can't get anymore than one to run. here's the code I'm using

嗨，我需要在同一个工作簿中的不同工作表上运行不同的 vba 脚本。基本上，每个工作表都有自己的 vba 脚本，该脚本触发 ODBC 连接，然后从数据库更新工作表。我已经能够让一个 vba 脚本在一张纸上运行并另存为...没问题，但只能运行一个。这是我正在使用的代码

$excel = new-object -comobject excel.application
$excelFiles = Get-ChildItem -Path C:\test\Daily_update.xlsm
$Date = (Get-Date -Format dd-MM-yy)
Foreach($file in $excelFiles)
{
 $workbook = $excel.workbooks.open($file.fullname)
 $worksheet = $workbook.worksheets.item(2)
 $excel.Run("Test_Refresh")

 $workbook.saveAs("C:\test\Daily_update_$Date.xlsm")
 $workbook.close()
}
$excel.quit()

When I try to add other worksheets and vba scripts it doesn't work at all.

当我尝试添加其他工作表和 vba 脚本时，它根本不起作用。

回答by Wynn

OK after walking away from the problem for a little while, a little more coffee and applying some logic. I got the thing to work. So just in case you need a script to do what I was after and run a specific macro on a specific worksheet in the same workbook, here it is.

离开问题一段时间后，再喝点咖啡并应用一些逻辑就OK了。我得到了工作。因此，以防万一您需要一个脚本来执行我所追求的操作并在同一工作簿中的特定工作表上运行特定宏，这里是。

$excel = new-object -comobject excel.application
$excelFiles = Get-ChildItem -Path C:\Test\Daily_update.xlsm
$Date = (Get-Date -Format dd-MM-yy)

 $workbook = $excel.workbooks.open($excelfiles.fullname)
 $WS2 = $workbook.worksheets.item(2)
 $WS2.Activate()
 $excel.Run("Test_Refresh")

 $WS3 = $workbook.worksheets.item(3)
 $WS3.Activate()
 $excel.Run("test_Refresh_2")

 $WS4 = $workbook.worksheets.item(4)
 $WS4.Activate()
 $excel.Run("test_Refresh_3")

 $workbook.saveas("C:\Test\SQL\Daily_update_$Date.xlsm")
 $workbook.close()

$excel.quit()

Just to add a little more to this. After some trial and error I found that the following code was far more efficient at running than the above. Also I noticed that if there were only 3 worksheets it was fine but any more started to raise errors, especially when calling sheets. But this problem disappeared when the workbook was opened and visible.

只是为了增加一点。经过一些试验和错误，我发现下面的代码运行效率远高于上面的代码。我还注意到，如果只有 3 个工作表，那很好，但是更多的工作表开始引发错误，尤其是在调用工作表时。但是当工作簿打开并可见时，这个问题就消失了。

#Call the application
$excel = new-object -comobject excel.application
#Now we select the file and path 
$excelFiles = Get-ChildItem -Path "\Server\Test\Daily_refresh.xlsm"
#The next variable speeds the script up by not calling the comobject as often
$app = $excel.Application
#Get system date and time and format it to comply with the final filename format
$Date = (Get-Date -Format dd-MM-yy)
#And again for the year folder
$Year = (Get-Date -Format yyyy)
#Test if folder exists
$DestYearFolder = "\Server\Test\Daily_Refresh_Output\Test$Year"
if (!(Test-Path -path $DestYearFolder)) {New-Item $DestYearFolder -Type Directory}
#Same as above only for the month folder
$Month = (Get-Date -Format MMM)
#Test if folder exists
$DestMonthFolder = "\Server\Test\Daily_Refresh_Output\Test$Year$Month"
if (!(Test-Path -path $DestMonthFolder)) {New-Item $DestMonthFolder -Type Directory}
#Now we open the Excel file and activate the macro enabled content
 $workbook = $app.workbooks.open($excelfiles)
 #The next command makes Excel visible
 $app.Visible = $true
 $workbook.Activate()
 #Now we run all the Macros that need to be run.
 $app.Run("Macro_1")
 $app.Run("Macro_2")
 $app.Run("Macro_3")
 $app.Run("Macro_4")
 $app.Run("Macro_5")
 $app.Run("Macro_6")
 $app.Run("Macro_7")
 $app.Run("Macro_8")
 $app.Run("Macro_9")
 $app.Run("Macro_10")

 #Now we save the workbook in the standard daily format and the close Excel
 $workbook.saveas("\Server\Test\Daily_Refresh_Output\Test$Year$Month\Daily_Refresh_test_$Date.xlsm")
 $workbook.close()

$excel.quit()