Question creating pivot tables in excel

[swethajain]

Hi,
Can anyone please please give me the code for creating a pivot table in excel from vb.net. it is very urgent. at present i am having this code. i have added the microsoft.excel.12.0 library also.

code:
Imports Microsoft.Office.Interop.Excel
Imports System.Runtime.InteropServices
Imports System.Data.OleDb

Public Class pivot

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim myConnection As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\New Folder\Lusail_Test_DB.accdb;Jet OLEDBatabase Password=Lusail@HTI_HTV")
Dim str_from As String = "select * from record"
Dim ds As New DataSet
Dim da As New OleDbDataAdapter(str_from, myConnection)
da.Fill(ds, "Record")

Dim xlApp As Application


Try
xlApp = Marshal.GetActiveObject("Excel.Application")
Catch ex As COMException
xlApp = New Application
End Try

Dim xlWBook As Workbook = xlApp.Workbooks.Add(XlWBATemplate.xlWBATWorksheet)
Dim xlWSheet As Worksheet = CType(xlWBook.Worksheets(1), Worksheet)
Dim xlRange As Range = CType(xlWSheet, Worksheet).Range("B2")
Dim ptCache As PivotCache = xlWBook.PivotCaches.Create(SourceType:=XlPivotTableSourceType.xlExternal)



With ptCache
.Connection = myConnection
.CommandText = str_from
.CommandType = XlCmdType.xlCmdSql

End With


Dim ptTable As PivotTable = xlWSheet.PivotTables.Add(PivotCache:=ptCache, TableDestination:=xlRange, TableName:="Record")

With ptTable
.ManualUpdate = True
.PivotFields("Construction package").Orientation = XlPivotFieldOrientation.xlRowField
.PivotFields("Region").Orientation = XlPivotFieldOrientation.xlDataField
.PivotFields("Trade").Orientation = XlPivotFieldOrientation.xlDataField
.Format(XlPivotFormatType.xlReport2)
.ManualUpdate = False
End With

xlWBook.SaveAs("c:\Report.xls")

With xlApp
.Visible = True
.UserControl = True
End With

ptTable = Nothing
ptCache = Nothing
xlWSheet = Nothing
xlWBook = Nothing
xlApp = Nothing
End Sub
End Class

I have been getting the error: The parameter is incorrect. (Exception from HRESULT: 0x80070057 (E_INVALIDARG)) near .connection=myconnection

 

[MattP]

The PivotCache.Connection is looking for a string not an OleDbConnection.

Try something like this:

        Dim conString As String = "Provider=Micros oft.ACE.OLEDB.12.0;Data Source=C:\New Folder\Lusail_Test_DB.accdb;Jet OLEDBatabase Password=Lusail@HTI_HTV"
        Dim myConnection As New System.Data.OleDb.OleDbConnection(conString)
        ...
        ptCache.Connection = conString

Pretty good example here for reference: Andrew Whitechapel : Creating a PivotTable Programmatically

 

[swethajain]

Hi,
i tried it but again it is showing the same error there.

I saw that link before but by using that code also i am getting error