wbshrk
12-27-2010, 11:45 AM
Ok I can get access to open the xls file, create the new sheet for the pivot, and create a flawed pivot. The code does not seem to add .PivotFields("total").Orientation = xlDataField so there is no data field. All the pivot shows is the row and column header that is it. What am I missing? Ver. Access 2007 XLS 2007 file.
Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open(Me.txtfilename.Value)
objExcel.Visible = False
Set colWorksheets = objExcel.Worksheets
objExcel.Sheets.Add.Name = "Summary"
With objExcel.ActiveWorkbook.PivotCaches.Add(SourceType:=1, SourceData:=objExcel.Sheets("counts").UsedRange).CreatePivotTable(TableDestination:="Summary!R3C1", TableName:="PivotTable1")
.AddFields RowFields:="NAME", ColumnFields:=Array("MONTH_Name", "total")
.PivotFields("total").Orientation = xlDataField
End With
Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open(Me.txtfilename.Value)
objExcel.Visible = False
Set colWorksheets = objExcel.Worksheets
objExcel.Sheets.Add.Name = "Summary"
With objExcel.ActiveWorkbook.PivotCaches.Add(SourceType:=1, SourceData:=objExcel.Sheets("counts").UsedRange).CreatePivotTable(TableDestination:="Summary!R3C1", TableName:="PivotTable1")
.AddFields RowFields:="NAME", ColumnFields:=Array("MONTH_Name", "total")
.PivotFields("total").Orientation = xlDataField
End With