asked on

Create Excel pivot table from within VB6 app (NOT VBA)

Hi guys,

I need to create Excel pivot table from within my vb app
Here is a relevant piece of code:

.......
Dim pc As PivotCache

Set pc = oWBook.PivotCaches.Add(xlExternal)
With pc
Set .Recordset = rstRecordset ' <-Got error 1004 - Application-defined or object-defined error
.CreatePivotTable Range("A3")
End With
.....

rstRecordset at that moment is open and contains all requested data.
What am I doing wrong?

ASKER CERTIFIED SOLUTION

EDDYKT

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Michael_D

ASKER

EDDYKT,
Yes, I did try almost the same
But instead of ODBC I am using OLE DB (data came from MS SQL Server 2000)

Michael_D

ASKER

When i am using ODBC driver to connect to Server the code works.

Is this one of limitations of Excel? Or I have somehow indicate that I am using OLEDB?

Michael_D

ASKER

OK I got it!

Here is the working code:
Dim pc As PivotCache
Dim pt As PivotTable
Set pc = oWBook.PivotCaches.Add(xlExternal)
With pc
.Connection = "OLEDB;Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=<MyDB>;Data Source=<MySQLServer>"
.CommandType = xlCmdSql
.CommandText = "SELECT MyRowField,MyColField,MyDataField FROM MyTable"
Set pt = .CreatePivotTable(oWSheet.Range("A3"))
End With

pt.AddFields "MyRowField", "MyColField"
pt.PivotFields("MyDataField").Orientation = xlDataField
pt.ColumnGrand = False
pt.RowGrand = False

In my first attempts I forgot to put OLEDB in
|
V
.Connection = "OLEDB; .....

Michael_D

ASKER

Anyway I don't like to ask for refund so I will give the points to EDDYKT
Thank you