Using .Net to create Excel Pivot table

[Gary Dunne]

Hi All,
I'm struggling to find the correct syntax for creating a Pivot table in
an excel file via VB . Net 2005

The Excel help file is not particularly helpful where parameters are
concerned (It rarely mentiones the required data types etc)

The following is as far as I can get;

Dim excelApp As Excel.Application
Dim wkBook As Excel.Workbook, wkSheet As Excel.Worksheet

excelApp = New Excel.Application
excelApp.Visible = False

wkBook = excelApp.Workbooks.Add()
wkSheet = wkBook.Worksheets("Sheet1")

Dim connectionStr as String = ...
Dim tableName as String = "myDb.dbo.TableName"

wkBook.Connections.Add("MyConnection", "Description", connectionStr,
tableName, 3)
wkBook.Connections("MyConnection").Refresh()

Dim pvtCache As Excel.PivotCache =
wkBook.PivotCaches.Create(Excel.XlPivotTableSource Type.xlExternal,
wkBook.Connections("MyConnection"), _
Excel.XlPivotTableVersionList.xlPivotTableVersion1 1)

' The above part works fine

' but creating the Pivot Table throws up a ComException .. if I play with
the parameters I get a very unhelpful ArgumentException telling me "The
argument is incorrect" .. but which one ?

Dim pvtTable As Excel.PivotTable

' This version throws an ArgumentException
pvtTable = wkSheet.PivotTables.Add(PivotCache:=pvtCache,
TableDestination:=Excel.XlParameterType.xlRange, TableName:="PivotTable1")
' This version (and multiple others) throw a ComException
pvtTable = wkSheet.PivotTables.Add(PivotCache:=pvtCache,
TableDestination:=wkSheet.Range("A3"), TableName:="PivotTable1") ' Throws a
ComException

Can anyone help with the correct syntax please ?

Thanks

Gary

[Rod Gill]

Hi,

Best way is to record a macro in Excel of you manually creating the pivot
table, then copy paste the VBA code to VB and modify as needed.

--

Rod Gill

"Gary Dunne" <ga********@AT.hotmail.dot.comwrote in message
news:Ot**************@TK2MSFTNGP02.phx.gbl...

Hi All,
I'm struggling to find the correct syntax for creating a Pivot table in
an excel file via VB . Net 2005

The Excel help file is not particularly helpful where parameters are
concerned (It rarely mentiones the required data types etc)

The following is as far as I can get;

Dim excelApp As Excel.Application
Dim wkBook As Excel.Workbook, wkSheet As Excel.Worksheet

excelApp = New Excel.Application
excelApp.Visible = False

wkBook = excelApp.Workbooks.Add()
wkSheet = wkBook.Worksheets("Sheet1")

Dim connectionStr as String = ...
Dim tableName as String = "myDb.dbo.TableName"

wkBook.Connections.Add("MyConnection", "Description", connectionStr,
tableName, 3)
wkBook.Connections("MyConnection").Refresh()

Dim pvtCache As Excel.PivotCache =
wkBook.PivotCaches.Create(Excel.XlPivotTableSource Type.xlExternal,
wkBook.Connections("MyConnection"), _
Excel.XlPivotTableVersionList.xlPivotTableVersion1 1)

' The above part works fine

' but creating the Pivot Table throws up a ComException .. if I play with
the parameters I get a very unhelpful ArgumentException telling me "The
argument is incorrect" .. but which one ?

Dim pvtTable As Excel.PivotTable

' This version throws an ArgumentException
pvtTable = wkSheet.PivotTables.Add(PivotCache:=pvtCache,
TableDestination:=Excel.XlParameterType.xlRange, TableName:="PivotTable1")
' This version (and multiple others) throw a ComException
pvtTable = wkSheet.PivotTables.Add(PivotCache:=pvtCache,
TableDestination:=wkSheet.Range("A3"), TableName:="PivotTable1") ' Throws
a ComException

Can anyone help with the correct syntax please ?

Thanks

Gary

[Gary Dunne]

Thanks Rod,

I found some VB6 code online that I was able to alter to suit my needs.
(The record macro idea is also very helpful for fillig in the blanks)

Gary

"Rod Gill" <rodATproject-systemsDOTcoDOTnzwrote in message
news:ex**************@TK2MSFTNGP03.phx.gbl...

Hi,

Best way is to record a macro in Excel of you manually creating the pivot
table, then copy paste the VBA code to VB and modify as needed.

--

Rod Gill

"Gary Dunne" <ga********@AT.hotmail.dot.comwrote in message
news:Ot**************@TK2MSFTNGP02.phx.gbl...

>Hi All,
I'm struggling to find the correct syntax for creating a Pivot table in
an excel file via VB . Net 2005

The Excel help file is not particularly helpful where parameters are
concerned (It rarely mentiones the required data types etc)

The following is as far as I can get;

Dim excelApp As Excel.Application
Dim wkBook As Excel.Workbook, wkSheet As Excel.Worksheet

excelApp = New Excel.Application
excelApp.Visible = False

wkBook = excelApp.Workbooks.Add()
wkSheet = wkBook.Worksheets("Sheet1")

Dim connectionStr as String = ...
Dim tableName as String = "myDb.dbo.TableName"

wkBook.Connections.Add("MyConnection", "Description", connectionStr,
tableName, 3)
wkBook.Connections("MyConnection").Refresh()

Dim pvtCache As Excel.PivotCache =
wkBook.PivotCaches.Create(Excel.XlPivotTableSourc eType.xlExternal,
wkBook.Connections("MyConnection"), _
Excel.XlPivotTableVersionList.xlPivotTableVersion 11)

' The above part works fine

' but creating the Pivot Table throws up a ComException .. if I play with
the parameters I get a very unhelpful ArgumentException telling me "The
argument is incorrect" .. but which one ?

Dim pvtTable As Excel.PivotTable

' This version throws an ArgumentException
pvtTable = wkSheet.PivotTables.Add(PivotCache:=pvtCache,
TableDestination:=Excel.XlParameterType.xlRange ,
TableName:="PivotTable1")
' This version (and multiple others) throw a ComException
pvtTable = wkSheet.PivotTables.Add(PivotCache:=pvtCache,
TableDestination:=wkSheet.Range("A3"), TableName:="PivotTable1") ' Throws
a ComException

Can anyone help with the correct syntax please ?

Thanks

Gary