Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Query then pivot table
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,La stCol)) end with |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Query then pivot table
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. "MichDenis" wrote: 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" a écrit dans le message de groupe de discussion : ... 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,La stCol)) end with |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Query then pivot table
Maybe this:
Sheets("Sheet1").Select Range("A1").Select ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _ Sheets("Sheet1").Range("A1").CurrentRegion).Create PivotTable _ TableDestination:=Sheets("Sheet2").Range("A3"), TableName:="PivotTable1", _ DefaultVersion:=xlPivotTableVersion10 The key is this: Sheets("Sheet1").Range("A1").CurrentRegion). Does that do it? Ryan-- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Joel" wrote: 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. "MichDenis" wrote: 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" a écrit dans le message de groupe de discussion : ... 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,La stCol)) end with |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Query then pivot table
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$1:DECALER(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.R efresh 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" a écrit dans le message de groupe de discussion : ... 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. "MichDenis" wrote: 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" a écrit dans le message de groupe de discussion : ... 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,La stCol)) end with |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Query then pivot table
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" wrote: 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$1:DECALER(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.R efresh 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" a écrit dans le message de groupe de discussion : ... 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. "MichDenis" wrote: 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" a écrit dans le message de groupe de discussion : ... 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,La stCol)) end with |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Query then pivot table
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$1:DECALER(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" a écrit dans le message de groupe de discussion : ... 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" wrote: 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$1:DECALER(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.R efresh 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" a écrit dans le message de groupe de discussion : ... 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. "MichDenis" wrote: 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" a écrit dans le message de groupe de discussion : ... 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,La stCol)) end with |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
a query for pivot table | Excel Discussion (Misc queries) | |||
Pivot Table From MS Query | Excel Programming | |||
Pivot Table Query | Excel Discussion (Misc queries) | |||
Pivot Table Query | Excel Discussion (Misc queries) | |||
Pivot Table Query | Excel Programming |