VBA code for refreshing query on protected sheet

[karolina1406]

hi, can someone help with vba code for refreshing query (called "Query1) on protected with a password "123" sheet called "Summary"?

 

[Fazza]

maybe easiest with the macro recorder. is that familiar? unprotect sheet, refresh query, re-protect sheet
start macro recorder with ALT-T-M-R
and finish maybe (may vary by version & I don't know about 365) with pressing rectangle at far LHS of status bar
I don't know the way 365 refers to query table objects either
if you want to refine the recorded code, please post it back to the forum. cheers

 

[karolina1406]

maybe easiest with the macro recorder. is that familiar? unprotect sheet, refresh query, re-protect sheet
start macro recorder with ALT-T-M-R
and finish maybe (may vary by version & I don't know about 365) with pressing rectangle at far LHS of status bar
I don't know the way 365 refers to query table objects either
if you want to refine the recorded code, please post it back to the forum. cheers

thank you for your help. Recorded macro was actually very easy and it almost worked It updated the query table with all the "added" values however, when any value was removed from the source table - it didn't update Query table with this removal. the code was as below

 ActiveSheet.Unprotect
 ActiveWorkbook.RefreshAll

 

[Fazza]

That is not expected query behavior. The VBA is not the issue - it has worked: it is something else with the set up.

Suggest you check the source table reference - be sure that when the refresh is done the data source is the modified data and the connection is to that saved version.
(I don't think the SQL could be an issue, but best to check that too. Long shot, and seems unlikely/wrong, but maybe queries in 365 have some setting for deleted data to somehow to not be removed. This would really surprise me.)