Powershell Script - Open Excel, Update External Data, Save as

6
votes

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!

1
excelpowershellodbcpivot
This looks like powershell and not vbscript – Pankaj Jaju
thanks for pointing that out, my bad – user8593258
Instead of $wb.refreshall, use $wb.UpdateLink() – G42
using $wb.updatelinks() didnt throw up any errors but it didnt refresh/update the spreadsheet. – user8593258
I managed to work it out I added () at the end of refresh all. So the new line was $wb.refreshall(), thank you for helping me get it sorted :) – user8593258

1 Answers

9
votes

Change

$wb.RefreshAll

to

$wb.RefreshAll()