PDA

View Full Version : Solved: External Data Requery



FrymanSMU
05-13-2009, 12:21 PM
I keep getting an Excel error when I run the following code. The external data query is extrememly large and the macro does not seem to be waiting for the data to refresh.

Error Message: The cell or chart you are trying to change is protected and therefore read-only.

Any suggestions much appreicated.

Thanks,
Rich

Sub Button17_Click()
'On Error Resume Next
Application.ScreenUpdating = False
Sheets("RTS_Details").Visible = True
Sheets("Daily_Report").Visible = False
Sheets("User_Report").Visible = False
Sheets("RTS_Details").Select
Sheets("RTS_Details").Unprotect
Range("H16").Select
Selection.QueryTable.Refresh BackgroundQuery:=True
Range("F5").Select
Selection.AutoFill Destination:=Range("F5:F15000"), Type:=xlFillValues
Range("H5").Select
Selection.AutoFill Destination:=Range("H5:H15000"), Type:=xlFillValues
Sheets("RTS_Details").Select
Sheets("RTS_Details").Protect
Application.ScreenUpdating = True
End Sub

p45cal
05-13-2009, 03:37 PM
from help:
BackgroundQuery Optional Variant. Used only with QueryTables that are based on the results of a SQL query. True to return control to the procedure as soon as a database connection is made and the the query is submitted. The QueryTable is updated in the background. False to return control to the procedure only after all data has been fetched to the worksheet. If this argument isn't specified, the setting of the BackgroundQuery (http://www.vbaexpress.com/forum/xlproBackgroundQuery1.htm) property determines the query mode.

So change

Selection.QueryTable.Refresh BackgroundQuery:=True
to
Selection.QueryTable.Refresh BackgroundQuery:=False

?

Bob Phillips
05-13-2009, 03:47 PM
<deleted>

FrymanSMU
05-14-2009, 06:22 AM
p45cal, thanks changing Selection.QueryTable.Refresh BackgroundQuery:=False worked.