create pivot table programmatically

[anziga]

I'm using VB.NET to read database and write data into Excel document. Now I need to create a pivot table into same Excel document, but I have no idea how to do this and couldn't find any useful samples. I tried to record a macro when creating pivot table manually, but I couldn't get that code to work in VB.NET. What I need to do is to read certain range of cells from a worksheet and write a pivot table into another worksheet. Does anyone have a sample how to do this or a place where to obtain more info?

 

[Misca]

I'm not familiar with VB.NET but this is how I create Pivot Tables in VBA:

Sub CreatePivotTable()

Dim Rng As Range
Dim PT As PivotTable
Dim PC As PivotCache

'Set the data range for the pivot cache:
Set Rng = Range("A1").CurrentRegion

'Create the Pivot Cache (if you want to put it in another workbook, change ActiveWorkook with a proper workbook reference):
Set PC = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=Rng)

'Create a Pivot Table from the cache:
Set PT = PC.CreatePivotTable(TableDestination:=Sheets.Add.Range("A3"), TableName:="MyPivotTable")

'Build the Pivot Table:
With PT
    With .PivotFields("Date")
        .Orientation = xlPageField
        .Position = 1
    End With

    With .PivotFields("Item")
        .Orientation = xlRowField
        .Caption = "Product"
    End With
    
    With .PivotFields("Cash")
        .Orientation = xlDataField
        .Function = xlSum
    End With
End With

End Sub

 

[anziga]

Yeah well, I can get a working VBA code by recording that macro and tweaking a code for the range a bit, but I'm stuck with the VB.NET code... I got the code to read the datasource into pivotcache, but failed to write the actual pivottable. I got following error message:

System.Runtime.InteropServices.COMException was unhandled
ErrorCode=-2146827284
Message=Exception from HRESULT: 0x800A03EC
Source=Microsoft.Office.Interop.Excel
StackTrace:
at Microsoft.Office.Interop.Excel.ApplicationClass.get_ThisWorkbook()

and debugger points to this line:

Dim ptTable As Excel.PivotTable = excel_app.ThisWorkbook.Worksheets("Sheet2").PivotTables.Add(PivotCache:=ptCache, _
        TableDestination:=excel_app.ActiveCell, TableName:="Expenses_summary")

 

[anziga]

I changed the code for pivottable completely, but still no luck.

excel_app.ThisWorkbook.PivotCaches.Create(SourceType:=Excel.XlPivotTableSourceType.xlExternal, SourceData:= _
            excel_app.Worksheets("Sheet1!R3C1:R" & lastRow & "C5")).CreatePivotTable( _
            TableDestination:=excel_app.Worksheets("Sheet2!R3C1"), TableName:="PivotTable1")
        ' excel_app.Worksheets("Sheet2").Select()
        ' excel_app.Cells(3, 1).Select()
        excel_app.ActiveSheet.PivotTables("PivotTable1").AddDataField(excel_app.ActiveSheet.PivotTables( _
            "PivotTable1").PivotFields("Value(NZ$)"), "Sum of Value(NZ$)", Excel.XlConsolidationFunction.xlSum)
        With excel_app.ActiveSheet.PivotTables("PivotTable1").PivotFields("Category")
            .Orientation = Excel.XlPivotFieldOrientation.xlRowField
            .Position = 1
        End With

PivotCaches create function line results following error message, I've been trying to modify it in various ways, but it just doesn't work.
System.Runtime.InteropServices.COMException was unhandled
ErrorCode=-2146827284
Message=Exception from HRESULT: 0x800A03EC
Source=Microsoft.Office.Interop.Excel
StackTrace:
at Microsoft.Office.Interop.Excel.ApplicationClass.get_ThisWorkbook()

Any ideas?

 

[anziga]

I was playing around with different approaches and found out that is was the SourceData which is causing the problem.

With ptCache
            ' .SourceData = excel_app.Selection ' Exception from HRESULT: 0x800A03EC
            ' .SourceData = excel_app.ActiveWorkbook.Worksheets("Sheet1!R3C1:R" & lastRow & "C5") ' invalid index
            .SourceData = excel_app.Range("A3:E" & lastRow) ' Exception from HRESULT: 0x800A03EC
            ' .SourceData = "Sheet1!R3C1:R" & lastRow & "C5" ' Exception from HRESULT: 0x800A03EC
            ' .SourceData = range ' Exception from HRESULT: 0x800A03EC
End With

I have tried various ways but nothing works. Am I missing something, format, additional conditions, or what?