8,330 questions
This may work for you: how-do-i-put-the-focus-on-a-modal-dialog-box-using-powershell
Powershell- Excel refresh credential window
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(0, 76%, 10%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ET%3C/text%3E%3C/svg%3E)
Trevor
1
Reputation point
Hello. I've seen a few questions about this placed since 2016, but no answers.
I'm updating an excel spreadsheet with the .refresh command through a powershell script. This pulls up the modal window in Excel prompting for credentials.
The credentials are included in the query, so all I really need to do is press enter twice. However, my script doesn't proceed past the .refresh until these modal windows have been dealt with.
I've tried activating the window and sending ~ signals, but since it hangs at the .refresh that doesn't do much good.
Do you have any ideas about how to make these windows proceed?
Windows for business Windows Server User experience PowerShell
{count} votes
-
Emily Hua-MSFT 27,796 Reputation points2020-08-31T01:28:59.187+00:00 @Trevor ,
I would move "office-excel-itpro", as issues with "office-excel-itpro" tag in Q&A are about general questions of Excel desktop app, but your question is more focus on Powershell.
Sign in to comment
3 answers
Sort by: Most helpful
-
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(256, 74%, 34%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ERM%3C/text%3E%3C/svg%3E)
Rich Matheisen 47,901 Reputation points2020-08-29T15:03:56.763+00:00 -
Trevor 1 Reputation point
2020-08-31T18:56:03.733+00:00 Hello. The link you posted did help me select the modal window. I believe that I poorly stated the question at the beginning. My powershell script, once the .refreshAll() function is called, waits until the modal windows are dealt with before resuming.
To illustrate the issue, code:
Write-Output "selected it" [Microsoft.VisualBasic.Interaction]::AppActivate($WindowHandle) $excelworkbook.RefreshAll() Write-Output "hit refresh" start-sleep -s 3 $wshell.sendKeys('~')This is the relevant piece of the code. The modal activates, but the text "hit refresh" doesn't show until after all of the modal windows are dealt with so I know my script is not proceeding past the refresh until the modal windows are dealt with. This limits my ability to continue, since I can't program in a way to deal with the modal windows until after they appear.
-
Rich Matheisen 47,901 Reputation points
2020-08-31T19:25:49.827+00:00 I don't think you're going to easily bypass the credentialing. There may be a way to provide the credentials in VBA, but I don't know that.
Another way would be to provide the credentials in the query itself, but that would have to be done without encrypting them so the password would be exposed.
For something really kludgy you might try launching the refresh stuff as a job and then send the keys to the Excel window after waiting several seconds in the launching script. That, however, is just a thought off the top of my head.
The suggestion to ask the question (posed a little differently) in the Excel Dev group would probably get you the most relevant answer.
-
Trevor 1 Reputation point
2020-08-31T20:21:39.287+00:00 The credentials are already provided. They're built into the query.
I need to press "enter" on the credential popup, and that'll be it. Issue is, the .refreshAll() function pauses the script until the credential popup is dealt with. Is there a better way to refresh a sheet that I'm unaware of? -
Rich Matheisen 47,901 Reputation points
2020-09-01T15:00:02.787+00:00 I know very little about Excel, and I don't know what you're connecting to (a web site, SQL server, SharePoint, . . .). But I think you're up against the way Excel handles the connection (or maybe it's the way SQL handles it), so asking your question in a group where Excel is the focus would seem appropriate.
The only thing that comes to mind is that you might try the Excel options and check the security setting for data connections.
Sign in to comment -
-
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(128, 8%, 22%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EA%3C/text%3E%3C/svg%3E)
Anonymous2020-08-31T08:02:31.703+00:00 Hi，
For developer questions involving Microsoft Excel, you could ask the experts in the dedicate Excel for Developers forum here
https://social.msdn.microsoft.com/Forums/office/en-US/home?forum=exceldev
Best Regards,Ian
Please remember to "Accept Answer" and upvote if the reply is helpful.
-
Trevor 1 Reputation point
2020-09-07T18:09:04.023+00:00 I'll try there. Thank you.
Sign in to comment -
-
Trevor 1 Reputation point
2020-09-09T14:59:10.583+00:00 turns out setting
$objExcel.DisplayAlerts = $false
automatically proceeds with the default option for alerts, rather then just not dispalying them. Script works fabulously.
Thank you for your time.