Having an issue where a pivot chart is not refreshing upon opening my file. I have a web query setup on a protected worksheet called 'DATA' and a pivot table/chart on another worksheet called 'Summary'.
I also have another sheet called 'PW' where I have the password to unlock the worksheets stored (hidden) so no one can pick up my password if they look at the code. All worksheets are protected with the same password.
I believe I need to adjust my code to unlock both worksheets but... well I can't figure it out!
This is the code I have that refreshes the web query without any issues. If someone could help me adjust it to unlock both sheets, refresh all and then protect both worksheets it'd be much appreciated :)
I'm using Excel 2016.
Private Sub Workbook_Open()
Application.ScreenUpdating = False
Sheets("DATA").Unprotect Password:=Sheets("PW").Range("K2").Value
Workbooks(ThisWorkbook.Name).RefreshAll
Sheets("DATA").Protect _
Password:=Sheets("PW").Range("K2").Value, _
UserInterfaceOnly:=True, _
AllowFiltering:=True, _
AllowSorting:=True, _
AllowUsingPivotTables:=True
End Sub
EDIT:
I've tried the following code to unprotect both worksheets, refresh the web query on the 'DATA' worksheet, then refresh the pivot table on the 'Summary' worksheet, then protect both sheets again. It's returning an error though... Any ideas? Sorry new to this...
Private Sub Workbook_Open()
Dim ws As Worksheet
Set sheetsArray = ActiveWorkbook.Sheets(Array("DATA", "Summary"))
Application.ScreenUpdating = False
For Each ws In sheetsArray
ws.Unprotect Password:=Sheets("PW").Range("K2").Value
Next
ActiveWorkbook.Connections("Connection").Refresh
Sheets("Summary").PivotTables("PivotTable2").PivotCache.Refresh
For Each ws In sheetsArray
ws.Protect Password:=Sheets("PW").Range("K2").Value, _
Next
UserInterfaceOnly:=True, _
AllowFiltering:=True, _
AllowSorting:=True, _
AllowUsingPivotTables:=True
End Sub
BackgroundQueryset to false for tables. – Doug Glancy