locked
Refreshing Excel Sheet which prompts for credentials using powershell RRS feed

  • Question

  • Hello all!

    I have an excel sheet that is connected to a datasource. When you go to refresh the datasource you get prompted by a windows security box to enter credentials.

    What I would like to do is automate this using powershell. So I have the following code:

    add-type -AssemblyName System.Windows.Forms #Required for keystroke emulation
    
    $source = [System.IO.Directory]::GetFiles("C:\Scripts\Test", "excelfile.xlsm") #Opens desired excel sheet
    
    #Write-Host $source
    
    $file1 = $source 
    
    $xl = new-object -c excel.application
    $xl.displayAlerts = $false 
    
    $wb1 = $xl.workbooks.open($file1) 
    $xl.ActiveWorkbook.Refreshall()
    
    #------------------------ When refresh starts a prompt for MFA credentials should appear, this code pertains to the aforementioned prompt
    Function Testing{
    sleep 1
    Write-Host "Today, we move forward!"
    #Emulates username and password entry for Azure MFA account
    #[System.Windows.Forms.SendKeys]::SendWait("username")
    #[System.Windows.Forms.SendKeys]::SendWait("{TAB}")
    #[System.Windows.Forms.SendKeys]::SendWait("password")
    #[System.Windows.Forms.SendKeys]::SendWait("{ENTER}") #enter    
    }
    #------------------------ END MFA Credentials prompt code ---------------------------------------------------------------------------------
    But if you run the code above it hangs up at the refresh call. Any assistance would be greatly appreciated!!!

    Wednesday, June 3, 2015 2:45 PM

Answers

  • ​Hi Chuck_1987,

    >> if you run the code above it hangs up at the refresh call

    This "hang" is because of the credentials pop up when refreshing the data, and we can't automate the credential enter process.

    For a workaround, I suggest you save the credential in the data connection, but in this way, it is unencrypted.

    >>I tried calling Testing in Refreshall(Testing) but for some reason it doesn't perform the requested function.

    What do you mean by “the requested function”? Do you achieve the function to refresh the activeworkbook? In your original issue, you could not run the Testing function, has it worked? To be honesty, I am not familiar with powershell. Since this forum is developing excel developing, if you have issue about emulates username and password entry for Azure MFA account with Powershell, it is more related with powershell, I suggest you go to the forum below for this is not related with Excel.

    Reference: http://social.technet.microsoft.com/Forums/en-US/home?forum=winserverpowershell

    >> I also when I go to save am prompted to overwrite the file in question. and instead of just saving it and closing the application actually tells me the file exists and am I sure I want to replace it when I really just want it to Open the file, refresh the data, and save then close.

    This issue is a second issue which is different from the original one, I suggest you post a new thread for this issue, and there would be more community members to help you and we could focus on the specific issue.

    Thanks for your understanding.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.



    Friday, June 5, 2015 9:58 AM

All replies

  • Hi Chuck_1987,

    >> But if you run the code above it hangs up at the refresh call.

    Did you mean that your powershell did not run the Testing function? Did the code for open excel file and refresh the workbook work? Based on your code, I found you did not call Testing function in your script. You could try the code below:

    add-type -AssemblyName System.Windows.Forms #Required for keystroke emulation
    
    $source = [System.IO.Directory]::GetFiles("D:\Backup\Desktop", "Thread.xlsx") #Opens desired excel sheet
    
    #Write-Host $source
    
    $file1 = $source 
    
    $xl = new-object -c excel.application
    $xl.displayAlerts = $false 
    $xl.Visible=$true
    $wb1 = $xl.workbooks.open($file1) 
    $xl.ActiveWorkbook.Refreshall()
    #------------------------ When refresh starts a prompt for MFA credentials should appear, this code pertains to the aforementioned prompt
    Testing
    Function Testing{
    sleep 1
    Write-Host "Today, we move forward!"
    #Emulates username and password entry for Azure MFA account
    #[System.Windows.Forms.SendKeys]::SendWait("username")
    #[System.Windows.Forms.SendKeys]::SendWait("{TAB}")
    #[System.Windows.Forms.SendKeys]::SendWait("password")
    #[System.Windows.Forms.SendKeys]::SendWait("{ENTER}") #enter    
    } 
    

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Thursday, June 4, 2015 12:48 PM
  • Hi Chuck_1987,

    >> But if you run the code above it hangs up at the refresh call.

    Did you mean that your powershell did not run the Testing function? Did the code for open excel file and refresh the workbook work? Based on your code, I found you did not call Testing function in your script. You could try the code below:

    add-type -AssemblyName System.Windows.Forms #Required for keystroke emulation
    
    $source = [System.IO.Directory]::GetFiles("D:\Backup\Desktop", "Thread.xlsx") #Opens desired excel sheet
    
    #Write-Host $source
    
    $file1 = $source 
    
    $xl = new-object -c excel.application
    $xl.displayAlerts = $false 
    $xl.Visible=$true
    $wb1 = $xl.workbooks.open($file1) 
    $xl.ActiveWorkbook.Refreshall()
    #------------------------ When refresh starts a prompt for MFA credentials should appear, this code pertains to the aforementioned prompt
    Testing
    Function Testing{
    sleep 1
    Write-Host "Today, we move forward!"
    #Emulates username and password entry for Azure MFA account
    #[System.Windows.Forms.SendKeys]::SendWait("username")
    #[System.Windows.Forms.SendKeys]::SendWait("{TAB}")
    #[System.Windows.Forms.SendKeys]::SendWait("password")
    #[System.Windows.Forms.SendKeys]::SendWait("{ENTER}") #enter    
    } 

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    I tried calling Testing in Refreshall(Testing) but for some reason it doesn't perform the requested function.

    I also when I go to save am prompted to overwrite the file in question. So I run the following code:

    $source = [System.IO.Directory]::GetFiles("C:\Scripts\Testing", "TestFile.xlsm") #Opens desired excel sheet
    
    $file1 = $source #Sets $file1 to $Source
    
    $xl = new-object -c excel.application #Creates com for excel app
    $xl.displayAlerts = $false #Disables excel save alerts
    
    $wb1 = $xl.workbooks.open($file1) #Opens the file designated by the variable $source
    
    $xl.ActiveWorkbook.Refreshall() #Sends command to excel to refresh the data sources
    
    Sleep 30 #wait while data updates
    Write-Host "Starting Save and Close Process"
    $wb1.Save() #Save excel file
    $wb1.close($true) #close excel file

    and instead of just saving it and closing the application actually tells me the file exists and am I sure I want to replace it when I really just want it to Open the file, refresh the data, and save then close.

    Thursday, June 4, 2015 4:36 PM
  • ​Hi Chuck_1987,

    >> if you run the code above it hangs up at the refresh call

    This "hang" is because of the credentials pop up when refreshing the data, and we can't automate the credential enter process.

    For a workaround, I suggest you save the credential in the data connection, but in this way, it is unencrypted.

    >>I tried calling Testing in Refreshall(Testing) but for some reason it doesn't perform the requested function.

    What do you mean by “the requested function”? Do you achieve the function to refresh the activeworkbook? In your original issue, you could not run the Testing function, has it worked? To be honesty, I am not familiar with powershell. Since this forum is developing excel developing, if you have issue about emulates username and password entry for Azure MFA account with Powershell, it is more related with powershell, I suggest you go to the forum below for this is not related with Excel.

    Reference: http://social.technet.microsoft.com/Forums/en-US/home?forum=winserverpowershell

    >> I also when I go to save am prompted to overwrite the file in question. and instead of just saving it and closing the application actually tells me the file exists and am I sure I want to replace it when I really just want it to Open the file, refresh the data, and save then close.

    This issue is a second issue which is different from the original one, I suggest you post a new thread for this issue, and there would be more community members to help you and we could focus on the specific issue.

    Thanks for your understanding.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.



    Friday, June 5, 2015 9:58 AM