One of the requirement I had was to download a certain file from the sharepoint on a daily basis and take a snapshot of the same for future reference. The drawback with Sharepoint is that we can download the file, but unless someone opens the file , refreshes all the connections and should perform the save, only then it will be updated. I achieved this with powershell and another tool available here – ExcelRefresh. You can use the code below to do that activity.
My setup was to place the ExcelRefresh.exe in the same folder where the downloads were being dumped.
| Set-Location C:\DownloadDumps | |
| #copy file from location to location | |
| $fromfile = "http://location.xlsx" | |
| $tofile = "C:\DownloadDumps\Dump-$(get-date -uformat '%Y-%M-%d').xlsx" | |
| $webclient = New-Object System.Net.WebClient | |
| $webclient.UseDefaultCredentials = $true | |
| # or | |
| # $webclient.Credentials = … | |
| $webclient.DownloadFile($fromfile, $tofile) | |
| #giving some wait time so that the file is downloaded over the LAN | |
| Start-Sleep -s 180 | |
| #find the latest file in the folder which needs to be opened and connections refreshed | |
| $dir = "C:\DownloadDumps" | |
| $latest = Get-ChildItem -Path $dir | Sort-Object LastAccessTime -Descending | Select-Object -First 1 | |
| #$latest.Name | |
| #create an argument list to pass to excel refresh | |
| $arguments=@(([String]::Format(" -f ")+($latest.Name)+(" -a"))) | |
| #echo $arguments | |
| #start process and pass the arguments. Make sure that the excel refresh is present in that folder | |
| Start-Process -FilePath "C:\DownloadDumps\ExcelRefresh.exe" $arguments |
Vinay Jagtap
A hard core Technocrat with over a decade of extensive experience in heading complex test projects coupled with real time experience of project management and thought leadership. Extensive experience in Performance, Security and Automation Testing and development of automation frameworks and ability to setup and execute Global service centers and Center of Excellences for testing.
