View Full Version : Solved: Pivot table
SeanJ
10-26-2006, 11:50 AM
Is there a way to create a pivot table from a SQL Select statement using VBA, where the data is only displayed in a pivot table. I know a pivot table reference a sheet of data for display or by importing data from Access.
I want to know if it can be done through an ODBC connection, and VBA
Thanks
austenr
10-26-2006, 12:03 PM
Something like this perhaps:
Sub GetSourceData()
Dim SQLString As Variant
Dim RowCount As Integer
Dim SQLRange As Range
Set SQLRange = Range("Sheet1!A1")
' Assign the SourceData array to the SQLString variable.
SQLString = SQLRange.PivotTable.SourceData
' Loop through each element of the SQLString array and copy these
' elements to Sheet1, starting in cell A1 and going down.
RowCount = 0
For Each xElement In SQLString
' The first element is the Connection String.
' Each additional element is the SELECT Statement
' broken in to 200-character text strings.
Range("A1").Offset(RowCount, 0).Value = xElement
RowCount = RowCount + 1
Next
End Sub
SeanJ
10-27-2006, 04:13 AM
Two questions.
1. xElement is not defined.
2. The sql statement is in a recordset see code below how can use it in your code code?
rsPubs.ActiveConnection = cnPubs
strSQL = "SELECT apn.aaaaProjectnumber_Org, CONVERT(VARCHAR(10),LModSchedule, 101) AS [Date] FROM ooo o JOIN aaaaProjectNumber apn ON o.[id] = apn.OTPId JOIN mmmRqmt mls ON o.[id] = mls.OTPId WHERE mls.linenum = 0 AND o.LModCost >= " & "'" & nDate & "'" & " ORDER BY lmoddate DESC"
rsPubs.Open strSQL
SeanJ
10-27-2006, 07:17 AM
Alright I did some more coding but not there yet.
' Create a PivotTable cache and report.
Set objPivotCache = ActiveWorkbook.PivotCaches.Add( _
SourceType:=xlExternal)
Set objPivotCache.Recordset = rstRecordset
With objPivotCache
.CreatePivotTable TableDestination:=Range("A3"), _
TableName:="Performance"
End With
With ActiveSheet.PivotTables("Performance")
.SmallGrid = False
With .PivotFields("Date")
.Orientation = xlRowField
.Position = 1
End With
With .PivotFields("Test Site")
.Orientation = xlColumnField
.Position = 1
End With
With ActiveSheet.PivotTables("Performance")
.AddDataField ActiveSheet.PivotTables( _
"Performance").PivotFields("Date"), "Count of Date", xlCount
End With
End With
I need to show each date in rows, not just the total. :banghead: :dunno
SeanJ
10-27-2006, 07:23 AM
I got it here is the code.
' Create a PivotTable cache and report.
Set objPivotCache = ActiveWorkbook.PivotCaches.Add( _
SourceType:=xlExternal)
Set objPivotCache.Recordset = rstRecordset
With objPivotCache
.CreatePivotTable TableDestination:=Range("A3"), _
TableName:="Performance"
End With
With ActiveSheet.PivotTables("Performance")
.SmallGrid = False
With .PivotFields("Date")
.Orientation = xlRowField
.Position = 1
End With
With .PivotFields("Test Site")
.Orientation = xlColumnField
.Position = 1
End With
With ActiveSheet.PivotTables("Performance")
.AddDataField ActiveSheet.PivotTables( _
"Performance").PivotFields("Date"), "Count of Date", xlCount
End With
With ActiveSheet.PivotTables("Performance").PivotFields("Date")
.Orientation = xlRowField
.Position = 1
End With
End With
Had to add the las pivot field back back again after the count was done.
I hope this help other.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.