Error Doing For Each ws

Here's the problem I'm having with the code below.

If the worksheet contains no query-table the codes exits the inner loop
(For Each qt) and moves to the next worksheet with no error.

However, when the worksheet does contains a query-table the code exits
the inner loop but fails when it hits the 'Next ws'.
At that point it returns a "'Run-time error '13': type mismatch". If I
choose debug and then resume the codes continues until it encounters
the next sheet with a query-table.

Any advice will be appreciated
Buck
================================================== ===

Dim ws As Worksheet
Dim wsName As String
Dim pt As PivotTable
Dim ptname As String
Dim qt As QueryTable
Dim qtname As String

'Loop through all sheets and updates all QueryTables
Set wb = ActiveWorkbook
For Each ws In wb.Sheets
wsName = ws.Name
For Each qt In ws.QueryTables
qtname = qt.Name
Worksheets(wsName).Activate
Range("A1").Activate
Worksheets(wsName).QueryTables(qtname).Refresh
BackgroundQuery:=False
Next qt
Else
End If
Next ws

I'd suggest asking this in a newsgroup related to Excel.

This newsgroup is for questions about Access, the database product that's
part of Office Professional.

However, what you've posted can't possibly be your actual code, since you've
got two lines:
Else
End If

before your "Next ws" statement, and there's no If statement anywhere.


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


" wrote in message
ups.com...
Here's the problem I'm having with the code below.

If the worksheet contains no query-table the codes exits the inner loop
(For Each qt) and moves to the next worksheet with no error.

However, when the worksheet does contains a query-table the code exits
the inner loop but fails when it hits the 'Next ws'.
At that point it returns a "'Run-time error '13': type mismatch". If I
choose debug and then resume the codes continues until it encounters
the next sheet with a query-table.

Any advice will be appreciated
Buck
================================================== ===

Dim ws As Worksheet
Dim wsName As String
Dim pt As PivotTable
Dim ptname As String
Dim qt As QueryTable
Dim qtname As String

'Loop through all sheets and updates all QueryTables
Set wb = ActiveWorkbook
For Each ws In wb.Sheets
wsName = ws.Name
For Each qt In ws.QueryTables
qtname = qt.Name
Worksheets(wsName).Activate
Range("A1").Activate
Worksheets(wsName).QueryTables(qtname).Refresh
BackgroundQuery:=False
Next qt
Else
End If
Next ws

It looks like Excel VBA code so I suggest you re-post the question in one of
the relevant newsgroups for Excel.

This newsgroup is for Microsoft Access, a database application, and not
general computer "access".

--
HTH
Van T. Dinh
MVP (Access)



" wrote in message
ups.com...
Here's the problem I'm having with the code below.

If the worksheet contains no query-table the codes exits the inner loop
(For Each qt) and moves to the next worksheet with no error.

However, when the worksheet does contains a query-table the code exits
the inner loop but fails when it hits the 'Next ws'.
At that point it returns a "'Run-time error '13': type mismatch". If I
choose debug and then resume the codes continues until it encounters
the next sheet with a query-table.

Any advice will be appreciated
Buck
================================================== ===

Dim ws As Worksheet
Dim wsName As String
Dim pt As PivotTable
Dim ptname As String
Dim qt As QueryTable
Dim qtname As String

'Loop through all sheets and updates all QueryTables
Set wb = ActiveWorkbook
For Each ws In wb.Sheets
wsName = ws.Name
For Each qt In ws.QueryTables
qtname = qt.Name
Worksheets(wsName).Activate
Range("A1").Activate
Worksheets(wsName).QueryTables(qtname).Refresh
BackgroundQuery:=False
Next qt
Else
End If
Next ws