Keep query return named range consistent?

[RRinTetons]

I have a worksheet that queries data from a SQL Server database. The query returns a fixed number of columns and varying numbers of rows. After the data gets to Excel I select it and make a pivot table out of it. Here's the line I use to create the PT

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"Sheet1!CompQueryFromJHMRCustom_13").CreatePivotTable TableDestination:="", TableName:= _
"PivotTable1", DefaultVersion:=xlPivotTableVersion10

Notice the named range used as the range for the PT - that's the name of the query return. The problem is that it increments each time the query runs - <name>_01 ... <name>_xx.

How can I get a consistent range argument for the pivot table? Rename the range somehow each time the query runs? Figure out the new name and reference it in the statement above? Some other good idea?



-
Richard Ray
Jackson Hole Mountain Resort

 

[SkipVought]

Hi,

It seems from your description of the situation, that your QUERY is a TRANSFORM (Additional COLUMNs are returned).

Why would you use a transform as the source for a PivotTable?

However, that's what you have.

So what, that the range changes. You apparently are only using the first x columns as fields in your PivotTable. It will not effact your PT results when you refresh.

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[RRinTetons]

I have 4 cells on the spreadsheet that accept input and are then passed to an SP in the database. It's not just refreshing the data in the table, it's an entire new instance of the query. Here's the code I use to create the PT:

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"Sheet1!CompQueryFromJHMRCustom_13").CreatePivotTable TableDestination:="", TableName:= _
"PivotTable1", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:="ProductCategory" _
, ColumnFields:="TheSeason_Name"
ActiveSheet.PivotTables("PivotTable1").PivotFields("Count").Orientation = _
xlDataField



The line

SourceData:= _
"Sheet1!CompQueryFromJHMRCustom_13").CreatePivotTable TableDestination:="", TableName:= _
"PivotTable1", DefaultVersion:=xlPivotTableVersion10

is the problem. Each time the user changes the cells and runs the query again the range returned comes back with a new name, so the code to rebuild the PT for the new data fails on the name of the range. I'm looking for a way to be able to run the code to build the PT consistently, but maybe I need to find a way to access a property for the query to tell me what the return is called?



-
Richard Ray
Jackson Hole Mountain Resort

 

[RRinTetons]

Note that each time I rerun the query with different parms it comes back with a new name 'CompQueryFromJHMRCustom_'+a number that increments by 1 for each execution of the query.

How about a way to 'reset' the query somehow so that it always comes back with the original name?


-
Richard Ray
Jackson Hole Mountain Resort

 

[SkipVought]

"it's an entire new instance of the query"

Why would you do that? It is TOTALLY unnecessary AND it mucks upki the sheet with additional QueryTables (on that sheet, check out the Names Box and see ALL the querytables)

So you're getting a brand new name each time. Does a number on your PT Source. YUK!

ONE QueryTable.

Data > Refresh

Select in your PivotTable.

Data > Refresh

Its THAT SIMPLE. No Coding required!



Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[RRinTetons]

Because I didn't know that I could parameterize a query thorugh MS Query until just now!! I was creating the query from whole cloth in VBA each time. :-( Now that I see how to use MS Query and pass it values from the worksheet the whole thing is going to be MUCH easier!


-
Richard Ray
Jackson Hole Mountain Resort

 

[SkipVought]

I'd suggest using some code like this for refreshing BOTH objects...

QuerySheetObject.QueryTables(1).refresh false
pivottableSheetObject.Pivottables(1).pivotcache.refresh

assuming that you have ONE querytable and ONE pivottable. Otherwise, post back for a loop solution.

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[RRinTetons]

Thanks. I looped through each of the queries and PT's and got everything refreshed. There are several parms to the SQL query, so I allow the user to get them all set and then click a button on the main sheet to refresh everything. This will work nicely, and be very extensible as they add other content they want to see.

-
Richard Ray
Jackson Hole Mountain Resort