Encounter exceptions sometimes when trying to Open a workbook via PowerShell and Microsoft.Office.Interop.Excel

I completed a simple Powershell scripts in order to open an existing Excel workbook, and update the data.
It works like a charm on some Windows PC, but failed sometimes when running on those PC with heavier loadings

Would encountering the following issues

• Rejected by the callee
• "Open" MethodNotFound

Trying some workarounds and found that if inserting "Start-Sleep" for seconds, it will relieve the pain some, but still not 100% avoided and waste some time in idle waiting. I start to seek out the way for polling the status of "application.workbooks". Fortunately, the symptom is observed

• When Application.Workbooks.count = 1, those issues would be reproduced
• When Application.Workbooks.count = 0, those issue would disappear

Therefore, we could assume that that Application.Workbooks would be ready when it becomes a collection instead of an object only. Now, we could start polling the Application.Workbooks.Count to test if count becomes 0. When it become 0, we could resume the operations we want.

Sample Code

$excel = New-Object -ComObject Excel.Application
$excel.Visible = $true
$excel.DisplayAlerts = $false

#When the count of $excel.workbooks is 0, $excel.workbooks is ready.
#When the count is greater than 0, $excel.workbooks is not initialized yet
while ($excel.workbooks.count -gt 0) {
Start-Sleep -milliseconds 100
Write-Debug "Initializing..."
}
             
$workbook = $excel.workbooks.Open($file)

How to combine existing worksheets using Power Query's Advanced Editor

We used to adopt VBA to combine data tables from multiple worksheets or workbooks. However, if adopt Power BI, such work could be done without any code and be more intuitive.

• With Power Query
• To combine data tables from multiple workbooks
• Combining Data From Multiple Excel Workbooks With Power Query–The Easy Way!
• To combine data tables from multiple worksheets
• Power Query - Combine Tables
• With 3rd party tools
• RDBMerge

The way mentioned in this article, Power Query - Combine Tables, could be leveraged for most purposes. However, it still costs much time to create the data connections in advance, and those created data connections might be meaningless afterward.

After digging out the programming language, M Language, used in Power Query, we could use Power Query's advance editor to combine worksheets easier. The following example shows the way to combine 20 data tables:

let 

Source20 = Excel.CurrentWorkbook(){[Name="Table20"]}[Content],    

Source19 = Excel.CurrentWorkbook(){[Name="Table19"]}[Content],    

Source18 = Excel.CurrentWorkbook(){[Name="Table18"]}[Content],    

Source17 = Excel.CurrentWorkbook(){[Name="Table17"]}[Content],    

Source16 = Excel.CurrentWorkbook(){[Name="Table16"]}[Content],    

Source15 = Excel.CurrentWorkbook(){[Name="Table15"]}[Content],    

Source14 = Excel.CurrentWorkbook(){[Name="Table14"]}[Content],    

Source13 = Excel.CurrentWorkbook(){[Name="Table13"]}[Content],    

Source12 = Excel.CurrentWorkbook(){[Name="Table12"]}[Content],    

Source11 = Excel.CurrentWorkbook(){[Name="Table11"]}[Content],    

Source10 = Excel.CurrentWorkbook(){[Name="Table10"]}[Content],    

Source9 = Excel.CurrentWorkbook(){[Name="Table9"]}[Content],    

Source8 = Excel.CurrentWorkbook(){[Name="Table8"]}[Content],    

Source7 = Excel.CurrentWorkbook(){[Name="Table7"]}[Content],    

Source6 = Excel.CurrentWorkbook(){[Name="Table6"]}[Content],    

Source5 = Excel.CurrentWorkbook(){[Name="Table5"]}[Content],    

Source4 = Excel.CurrentWorkbook(){[Name="Table4"]}[Content],    

Source3 = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],    

Source2 = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],    

Source1 = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],    

CombineData = Table.Combine({Source20, Source19, Source18, Source17, Source16, Source15, Source14, Source13, Source12, Source11, Source10, Source9, Source8, Source7, Source6, Source5, Source4, Source3, Source2, Source1})

in   CombineData

Hope this could help you to save much time when combining data tables.

References

• Learn about Power Query formulas
• Power Query Workshop