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

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

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

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

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