This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](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)
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?
@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.
![](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)
This may work for you: how-do-i-put-the-focus-on-a-modal-dialog-box-using-powershell
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.
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.
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?
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.
![](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, 94%, 22%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EIX%3C/text%3E%3C/svg%3E)
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,
Please remember to "Accept Answer" and upvote if the reply is helpful.
I'll try there. Thank you.
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.