1

I've looked around quite a bit for solutions to this and don't see any PowerShell only solutions. I've seen a few PowerShell/VBA solutions but nothing saying it is not possible to complete solely using PowerShell. I would prefer not to use VBA if possible, PowerShell only.

I have a few workbooks with multiple sheets that are currently manually refreshed to retrieve data from a SQL Server (2008 R2) database instance. I can do everything I need using the following code if I run line by line and wait for refresh operations to complete:

$Excel = New-Object -ComObject Excel.Application
$Workbook = $Excel.Workbooks.Open('C:\test.xlsx')
$Excel.Visible = $True
$Workbook.RefreshAll()
$workbook.Save()
$Workbook.Close()
$Excel.Quit()

The only problem is when I run the whole script, as expected, the Save() method executes while the refresh operation is still running resulting in this prompt thus interrupting the Save(), Close(), and Quit() methods:

enter image description here

I could of course use the Start-Sleep cmdlet in a loop to wait for the database connections to complete using a static interval, however, the stored procedures that are executed range from 2 seconds - 3 minutes and seems like a waste of resources to sleep on each refresh like that.

The Stack Overflow answer I linked above lists 3 possible solutions to this but I don't see those properties that are listed available in the PowerShell objects (QueryTable.Refreshing for example doesn't exist using PowerShell). It appears that they're available in VBA, although the code examples are written using PowerShell. Are the examples wrong or am I missing something here?

My question: Is it possible to complete the code above by adding a dynamic 'wait' operation after RefreshAll() and before Save() using some sort of "Excel is refreshing/busy" property using PowerShell only?

gbeaven
  • 1,522
  • 2
  • 20
  • 40
  • I don't think it is possible. Why don't you use something more specific for this kind of task? like vba. – Simo Sep 21 '18 at 14:34
  • @Simo Because less code is better to me. If I can complete it all with one language then that's preferred. If it's simply not possible, I understand that and will have to look into alternative options like adding VBA. Just wanting to know if it is possible with PowerShell only. – gbeaven Sep 21 '18 at 14:37
  • I don't think there's a way to handle excel events from powershell. You are really complicating your life insisting on using powershell. Maybe now there is a solution, but this is a very big **MAYBE**. But in the future? Maybe some new feature you have to add will be absolutely impossibile in powershell – Simo Sep 21 '18 at 14:40
  • It's like pretend that a car with squared wheels could possibly beat a normal car. Maybe it's possible, but it's very very difficult and expensive – Simo Sep 21 '18 at 14:41
  • A bit confused - the examples are all Powershell - but the methods/properties are specific to the Excel application - just like `$Workbook.RefreshAll()` in your existing code. – BigBen Sep 21 '18 at 14:44
  • @BigBen PowerShell is used to access Excel's COM objects. – gbeaven Sep 21 '18 at 14:50
  • Exactly - so your comment "if I can complete it all with one language" doesn't make sense to me. It sounds more like "Can I determine with native Powershell objects/commands whether the query is refreshing in the background?" – BigBen Sep 21 '18 at 14:51
  • @BigBen Seems like semantics at this point? PowerShell is the language, accessing COM objects (which isn't a programming language) so it's still 1 language..? – gbeaven Sep 21 '18 at 14:56
  • @BigBen It's starting to seem like you didn't fully read the question. I do see `RefreshAll()` method and am using it without issue. As stated, the whole script works - the issue is with the `Save()` interrupting the refresh operation that occurs from `RefreshAll()`. Correct, I don't see `QueryTable.Refreshing` property which is strange since I can see the QueryTable object and the linked SO answer referenced the Refreshing property in his PowerShell code. Wasn't sure if I was missing something. – gbeaven Sep 21 '18 at 15:13
  • All back to semantics. My point is that you're *already* using the Excel object model to `RefreshAll`, and just as equally will (probably, not aware of any other method) have to use the Excel object model to determine if the query is still refreshing in the background, as the linked question demonstrates. – BigBen Sep 21 '18 at 15:25
  • @Simo it is indeed possible. I've posted an answer to the question. – gbeaven Sep 21 '18 at 19:34

1 Answers1

4

I was able to figure this out with the following code additions:

$conn = $Workbook.Connections

and 

while($conn | ForEach-Object {if($_.OLEDBConnection.Refreshing){$true}}){
    Start-Sleep -Seconds 1
}

The Refreshing property lives in the OLEDBConnection object.

The final code:

$Excel = New-Object -ComObject Excel.Application
$Workbook = $Excel.Workbooks.Open('C:\test.xlsx')
$Excel.Visible = $True
$conn = $Workbook.Connections
$Workbook.RefreshAll()
while($conn | ForEach-Object {if($_.OLEDBConnection.Refreshing){$true}}){
    Start-Sleep -Seconds 1
}
$workbook.Save()
$Workbook.Close()
$Excel.Quit()
gbeaven
  • 1,522
  • 2
  • 20
  • 40