Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run-Time Error 1004 When Running Pivot Table Code
Hey folks got a small problem with some code I'm using that results in the
following error message: "Run-Time Error '1004': The pivotTable field name is not valid. To create a pivottable report, you must use data that is organized as a list with labeled columns. If you are changing the name of a pivottable field, you must type a new name for the field." Here's the code: #### #### Code Starts #### #### Option Explicit Sub main_prog() Call td_metrics_import Call pt_td_metrics("Pivot_Page1", "PivotTable1", "PivotTable2") Call pt_td_metrics("Pivot_Page2", "PivotTable3", "PivotTable4") Call create_graph End Sub Sub pt_td_metrics(Chrt_Pg_Name As String, p_tbl_name1 As Variant, p_tbl_name2 As Variant) Dim ws2 As Worksheet Dim ws1 As Worksheet Dim wb As Workbook Dim pt As PivotTable Dim ptCache As PivotCache Dim prange As Range Dim lastRow As Long Dim lastCol As String Dim i As Integer Set pt = Nothing Set prange = Nothing Set ptCache = Nothing Set ws1 = Nothing Set ws2 = Nothing Set wb = Workbooks("td_metrics_excel3.xls") Set ws1 = wb.Worksheets("Data_Page") wb.Worksheets.Add after:=wb.Sheets(wb.Sheets.Count), Count:=1 wb.Worksheets(wb.Worksheets.Count).Name = Chrt_Pg_Name Set ws2 = wb.Worksheets(Chrt_Pg_Name) lastRow = ws1.Cells(65536, 1).End(xlUp).Row lastCol = ws1.Range("IV1").End(xlToLeft).Column Set prange = ws1.Cells(1, 1).Resize(lastRow, lastCol) Set ptCache = wb.PivotCaches.Add(xlDatabase, prange.Address) Set pt = ptCache.CreatePivotTable(ws2.Cells(1, 1), p_tbl_name1) pt.AddFields RowFields:=Array("BG_DETECTION_DATE", "BG_SEVERITY"), ColumnFields:=Array("BG_PROJECT_DB", "BG_USER_01") With pt.PivotFields("BG_USER_08") .Orientation = xlDataField .Function = xlCount .Position = 1 End With pt.PivotFields("BG_DETECTION_DATE").LabelRange.Gro up Start:=True, End:=True, periods:=Array(False, False, False, False, True, True, True) With pt .ColumnGrand = False .RowGrand = False End With pt.PivotFields("BG_PROJECT_DB").Subtotals(1) = True pt.PivotFields("BG_PROJECT_DB").Subtotals(1) = False pt.PivotFields("BG_DETECTION_DATE").Subtotals(1) = True pt.PivotFields("BG_DETECTION_DATE").Subtotals(1) = False Set pt = Nothing Set pt = ptCache.CreatePivotTable(ws2.Cells(40, 1), p_tbl_name2) pt.AddFields RowFields:="BG_DETECTION_DATE", ColumnFields:="BG_PROJECT_DB" With pt.PivotFields("BG_USER_08") .Orientation = xlDataField .Function = xlCount .Position = 1 End With pt.PivotFields("BG_DETECTION_DATE").LabelRange.Gro up Start:=True, End:=True, periods:=Array(False, False, False, False, True, True, True) With pt .ColumnGrand = False .RowGrand = False End With pt.PivotFields("BG_PROJECT_DB").Subtotals(1) = True pt.PivotFields("BG_PROJECT_DB").Subtotals(1) = False pt.PivotFields("BG_DETECTION_DATE").Subtotals(1) = True pt.PivotFields("BG_DETECTION_DATE").Subtotals(1) = False End Sub Sub td_metrics_import() Dim db As Database Dim qdf As QueryDef Dim rs As Recordset Dim accapp As Access.Application Dim wb As Workbook Dim ws1 As Worksheet Dim path As String Dim i As Integer Dim tblName As String path = "C:\Documents and Settings\hendersr\My Documents\td_metrics.mdb" tblName = "tbl_initial_td_select" Set accapp = New Access.Application accapp.OpenCurrentDatabase (path) accapp.Run ("qry_run") accapp.Quit Set db = Workspaces(0).OpenDatabase(path, ReadOnly:=False) Set wb = Workbooks("td_metrics_excel3.xls") Set ws1 = wb.Worksheets("Data_Page") Application.DisplayAlerts = False For i = 1 To wb.Charts.Count wb.Charts(i).Delete Next i ws1.Cells.ClearContents For i = wb.Worksheets.Count To 1 Step -1 If wb.Worksheets(i).Name < "Data_Page" Then wb.Worksheets(i).Delete End If Next i Application.DisplayAlerts = True Set rs = db.TableDefs(tblName).OpenRecordset For i = 0 To rs.Fields.Count - 1 If i = 0 Then ws1.Range("IV1").End(xlToLeft) = rs.Fields(i).Name Else ws1.Range("IV1").End(xlToLeft).Offset(0, 1) = rs.Fields(i).Name End If Next i ws1.Range("IV2").End(xlToLeft).CopyFromRecordset rs End Sub Sub create_graph() Charts.Add With ActiveChart .SetSourceData Source:=Sheets("Pivot_Page1").Cells(1, 1) .Location Whe=xlLocationAsNewSheet .PivotLayout.PivotTable.PivotFields("BG_PROJECT_DB ").Orientation = xlHidden .PivotLayout.PivotTable.PivotFields("BG_DETECTION_ DATE").Orientation = xlHidden .PivotLayout.PivotTable.PivotFields("BG_USER_01"). Orientation = xlHidden With .PivotLayout.PivotTable.PivotFields("BG_SEVERITY") .Orientation = xlColumnField .Position = 1 End With .PlotArea.Interior.ColorIndex = xlNone End With End Sub #### #### Code Ends #### #### The code breaks on the line: #### Set pt = ptCache.CreatePivotTable(ws2.Cells(1, 1), p_tbl_name1) #### in the pt_td_metrics sub. If I comment out one of the lines where I call the sub pt_td_metrics i.e.: #### #### Sub main_prog() Call td_metrics_import Call pt_td_metrics("Pivot_Page1", "PivotTable1", "PivotTable2") 'Call pt_td_metrics("Pivot_Page2", "PivotTable3", "PivotTable4") Call create_graph End Sub #### #### Everything works fine. So anyone got any ideas? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|