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

Posted on 2005-04-20
Last Modified: 2008-02-01
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?
Question by:Michael_D
    LVL 26

    Accepted Solution

    LVL 13

    Author Comment

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

    Author Comment

    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?


    LVL 13

    Author Comment

    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; .....

    LVL 13

    Author Comment

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

    Featured Post

    What Security Threats Are You Missing?

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    How to inactivate a tab on a form based on permissions? 11 56
    z = x + y – 1 6 49
    Access 2016 VB code 9 71
    using Access 8 41
    If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
    Article by: Martin
    Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now