Excel Macro Pivot Table(Access Table) Require Solution
In Excel macro code as follows
Dim cnnConn As ADODB.Connection
Dim rstRecordset As ADODB.Recordset
Dim cmdCommand As ADODB.Command
' Open the connection.
Set cnnConn = New ADODB.Connection
With cnnConn
.ConnectionString = _
"Provider=Microsoft.Jet.OLEDB.4.0"
.Open wpath_name & "deployment.mdb"
End With
Set cmdCommand = New ADODB.Command
Set cmdCommand.ActiveConnection = cnnConn
With cmdCommand
.CommandText = "Select chnl as CHNL,reg as REG,cust_no as CUSTNO,cust_name AS CUSTNAME,sales_person_no AS SALESCODE,sales_person_name AS SALESNAME,cat AS CAT, value_final as PLAN, value_achieved AS ACH,ach_perc as achp,flag, qty_final as PLANQ, qty_achieved AS ACHQ From transactions WHERE REG =" & "'DXB'" & " and flag = '" & "Y" & "'"
.CommandType = adCmdText
.Execute
End With
Workbooks("deployment_macro.xls").Activate
Worksheets("DXB_PIVOT").Activate
Set rstRecordset = New ADODB.Recordset
Set rstRecordset.ActiveConnection = cnnConn
rstRecordset.Open cmdCommand
' Create PivotTable cache and report.
Set objPivotCache = ActiveWorkbook.PivotCaches.Add( _
SourceType:=xlExternal)
Set objPivotCache.Recordset = rstRecordset
ActiveSheet.PivotTables.Add _
PivotCache:=objPivotCache, _
TableDestination:=Range("A3"), _
TableName:="DXB_PIVOT", _
DefaultVersion:=xlPivotTableVersion10
''ActiveSheet
With ActiveSheet.PivotTables("DXB_PIVOT")
.SmallGrid = False
With .PivotFields("CHNL")
.Orientation = xlPageField
.Position = 1
End With
With .PivotFields("REG")
.Orientation = xlPageField
.Position = 2
End With
With .PivotFields("SALESCODE")
.Orientation = xlPageField
.Position = 3
End With
With .PivotFields("SALESNAME")
.Orientation = xlPageField
.Position = 4
End With
With .PivotFields("CUSTNO")
.Orientation = xlPageField
.Position = 5
End With
With .PivotFields("CUSTNAME")
.Orientation = xlRowField
.Subtotals(1) = False
.Position = 1
End With
With .PivotFields("CAT")
.Orientation = xlColumnField
.Position = 1
End With
With .PivotFields("PLANQ")
.Orientation = xlDataField
.Position = 1
.Caption = "PLANQ "
.NumberFormat = "#,##0_);[Red](#,##0)"
End With
With .PivotFields("PLAN")
.Orientation = xlDataField
.Position = 2
.Caption = "PLAN "
.NumberFormat = "#,##0_);[Red](#,##0)"
End With
With .PivotFields("ACHQ")
.Orientation = xlDataField
.Position = 3
.NumberFormat = "#,##0_);[Red](#,##0)"
.Caption = "ACHQ "
End With
With .PivotFields("ACH")
.Orientation = xlDataField
.Position = 4
.NumberFormat = "#,##0_);[Red](#,##0)"
.Caption = "ACH "
End With
End With
' Close the connections and clean up.
cnnConn.Close
Set cmdCommand = Nothing
Set rstRecordset = Nothing
Set cnnConn = Nothing
The report comes as follows
CUSTNO (All)
SALESNAME (All)
SALESCODE (All)
REG (All)
CHNL (All)
Cat
Customer Name Planq
Plan
Achq
Ach
But i require the modication
Cat
Customer Name Planq Plan Achq Ach
Can you help me
Recent comments
5 years 36 weeks ago
6 years 22 weeks ago
6 years 34 weeks ago
6 years 37 weeks ago
6 years 38 weeks ago
6 years 43 weeks ago
6 years 52 weeks ago
7 years 2 days ago
7 years 3 days ago
7 years 3 days ago