Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Type Error in For Each Worksheet Loop
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 Next ws |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Type Error in For Each Worksheet Loop
Your code worked perfect for me after one additional line:
Dim wb As Workbook Mike F " wrote in message oups.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 Next ws |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Type Error in For Each Worksheet Loop
as mike mentioned, works fine with the added line. add option explicit as
the first line, then run your code. you should get a variable undefined error. then add the line mike mentions, your code should run. if you always use option explicit, it will help you debug these kinds of errors -- Gary " wrote in message oups.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 Next ws |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Type Error in For Each Worksheet Loop
Thanks for the response but I was actually hoping you guys would find a
problem with the code. The code works fine for me as long as it is in a workbook I create. If I send the workbook I created to a coworker, it works fine. However, if a coworker pastes the code into a workbook they create, they get the run-time error 'Type mismatch'. If they send me the workbook they created, I get the same error they do. We are all using the same version of Excel and I've verified that we all have the same references. I'm mystified. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Type Error in For Each Worksheet Loop
For Each ws In wb.Sheets
says to look through all sheets. If you have chart sheets, you could have trouble. maybe... For Each ws In wb.worksheets But I agree with the other comments about declaring your variables. The other thing is that you don't need to refer to the worksheet using its name. You can use that ws variable. Same with your querytable variable. (And I didn't see a reason to activate the sheet and select A1, either.) Option Explicit Sub aa() Dim wb As Workbook Dim ws As Worksheet Dim qt As QueryTable 'Loop through all sheets and updates all QueryTables Set wb = ActiveWorkbook For Each ws In wb.Worksheets For Each qt In ws.QueryTables qt.Refresh BackgroundQuery:=False Next qt Next ws End Sub Another alternative: activeworkbook.refreshall This hits the pivottables, too, if that's a problem. " wrote: 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 Next ws -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Type Error in For Each Worksheet Loop
Dave, how does 'activeworkbook.refreshall' sequence the refresh tasks?
More specifically, will it refresh query-tables before it refreshes the pivot-tables based on those query tables? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Type Error in For Each Worksheet Loop
I don't know, but it shouldn't take too much testing to find out.
Please post back your results when you get them. Buck wrote: Dave, how does 'activeworkbook.refreshall' sequence the refresh tasks? More specifically, will it refresh query-tables before it refreshes the pivot-tables based on those query tables? -- Dave Peterson |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Type Error in For Each Worksheet Loop
It refreshes the sheets in the order they appear in the sheet tabs. If
Sheet1 has a pivot-table based on query-table data in sheet2 then 'Refreshall' updates the pivot-table and then the query-table, which means changes in the query-table data aren't reflected in the pivot-table. Looks like you would either have to move sheets with external links to the front of the workbook or run the 'Refreshall' twice so the pivot-table can pick up the new data on the second pass. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Type Error in For Each Worksheet Loop
Thanks for posting back.
Buck wrote: It refreshes the sheets in the order they appear in the sheet tabs. If Sheet1 has a pivot-table based on query-table data in sheet2 then 'Refreshall' updates the pivot-table and then the query-table, which means changes in the query-table data aren't reflected in the pivot-table. Looks like you would either have to move sheets with external links to the front of the workbook or run the 'Refreshall' twice so the pivot-table can pick up the new data on the second pass. -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Loop type formula - no Macro | Excel Discussion (Misc queries) | |||
Help: Compile error: type mismatch: array or user defined type expected | Excel Programming | |||
Help - Type mismatch when running loop with strings from arrays | Excel Programming | |||
Befuddled with For Next Loop ------ Run - Time Error '13' Type Mismatch Error | Excel Programming | |||
Copying data to another worksheet gives "Type Mismatch" error | Excel Programming |