Anyway to force Excel RefreshAll to ask for credentials via code?
I am working on a project where I am refactoring code to be asynchronous. I am using Async/Await using Task.Run and in my code block I call the excel worksheets "RefreshAll". When called it prompts for the credentials for the server connection with which it will refresh from. Since I set this up using Async, it now prompts for the credentials but does not wait for them to be entered before continuing and will continue running the rest of the code which causes a number of errors because the credentials have not been entered yet. So I was thinking that if I can force the credentials to be entered before I make my async/await call, it would solve my issue. However I can't seem to find a way to do this. I'm open to any other ideas if anyone has any.
Thanks!
Code:
Private Async Function RefreshReport_OrigBakgroundWorker1(xlsWorkbook As Excel.Workbook, conn As SqlConnection, Optional autoId As Integer = -1) As Task
Try
xlsWorkbook.RefreshAll() '<-- This is where it prompts for the credentials and I would like to force it to ask in another code block
Log("Refresh done and call save for report: " & xlsWorkbook.Name)
xlsWorkbook.Save()
Log("Save done for report: " & xlsWorkbook.Name)
Catch ex As Exception
Log("Error in RefreshReport_OrigBakgroundWorker1: " + ex.Message, LogType.LogError)
Finally
End Try
End Function