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?
LVL 13
Who is Participating?
Michael_DAuthor Commented:
Yes, I did try almost the same
But instead of ODBC I am using OLE DB  (data came from MS SQL Server 2000)
Michael_DAuthor Commented:
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_DAuthor Commented:
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
.Connection = "OLEDB; .....

Michael_DAuthor Commented:
Anyway I don't like to ask for refund so I will give the points to EDDYKT
Thank you
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.