error in creating a pivot table from windows application using vb.net

Hi,
I have been receiving the error "Exception from HRESULT: 0x800A03EC" near (***) in the code below,

Code:

---

Imports Microsoft.Office.Interop.Excel
Imports System.Runtime.InteropServices
Imports System.Data.OleDb
Public Class excel
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim conString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\New Folder\Lusail_Test_DB.accdb;Jet OLEDB:Database Password=Lusail@HTI_HTV"
Dim myConnection As New System.Data.OleDb.OleDbConnection(conString)
myConnection.Open()
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.Add(SourceType:=XlPivotTableSourceType.xlExternal)
With ptCache
.Connection = conString
.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.xlRowField
.PivotFields("Trade").Orientation = XlPivotFieldOrientation.xlRowField
.PivotFields("Duration").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
Private Sub excel_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
End Sub
End Class

---

Can anyone please provide me the coorect code to create a pivot table from vb.net?

Welcome to the forums! :wave:

Please make use of [CODE] tags when posting code in future, as explained here :

http://www.codeguru.com/forum/showthread.php?t=403073

Thanks!

Hannes

And, if you wanted to try it, it's probably been asked before.

Found one: http://www.codeproject.com/KB/aspnet...px?msg=2399759