Create a 3dcolumn pivot chart in Excel using VBA in Access 1

[iojbr]

Hi:

I have the following code to create a pivot table in Excel using data exported to Excel from an Access database. How can I create an XL3DColumn pivot chart based on this data. There are three columns of exported data: Field Names are: "site number", "quarter", and "cost". In the chart, I want "site number" and "quarter" to be on its own, separate axis. The third axis would be an aggregate value Sum("cost").


Option Explicit
Function CreateChart(strSourceName As String, _
StrFileName As String)

Dim xlApp As Excel.Application
Dim xlWrkbk As Excel.Workbook
Dim xlChartObj As Excel.Chart
Dim xlSourceRange As Excel.Range
Dim xlColPoint As Excel.Point
Dim PT As Excel.PivotTable
Dim PTCache As Excel.PivotCache
Dim WSD As Excel.Worksheet
Dim PRange As Excel.Range
Dim FinalRow As Long
Dim FinalCol As Long
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, _
strSourceName, StrFileName, False
Set xlApp = CreateObject("Excel.Application")
Set xlWrkbk = xlApp.Workbooks.Open(StrFileName)
Set WSD = xlWrkbk.Worksheets("Test")
Set xlSourceRange = _
WSD.Range("a1").CurrentRegion

For Each PT In WSD.PivotTables
PT.TableRange2.Clear
Next PT
FinalRow = WSD.Cells(xlApp.Rows.Count, 1).End(xlUp).Row
FinalCol = WSD.Cells(1, xlApp.Columns.Count).End(xlToLeft).Column
Set PRange = WSD.Cells(1, 1).Resize(FinalRow, FinalCol)
Set PTCache = xlWrkbk.PivotCaches.Add(SourceType:=xlDatabase, _
SourceData:=PRange)
Set PT = PTCache.CreatePivotTable(TableDestination:=WSD.Cells(2, FinalCol + 2), _
TableName:="PivotTable1")
PT.AddFields RowFields:="site Number", _
ColumnFields:="Quarter"
With PT.PivotFields("Cost")
.Orientation = xlDataField
.Function = xlSum
.Position = 1
End With

With xlWrkbk
.Save
.Close
End With

xlApp.Quit

Exit_CreateChart:
Set xlSourceRange = Nothing
Set xlColPoint = Nothing
Set xlChartObj = Nothing
Set xlWrkbk = Nothing
Set xlApp = Nothing
Exit Function

Err_CreateChart:

MsgBox CStr(Err) & " " & Err.Description
Resume Exit_CreateChart

End Function




The code I used came from two different sources.

Just to give credit, the code I used came from two different sources:

http://support.microsoft.com/kb/202169

http://www.brainbell.com/tutorials/Excel_VBA/Build_A_Pivot_Table_In_Excel_VBA.html

 

[SkipVought]

Hi,

Have you tried actually creating it on a sheet in Excel?

If you can do that, then turn on your macro recorder and do it again.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[iojbr]

Thanks Skip. It worked out great. However, from line below, is there any way to define the range for the source data in a way so I don't have to go back and changes to it everytime the pivot table expands due to more data.


ActiveChart.SetSourceData Source:=Range("'Test'!$F$2:$I$9")

 

[SkipVought]

Assuming that your data is contiguous and isolated from other data...

ActiveChart.SetSourceData Source:= Sheets("Test").Range("F2").CurrentRegion

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}