create pivot table programmatically

A

anziga

Board Regular
Joined
Feb 4, 2011
Messages
55
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?
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Sort by date Sort by votes
I'm not familiar with VB.NET but this is how I create Pivot Tables in VBA:
Code: 
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
 
Upvote 0
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:
Code: 
Dim ptTable As Excel.PivotTable = excel_app.ThisWorkbook.Worksheets("Sheet2").PivotTables.Add(PivotCache:=ptCache, _
        TableDestination:=excel_app.ActiveCell, TableName:="Expenses_summary")
 
Upvote 0
I changed the code for pivottable completely, but still no luck.
Code: 
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?
 
Upvote 0
I was playing around with different approaches and found out that is was the SourceData which is causing the problem.
Code: 
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?
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Solved
Extracting out Title in PDF -> Word conversion
Replies
8
Views
146
NdNoviceHlp
NdNoviceHlp
J
  • Question
Filter Pivot Table Generated From PowerQuery Data Model
Replies
1
Views
95
jdorby
J
G
  • Question
Blood pressure data summary Pivot Table - unable to create the desired result
Replies
3
Views
102
awoohaw
awoohaw
H
  • Question
Make an slicer in vba excel automatically
Replies
1
Views
95
Domenic
D
A
  • Solved
Pivot Table Sum giving Zeros but count works
Replies
5
Views
606
RoryA
RoryA

Forum statistics

Threads
1,215,046
Messages
6,122,855
Members
449,096
Latest member
Erald

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top