9

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!

Improve this question
6
  • 1
    This looks like powershell and not vbscript Commented Nov 7, 2017 at 13:41
  • thanks for pointing that out, my bad Commented Nov 7, 2017 at 13:45
  • Instead of $wb.refreshall, use $wb.UpdateLink() Commented Nov 7, 2017 at 13:49
  • using $wb.updatelinks() didnt throw up any errors but it didnt refresh/update the spreadsheet. Commented Nov 7, 2017 at 14:21
  • 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 :) Commented Nov 7, 2017 at 14:25

3 Answers 3

Reset to default
14

Change

$wb.RefreshAll

to

$wb.RefreshAll()
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

3

Additional considerations need to be made for Power Query refreshes.

https://social.technet.microsoft.com/Forums/en-US/7f73b446-9351-45f2-8114-2ac0f37a12e4/power-query-connections-not-refreshing-when-issued-workbookrefreshall-from-powershell?forum=powerquery

One of the issues here is that BackgroundRefresh is enabled by default for Excel query tables (including ones created by Power Query), so when you call $wb.RefreshAll() it kicks off the refreshes and returns immediately while the refreshes are still running in the background. Blockquote

Improve this answer

Comments

1

Here is the documentation for the "Workbooks.Open" Method. The second paramerter is the Updatelinks. 'UpdateLinks parameter determine whether external references (links) are updated when the workbook is opened'. When you are opening the workbook in your code set the updatelinks value to 3.

$x1 = New-Object -ComObject "Excel.Application"
$x1.Visible = $false
$wb = $x1.workbooks.Open($file,3)
Improve this answer

1 Comment

This should work for most cases. However excel has the option to set recalculating the cells to manual. I don't think this simple solution would be enough then.