| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
create pivot table in VBA
I am trying to create a very simple pivot table from a datafile in a macro. I
can manually do all the steps but when I record the steps I get the error Add Fields method of PivotTable Class failed. Can someone point out this newbie's error? thanks Sub test1() ' ' test1 Macro ' Macro recorded 3/22/2007 by LUPF ' ' Workbooks.OpenText Filename:="M:\Reporting\TES_CODER.txt", Origin:=437, _ StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _ ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False _ , Space:=False, Other:=True, OtherChar:="~", FieldInfo:=Array(Array(1, 1 _ ), Array(2, 1), Array(3, 1), Array(4, 3), Array(5, 1), Array(6, 2), Array(7, 1), Array(8, 1), _ Array(9, 1)), TrailingMinusNumbers:=True Columns("G:G").Select Selection.NumberFormat = "0.00" Range("A1:I1").Select Selection.Font.Bold = True Cells.Select Cells.EntireColumn.AutoFit ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _ "TES_CODER!C1:C2").CreatePivotTable TableDestination:="", TableName:= _ "PivotTable1", DefaultVersion:=xlPivotTableVersion10 ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1) ActiveSheet.Cells(3, 1).Select ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:="Modified User" '******* ERROR HERE ******** ActiveSheet.PivotTables("PivotTable1").PivotFields ("Transaction").Orientation _ = xlDataField ActiveWorkbook.ShowPivotTableFieldList = False Application.CommandBars("PivotTable").Visible = False End Sub rdavid2 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
create pivot table in VBA
My first guess is that you don't have a header in column A or B that is
exactly: "Modified User" Did you really mean to use just columns 1 and 2? "TES_CODER!C1:C2" rdavid2 wrote: I am trying to create a very simple pivot table from a datafile in a macro. I can manually do all the steps but when I record the steps I get the error Add Fields method of PivotTable Class failed. Can someone point out this newbie's error? thanks Sub test1() ' ' test1 Macro ' Macro recorded 3/22/2007 by LUPF ' ' Workbooks.OpenText Filename:="M:\Reporting\TES_CODER.txt", Origin:=437, _ StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _ ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False _ , Space:=False, Other:=True, OtherChar:="~", FieldInfo:=Array(Array(1, 1 _ ), Array(2, 1), Array(3, 1), Array(4, 3), Array(5, 1), Array(6, 2), Array(7, 1), Array(8, 1), _ Array(9, 1)), TrailingMinusNumbers:=True Columns("G:G").Select Selection.NumberFormat = "0.00" Range("A1:I1").Select Selection.Font.Bold = True Cells.Select Cells.EntireColumn.AutoFit ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _ "TES_CODER!C1:C2").CreatePivotTable TableDestination:="", TableName:= _ "PivotTable1", DefaultVersion:=xlPivotTableVersion10 ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1) ActiveSheet.Cells(3, 1).Select ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:="Modified User" '******* ERROR HERE ******** ActiveSheet.PivotTables("PivotTable1").PivotFields ("Transaction").Orientation _ = xlDataField ActiveWorkbook.ShowPivotTableFieldList = False Application.CommandBars("PivotTable").Visible = False End Sub rdavid2 -- Dave Peterson |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
create pivot table in VBA
Dave,
I have a column called "Modified User". I have requested that all the column names get the spaces replaced with under scores. I also tried clearing away the extra columns to get to just what was needed ( where I got it down to C1:C2). I figure it is something really simple that I am missing. bob "Dave Peterson" wrote: My first guess is that you don't have a header in column A or B that is exactly: "Modified User" Did you really mean to use just columns 1 and 2? "TES_CODER!C1:C2" rdavid2 wrote: I am trying to create a very simple pivot table from a datafile in a macro. I can manually do all the steps but when I record the steps I get the error Add Fields method of PivotTable Class failed. Can someone point out this newbie's error? thanks Sub test1() ' ' test1 Macro ' Macro recorded 3/22/2007 by LUPF ' ' Workbooks.OpenText Filename:="M:\Reporting\TES_CODER.txt", Origin:=437, _ StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _ ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False _ , Space:=False, Other:=True, OtherChar:="~", FieldInfo:=Array(Array(1, 1 _ ), Array(2, 1), Array(3, 1), Array(4, 3), Array(5, 1), Array(6, 2), Array(7, 1), Array(8, 1), _ Array(9, 1)), TrailingMinusNumbers:=True Columns("G:G").Select Selection.NumberFormat = "0.00" Range("A1:I1").Select Selection.Font.Bold = True Cells.Select Cells.EntireColumn.AutoFit ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _ "TES_CODER!C1:C2").CreatePivotTable TableDestination:="", TableName:= _ "PivotTable1", DefaultVersion:=xlPivotTableVersion10 ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1) ActiveSheet.Cells(3, 1).Select ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:="Modified User" '******* ERROR HERE ******** ActiveSheet.PivotTables("PivotTable1").PivotFields ("Transaction").Orientation _ = xlDataField ActiveWorkbook.ShowPivotTableFieldList = False Application.CommandBars("PivotTable").Visible = False End Sub rdavid2 -- Dave Peterson |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
create pivot table in VBA
If you know the headers are in the first line of the text file--but you can't
trust the spelling, you could change your code to just overwrite those headers: After you import the data, but before the pivottable: activesheet.range("a1").resize(1,2).value _ = array("firstheaderhere", "Modified User") rdavid2 wrote: Dave, I have a column called "Modified User". I have requested that all the column names get the spaces replaced with under scores. I also tried clearing away the extra columns to get to just what was needed ( where I got it down to C1:C2). I figure it is something really simple that I am missing. bob "Dave Peterson" wrote: My first guess is that you don't have a header in column A or B that is exactly: "Modified User" Did you really mean to use just columns 1 and 2? "TES_CODER!C1:C2" rdavid2 wrote: I am trying to create a very simple pivot table from a datafile in a macro. I can manually do all the steps but when I record the steps I get the error Add Fields method of PivotTable Class failed. Can someone point out this newbie's error? thanks Sub test1() ' ' test1 Macro ' Macro recorded 3/22/2007 by LUPF ' ' Workbooks.OpenText Filename:="M:\Reporting\TES_CODER.txt", Origin:=437, _ StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _ ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False _ , Space:=False, Other:=True, OtherChar:="~", FieldInfo:=Array(Array(1, 1 _ ), Array(2, 1), Array(3, 1), Array(4, 3), Array(5, 1), Array(6, 2), Array(7, 1), Array(8, 1), _ Array(9, 1)), TrailingMinusNumbers:=True Columns("G:G").Select Selection.NumberFormat = "0.00" Range("A1:I1").Select Selection.Font.Bold = True Cells.Select Cells.EntireColumn.AutoFit ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _ "TES_CODER!C1:C2").CreatePivotTable TableDestination:="", TableName:= _ "PivotTable1", DefaultVersion:=xlPivotTableVersion10 ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1) ActiveSheet.Cells(3, 1).Select ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:="Modified User" '******* ERROR HERE ******** ActiveSheet.PivotTables("PivotTable1").PivotFields ("Transaction").Orientation _ = xlDataField ActiveWorkbook.ShowPivotTableFieldList = False Application.CommandBars("PivotTable").Visible = False End Sub rdavid2 -- Dave Peterson -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
create pivot table in VBA
Dave,
Thanks for the tip on the column headers. I had the column headers changed and now the macro runs and creates the pivot table. thanks bob "Dave Peterson" wrote: If you know the headers are in the first line of the text file--but you can't trust the spelling, you could change your code to just overwrite those headers: After you import the data, but before the pivottable: activesheet.range("a1").resize(1,2).value _ = array("firstheaderhere", "Modified User") rdavid2 wrote: Dave, I have a column called "Modified User". I have requested that all the column names get the spaces replaced with under scores. I also tried clearing away the extra columns to get to just what was needed ( where I got it down to C1:C2). I figure it is something really simple that I am missing. bob "Dave Peterson" wrote: My first guess is that you don't have a header in column A or B that is exactly: "Modified User" Did you really mean to use just columns 1 and 2? "TES_CODER!C1:C2" rdavid2 wrote: I am trying to create a very simple pivot table from a datafile in a macro. I can manually do all the steps but when I record the steps I get the error Add Fields method of PivotTable Class failed. Can someone point out this newbie's error? thanks Sub test1() ' ' test1 Macro ' Macro recorded 3/22/2007 by LUPF ' ' Workbooks.OpenText Filename:="M:\Reporting\TES_CODER.txt", Origin:=437, _ StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _ ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False _ , Space:=False, Other:=True, OtherChar:="~", FieldInfo:=Array(Array(1, 1 _ ), Array(2, 1), Array(3, 1), Array(4, 3), Array(5, 1), Array(6, 2), Array(7, 1), Array(8, 1), _ Array(9, 1)), TrailingMinusNumbers:=True Columns("G:G").Select Selection.NumberFormat = "0.00" Range("A1:I1").Select Selection.Font.Bold = True Cells.Select Cells.EntireColumn.AutoFit ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _ "TES_CODER!C1:C2").CreatePivotTable TableDestination:="", TableName:= _ "PivotTable1", DefaultVersion:=xlPivotTableVersion10 ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1) ActiveSheet.Cells(3, 1).Select ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:="Modified User" '******* ERROR HERE ******** ActiveSheet.PivotTables("PivotTable1").PivotFields ("Transaction").Orientation _ = xlDataField ActiveWorkbook.ShowPivotTableFieldList = False Application.CommandBars("PivotTable").Visible = False End Sub rdavid2 -- Dave Peterson -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| how to create pivot table from existing pivot table in excel 2007 | Excel Discussion (Misc queries) | |||
| can I create pivot table from two other pivot tables? | Excel Discussion (Misc queries) | |||
| How do I create multiple pivot charts from one pivot table? | Charts and Charting in Excel | |||
| pivot table: create worksheetsheet using pivot table | Excel Discussion (Misc queries) | |||
| How do I create a pivot table if the pivot table icon or menu ite. | Charts and Charting in Excel | |||
Linear Mode
