How to check if SHEET has QUERYTABLE property ?

[max chen]

HI

I gonna refresh all sheets with following codes, but some sheets may
have NO queryTable, i.g. some sheet with only chart. Then the
sheets(nIndex).queryTable must yeild error alarm windows, , event on
error can not disable it.

Is there any method to check if there is queryTable property for the
SHEET, or the SHEET is chart ?

Plz help me, i'm new to excel VBA. Thanks alot .

max.


--------------------------------------------------------
Private Sub CommandButton1_Click()

For i = 1 To Sheets.Count
If Sheets(i).Name <> c当å‰sheet Then
On Error GoTo NoQueryTable

nQueryTableCount = Sheets(i).QueryTables.Count
'the above statement bring out error alarm window

For j = 1 To nQueryTableCount
Sheets(i).QueryTables(j).Refresh BackgroundQuery:=False
Next

NoQueryTable:
aa = "i am sad..."
End If
Next

End Sub

 

[steve]

Max,

I think you can do it easily with this

Application.DisplayAlerts = False
Application.ScreenUpdating = False
ActiveWorkbook.RefreshAll
Application.ScreenUpdating = True
Application.DisplayAlerts = True

 

[max chen]

Thanks a lot

After I set the backgroundUpdate option of the imported data area to
false, The refreshAll do refresh all sheets. It's Great!

max.

 

[steve]

Max,

Glad it works for you! Works for me, all the time. I have a special
toolbar button for it...