Hi:
I am exporting a recordset to Excel from Access to create a pivot chart. Everything worked yesterday running on Access and Excel 2007. When I tried to run the same procedure today on a work computer running Access and Excel 2003, I received a run-time error ("The object does not support this property or method.") on the following line:
ActiveSheet.Shapes.AddChart.Select
Below is the whole function:
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("ExpData")
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:="Facility Code", _
ColumnFields:="Month-Year"
With PT.PivotFields("Unit Price")
.Orientation = xlDataField
.Function = xlSum
.Position = 1
End With
ActiveSheet.Shapes.AddChart.Select
ActiveChart.SetSourceData Source:=Sheets("ExpData").Range("F2").CurrentRegion
ActiveWorkbook.ShowPivotChartActiveFields = True
ActiveChart.ChartType = xl3DColumn
ActiveWorkbook.ShowPivotChartActiveFields = False
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
I am exporting a recordset to Excel from Access to create a pivot chart. Everything worked yesterday running on Access and Excel 2007. When I tried to run the same procedure today on a work computer running Access and Excel 2003, I received a run-time error ("The object does not support this property or method.") on the following line:
ActiveSheet.Shapes.AddChart.Select
Below is the whole function:
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("ExpData")
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:="Facility Code", _
ColumnFields:="Month-Year"
With PT.PivotFields("Unit Price")
.Orientation = xlDataField
.Function = xlSum
.Position = 1
End With
ActiveSheet.Shapes.AddChart.Select
ActiveChart.SetSourceData Source:=Sheets("ExpData").Range("F2").CurrentRegion
ActiveWorkbook.ShowPivotChartActiveFields = True
ActiveChart.ChartType = xl3DColumn
ActiveWorkbook.ShowPivotChartActiveFields = False
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
