MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Web Query Refresh

Posted by Jason on December 12, 2001 5:57 AM

Hi, does anyone know how to make a web query refresh on workbook open, if the workbook is protected?!? Even unprotecting the query range (which I DON'T want to do!) doesn't work...the 'Refresh Data' command is also grayed out. I've even tried having a WorkbookOpen script do a RefreshAll on the sheet...that doesn't work either. There's some reference to a RefreshData method of an RTD object, which may apply, but I don't understand what an RTD object is. Anyway, I'd love to do this without scripting, because (a) it won't work if users disable the macros, and (b) I'd need to embedd the protection password in the script! Any ideas?!?

Posted by Jason on December 17, 2001 4:35 AM

Well, I figured it out myself -- at least, via a VBScript which is tied to Workbook Open:

ActiveSheet.Unprotect ("password")
Selection.QueryTable.Refresh BackgroundQuery:=True
ActiveSheet.Protect ("password")

Anyway, if anyone knows a non-scripting way to do this, that would be cool...