Powershell Script - Open Excel, Update External Da

Question

Is anyone able to help me with this script.

$file = 'C:\Scripts\Spreadsheet.xlsx'
$x1 = New-Object -ComObject "Excel.Application"
$x1.Visible = $false
$enddate = (Get-Date).tostring("dd-MM-yy")
$filename = 'C:\Scripts\Spreadsheet ' + $enddate + '.xlsx'
$wb = $x1.workbooks.Open($file)
$wb.refreshall
$wb.SaveAs($filename)
$wb.Close()
$x1.Quit()
Remove-Variable wb,x1

The workbook opens and updates its pivot table with external data through an ODBC connection. When you open the workbook manually it refreshes. When you open it with the script it just opens and does not refresh the data.

I have tried the following:

• Checking the checkbox "Always use connection file"
• Saving the password for the data source inside excel
• Disabling "Enable background refresh" and all the other refresh options
• Created a macro to automatically refresh the data source when the workbook opens

Any help would be appreciated, thanks!

Answer 1

Change

$wb.RefreshAll

to

$wb.RefreshAll()