Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2003
    Posts
    1

    Unanswered: Excel Pivot table with VBA

    Hi,
    I have a question of query database for SQL server and display summary result on Excel Pivot Table. I have been write a VBA marco to connect SQL server , retrieve data and display on Excel Pivot Table as list in below:


    Sub connectSQLObject()
    Dim QArray As Variant
    Dim Range1 As Range
    Dim ConnectString As String
    Dim PivotName As String
    Dim Pivot3 As PivotTable

    PivotName = "Pivot3"
    Worksheets(1).Select
    Set Range1 = Worksheets(1).Range("A30")

    ConnectString = "ODBC;" & "DBQ=" & ThisWorkbook.Path & "\crmdbv310_RnD.MDF;" & "Driver={sql server};
    SERVER=IMSBTPMDEV ;UID=Research ;PWD=Research ;DATABASE=crmdbv310_RnD"

    QArray = Array(ConnectString, "SELECT * FROM tblCustomer WHERE CustCode < 1000")

    PivotName = "Pivot3"
    Worksheets(1).PivotTableWizard _
    SourceType:=xlExternal, _
    SourceData:=QArray, _
    tableDestination:=Range1, _
    tableName:=PivotName, BackgroundQuery:=False
    Set Pivot3 = Worksheets(1).PivotTables(PivotName)

    With Pivot3
    .PivotFields("CustCode").Orientation = xlColumnField
    .PivotFields("CustName").Orientation = xlRowField
    .PivotFields("CompRegNo").Orientation = xlDataField
    End With

    End Sub


    My problem are:
    1. Connect SQL string problem:
    I have writed a connection string statement for SQL server as show in below, this connection string is working but EXCEL always prompt a dialog box required me re-
    enter password and user_id. (Note: the Database User_ID and Password are same that is 'Research') Could you help me debug below connection string. Thank you.
    ConnectString = "ODBC;" & "DBQ=" & ThisWorkbook.Path & "\crmdbv310_RnD.MDF;" & "Driver={sql server};
    SERVER=IMSBTPMDEV ;UID=Research ;PWD=Research ;DATABASE=crmdbv310_RnD"

    2. SQL query statement limitation ?
    If I used simple SQL statement to retreive data and display out via Excel Pivot Table, the pivot table can easy display out the relate data fields as show in below
    code:
    .......
    QArray = Array(ConnectString, "SELECT * FROM tblCustomer WHERE CustCode < 1000")

    .......
    .PivotFields("CustCode").Orientation = xlColumnField
    .PivotFields("CustName").Orientation = xlRowField
    .PivotFields("CompRegNo").Orientation = xlDataField
    ........

    Above query statement just limits on retreive data filed on a table from database. I have been tried join to table and display data but Excel cannot execute. The code
    as show in below:
    .......
    QArray = Array(ConnectString, "SELECT * FROM tblCustomer.StateID ,
    tblAddressState.StateName, tblCustomer.CustName , tblCustomer.CustCode, tblCustomer.CompRegNo WHERE
    tblCustomer.StateId = tblAddressState.StateId")

    .......
    .PivotFields("tblCustomer.CustCode ").Orientation = xlColumnField
    .PivotFields("tblAddressState.StateName").Orientat ion = xlRowField
    .PivotFields("tblCustomer.CompRegNo").Orientation = xlDataField
    ........

    Can you helping debugging above VBA Marco. Thank you.


    If you can provide any web site relate Excel Pivot Table with VBA and SQL there is very thanl you so much.

    Best Regards,
    Hoo

  2. #2
    Join Date
    Oct 2003
    Posts
    1,091
    1. Connect SQL string problem:
    I have writed a connection string statement for SQL server as show in below, this connection string is working but EXCEL always prompt a dialog box required me re-
    enter password and user_id. (Note: the Database User_ID and Password are same that is 'Research') Could you help me debug below connection string. Thank you.
    ConnectString = "ODBC;" & "DBQ=" & ThisWorkbook.Path & "\crmdbv310_RnD.MDF;" & "Driver={sql server};
    SERVER=IMSBTPMDEV ;UID=Research ;PWD=Research ;DATABASE=crmdbv310_RnD"
    Not sure, but I think the password has to be enclosed in "

    ConnectString = "ODBC;" & "DBQ=" & ThisWorkbook.Path & "\crmdbv310_RnD.MDF;" & "Driver={sql server};
    SERVER=IMSBTPMDEV ;UID="Research" ;PWD="Research" ;DATABASE=crmdbv310_RnD"

    I think a few people on this board who have experience in VBA and SQL

    www.ozgrid.com
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  3. #3
    Join Date
    Oct 2003
    Posts
    1,091
    Here is probably the best site. Dennis also is on the staff of OZGrid.

    http://www.xldennis.com/
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •