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

Register URL Protocol Handlers to Invoke Specific Browsers

User Story

Need to store frequently used links in OneNote, but some links only work on some specific browser. For example, the intranet SharePoint sites would work properly with MS Internet Explorer only, and JavaScript Benchmark Sites would need to run on different browsers individually.

The following would be my link list, and would like to have the URL protocol identified easily, too. Therefore, it would be like following:

• Intranet
• msie:https://moss/division1/dept1
• msie:https://moss/division1/outsourcing1
• chrome:https://foo/projects/schedule
• Software Testing
• http://dromaeo.com/
• chrome:http://dromaeo.com/
• firefox:http://dromaeo.com/
• msie:http://dromaeo.com/

Objecives

• Register customized URL handlers to invoke the corresponding browser

Tasks

• Find the way to set custom URL handlers
• Configure the shell command to remove the custom URI scheme, and feed the target link to the specific browser

Environment

• Windows 7 64 bits
• Office 2010 32 bits

References

•  Find out the way to register URL protocols for Windows 7
• http://msdn.microsoft.com/en-us/library/ie/aa767914(v=vs.85).aspx
• Trim out the "specific protocol", and feed the trimmed URL to the target browser
• http://stackoverflow.com/questions/11675346/dos-batch-argument-substring-in-one-line
• DOS Batch Tips: How to trim string
• http://ss64.com/nt/syntax-substring.html

Artifact Sample:

• Registry key for URL handler: msie://

[HKEY_CLASSES_ROOT\msie]

@="URL:MS Internet Explorer Protocol"

"URL Protocol"=""

[HKEY_CLASSES_ROOT\msie\Shell]
[HKEY_CLASSES_ROOT\msie\Shell\Open]
[HKEY_CLASSES_ROOT\msie\Shell\Open\Command]

@=cmd /v:on /c set arg=%1 & set arg=!arg:~5! & start "C:\Program Files\Internet Explorer\iexplore.exe" !arg!

• Sample Registry Key files