Using .Net to create Excel Pivot table

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Gary Dunne
    #1

    Using .Net to create Excel Pivot table

    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.Applicati on
    Dim wkBook As Excel.Workbook, wkSheet As Excel.Worksheet

    excelApp = New Excel.Applicati on
    excelApp.Visibl e = False

    wkBook = excelApp.Workbo oks.Add()
    wkSheet = wkBook.Workshee ts("Sheet1")

    Dim connectionStr as String = ...
    Dim tableName as String = "myDb.dbo.Table Name"

    wkBook.Connecti ons.Add("MyConn ection", "Descriptio n", connectionStr,
    tableName, 3)
    wkBook.Connecti ons("MyConnecti on").Refresh( )

    Dim pvtCache As Excel.PivotCach e =
    wkBook.PivotCac hes.Create(Exce l.XlPivotTableS ourceType.xlExt ernal,
    wkBook.Connecti ons("MyConnecti on"), _
    Excel.XlPivotTa bleVersionList. xlPivotTableVer sion11)

    ' 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 ArgumentExcepti on telling me "The
    argument is incorrect" .. but which one ?

    Dim pvtTable As Excel.PivotTabl e

    ' This version throws an ArgumentExcepti on
    pvtTable = wkSheet.PivotTa bles.Add(PivotC ache:=pvtCache,
    TableDestinatio n:=Excel.XlPara meterType.xlRan ge, TableName:="Piv otTable1")
    ' This version (and multiple others) throw a ComException
    pvtTable = wkSheet.PivotTa bles.Add(PivotC ache:=pvtCache,
    TableDestinatio n:=wkSheet.Rang e("A3"), TableName:="Piv otTable1") ' Throws a
    ComException

    Can anyone help with the correct syntax please ?

    Thanks

    Gary





    Tags: None
    • Rod Gill
      #2
      Re: Using .Net to create Excel Pivot table

      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" <gary_dunne@AT. hotmail.dot.com wrote in message
      news:OtpsGAiJJH A.5900@TK2MSFTN GP02.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.Applicati on
      Dim wkBook As Excel.Workbook, wkSheet As Excel.Worksheet
      >
      excelApp = New Excel.Applicati on
      excelApp.Visibl e = False
      >
      wkBook = excelApp.Workbo oks.Add()
      wkSheet = wkBook.Workshee ts("Sheet1")
      >
      Dim connectionStr as String = ...
      Dim tableName as String = "myDb.dbo.Table Name"
      >
      wkBook.Connecti ons.Add("MyConn ection", "Descriptio n", connectionStr,
      tableName, 3)
      wkBook.Connecti ons("MyConnecti on").Refresh( )
      >
      Dim pvtCache As Excel.PivotCach e =
      wkBook.PivotCac hes.Create(Exce l.XlPivotTableS ourceType.xlExt ernal,
      wkBook.Connecti ons("MyConnecti on"), _
      Excel.XlPivotTa bleVersionList. xlPivotTableVer sion11)
      >
      ' 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 ArgumentExcepti on telling me "The
      argument is incorrect" .. but which one ?
      >
      Dim pvtTable As Excel.PivotTabl e
      >
      ' This version throws an ArgumentExcepti on
      pvtTable = wkSheet.PivotTa bles.Add(PivotC ache:=pvtCache,
      TableDestinatio n:=Excel.XlPara meterType.xlRan ge, TableName:="Piv otTable1")
      ' This version (and multiple others) throw a ComException
      pvtTable = wkSheet.PivotTa bles.Add(PivotC ache:=pvtCache,
      TableDestinatio n:=wkSheet.Rang e("A3"), TableName:="Piv otTable1") ' Throws
      a ComException
      >
      Can anyone help with the correct syntax please ?
      >
      Thanks
      >
      Gary
      >
      >
      >
      >
      >

        Comment

        • Gary Dunne
          #3
          Re: Using .Net to create Excel Pivot table

          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-systemsDOTcoDOT nzwrote in message
          news:ex9FgWnJJH A.4772@TK2MSFTN GP03.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" <gary_dunne@AT. hotmail.dot.com wrote in message
          news:OtpsGAiJJH A.5900@TK2MSFTN GP02.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.Applicati on
          >Dim wkBook As Excel.Workbook, wkSheet As Excel.Worksheet
          >>
          >excelApp = New Excel.Applicati on
          >excelApp.Visib le = False
          >>
          >wkBook = excelApp.Workbo oks.Add()
          >wkSheet = wkBook.Workshee ts("Sheet1")
          >>
          >Dim connectionStr as String = ...
          >Dim tableName as String = "myDb.dbo.Table Name"
          >>
          >wkBook.Connect ions.Add("MyCon nection", "Descriptio n", connectionStr,
          >tableName, 3)
          >wkBook.Connect ions("MyConnect ion").Refresh( )
          >>
          >Dim pvtCache As Excel.PivotCach e =
          >wkBook.PivotCa ches.Create(Exc el.XlPivotTable SourceType.xlEx ternal,
          >wkBook.Connect ions("MyConnect ion"), _
          >Excel.XlPivotT ableVersionList .xlPivotTableVe rsion11)
          >>
          >' 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 ArgumentExcepti on telling me "The
          >argument is incorrect" .. but which one ?
          >>
          >Dim pvtTable As Excel.PivotTabl e
          >>
          >' This version throws an ArgumentExcepti on
          >pvtTable = wkSheet.PivotTa bles.Add(PivotC ache:=pvtCache,
          >TableDestinati on:=Excel.XlPar ameterType.xlRa nge,
          >TableName:="Pi votTable1")
          >' This version (and multiple others) throw a ComException
          >pvtTable = wkSheet.PivotTa bles.Add(PivotC ache:=pvtCache,
          >TableDestinati on:=wkSheet.Ran ge("A3"), TableName:="Piv otTable1") ' Throws
          >a ComException
          >>
          >Can anyone help with the correct syntax please ?
          >>
          >Thanks
          >>
          >Gary
          >>
          >>
          >>
          >>
          >>
          >

            Comment

            Previous template Next
            Working...