How to check if SHEET has QUERYTABLE property ?

  • Thread starter Thread starter max chen
  • Start date Start date
M

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
 
Max,

I think you can do it easily with this

Application.DisplayAlerts = False
Application.ScreenUpdating = False
ActiveWorkbook.RefreshAll
Application.ScreenUpdating = True
Application.DisplayAlerts = True
 
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.
 
Max,

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