Create a Pivottable

[dace]

Hi everyone-

I can't seem to create a pivottable on an excel worksheet in-code.

I have:

Dim excelApp = applicationObject
Dim workBooks As Excel.Workbooks = excelApp.Workbooks
Dim activeBook As Excel.Workbook = workBooks(1)
Dim workSheets As Excel.Sheets = activeBook.Worksheets
Dim activeSheet As Excel.Worksheet = workSheets(1)

Dim ConnectString
Dim DestinRange As Excel.Range
Dim PivotName As String

ConnectString = "Driver={SQL Server};" & "Server=localhost;" & "Database=testing;" & "Trusted_Connection=yes SELECT col1, col2 FROM testing"

DestinRange = activeSheet.Range("B8")
PivotName = "Pivot1"
activeSheet.PivotTableWizard(????????????????

I'm stuck at the line above. How would I create a new pivottable and have it display in excel?

Thanks!

 

[dace]

I managed to find the info I needed, and the following works nicely:

activeBook.PivotCaches.Add( _
SourceType:=Excel.XlPivotTableSourceType.xlDatabase, _ SourceData:="____").CreatePivotTable( _
TableDestination:="[Book1]Sheet1!R9C6", _
TableName:="PivotTable1")