PDA

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.