Query then pivot table

[Joel]

I had an interesting problem today. A posting had a SQL Query in a macro
then wanted to create a Pivot table. The user also want to be able to
refresh the query and update the pivot table.

Since the number of the rows of the query can change the datasource of the
pivot table would also change; the datasource of the query table also needs
to be changed after the query was updated.

The problem is the Pivot Table Datasource is in R1C1 addressing

Can anybody figure out how in VBA to refernce R1C1 address

set MyRange = Range("Sheet2!R1C1:R6C4") doesn't work

neither does
set Myrange = Evaluate("indirect(Sheet2!R1C1:R6C4,false)")

I ended up doing the following

MyRange = "Sheet2!R1C1:R6C4"
'Remove Sheet Name
MySht = MyRange = Left(Myrange,Instr(MyRange,"!") - 1)
'remove sheet and first R from R1C1 address
MySht = MyRange = Left(Myrange,Instr(MyRange,"!") + 2)
FirstRow = Val(MyRange)
MyRange = mid(Myrange,Instr(MyRange,"C") + 1)
FirstCol = Val(MyRange)
MyRange = mid(Myrange,Instr(MyRange,"R") + 1)
LastRow = Val(MyRange)
MyRange = mid(Myrange,Instr(MyRange,"C") + 1)
LastCol = Val(MyRange)

with Sheets(MySht)
Set MyRange = .Range(.Cells(FirstRow,FirstCol),.Cells(LastRow,LastCol))
end with

 

[MichDenis]

Hi Joel,

Try this :

'-----------------------------------
Sub PivotTable()

Dim Adr As String 'Source
Dim Adr1 As String 'Destination
Dim PT As PivotTable

'Where will be the pivottable
With Worksheets("Feuil2")
Adr1 = .Name & "!" & .Range("G10").Address
'Where are the data
Adr = .Name & "!" & .Range("A1:B" & _
.Range("B65536").End(xlUp).Row).Address
End With
'creation of the PivotTable
Set PT = ActiveWorkbook.PivotCaches.Add _
(SourceType:=xlDatabase, SourceData:=Range(Adr)) _
.CreatePivotTable(TableDestination:=Range(Adr1), _
TableName:="MyPivotTable", DefaultVersion:=xlPivotTableVersion10)
With PT
.AddFields RowFields:="Field"
.PivotFields("field").Orientation = xlDataField
End With
End Sub
'-----------------------------------


"Joel" <[email protected]> a écrit dans le message de groupe de discussion :
(e-mail address removed)...
I had an interesting problem today. A posting had a SQL Query in a macro
then wanted to create a Pivot table. The user also want to be able to
refresh the query and update the pivot table.

Since the number of the rows of the query can change the datasource of the
pivot table would also change; the datasource of the query table also needs
to be changed after the query was updated.

The problem is the Pivot Table Datasource is in R1C1 addressing

Can anybody figure out how in VBA to refernce R1C1 address

set MyRange = Range("Sheet2!R1C1:R6C4") doesn't work

neither does
set Myrange = Evaluate("indirect(Sheet2!R1C1:R6C4,false)")

I ended up doing the following

MyRange = "Sheet2!R1C1:R6C4"
'Remove Sheet Name
MySht = MyRange = Left(Myrange,Instr(MyRange,"!") - 1)
'remove sheet and first R from R1C1 address
MySht = MyRange = Left(Myrange,Instr(MyRange,"!") + 2)
FirstRow = Val(MyRange)
MyRange = mid(Myrange,Instr(MyRange,"C") + 1)
FirstCol = Val(MyRange)
MyRange = mid(Myrange,Instr(MyRange,"R") + 1)
LastRow = Val(MyRange)
MyRange = mid(Myrange,Instr(MyRange,"C") + 1)
LastCol = Val(MyRange)

with Sheets(MySht)
Set MyRange = .Range(.Cells(FirstRow,FirstCol),.Cells(LastRow,LastCol))
end with

 

[Joel]

I know how to create a pivot table. the problem is with the update the 2nd
time the query table is refreshed. the pivot table is not synchronized with
the query.

The real problem is how do you define a range in VBA when you have a R1C1
address range.

 

[ryguy7272]

Maybe this:
Sheets("Sheet1").Select
Range("A1").Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
Sheets("Sheet1").Range("A1").CurrentRegion).CreatePivotTable _
TableDestination:=Sheets("Sheet2").Range("A3"),
TableName:="PivotTable1", _
DefaultVersion:=xlPivotTableVersion10

The key is this:
Sheets("Sheet1").Range("A1").CurrentRegion).

Does that do it?
Ryan--

 

[MichDenis]

Sorry, i stopped reading here :
| The problem is the Pivot Table Datasource is in R1C1 addressing
| Can anybody figure out how in VBA to refernce R1C1 address


To create the pivottable, use a name
(insertion / name ...) with this kind of formula as example
=Bd!$A$1ECALER(Bd!$F$1;0;0;NBVAL(Bd!$A:$A))

And to update your pivottable each time you update your querytable
You may use a class module....

Class module name : AppQt

Class module Code :
Public WithEvents AppQt As QueryTable
'---------------------------------
Private Sub AppQt_AfterRefresh(ByVal Success As Boolean)
If Success = True Then
Worksheets("Bd").PivotTables("Denis").PivotCache.Refresh
Else
MsgBox "Query failed or was cancelled"
End If
End Sub
'---------------------------------

In a general module, This code :

Dim AppObject As New AppQt
'----------------------------------
Sub Init()
Set AppObject.AppQt = ThisWorkbook.Worksheets("Feuil1").QueryTables(1)
End Sub
'----------------------------------

And in the ThisWorkbook :
'---------------------------
Private Sub Workbook_Open()
Module1.Init
End Sub
'---------------------------




"Joel" <[email protected]> a écrit dans le message de groupe de discussion :
(e-mail address removed)...
I know how to create a pivot table. the problem is with the update the 2nd
time the query table is refreshed. the pivot table is not synchronized with
the query.

The real problem is how do you define a range in VBA when you have a R1C1
address range.

 

[Joel]

I kind of like my ugly solution of extracting the rows and columns from th
eR1c1 format. It seems the only oter solution is to save the range of the
source table as a named range and then update the named range everytime a new
query is performed.

What start this problem is the original posting was trying to clear the
source data from the pivot table using the sourcedata property.

 

[MichDenis]

Have you met any trouble using a "name"
with that kind of formula to designate the range
of the source of your pivottable ?
=Bd!$A$1ECALER(Bd!$F$1;0;0;NBVAL(Bd!$A:$A))

And how about the class module to update your pivottable
when the querytable is updated ?




"Joel" <[email protected]> a écrit dans le message de groupe de discussion :
(e-mail address removed)...
I kind of like my ugly solution of extracting the rows and columns from th
eR1c1 format. It seems the only oter solution is to save the range of the
source table as a named range and then update the named range everytime a new
query is performed.

What start this problem is the original posting was trying to clear the
source data from the pivot table using the sourcedata property.