Creating an Excel Piviot Table with a SQL Parameter Query


Hello!

I am trying to create an excel pivot table which gets launched inside a VB application.  I think there is two roads I can go by:

1. I can build an SQL statement in VB with some date fields that the user can select, then pass that sql to the excel pivot table.  OR

2. I can build a parameter sql statement inside the Excel pivot table and pass the parameters from the VB app.  

Either way would work fine for me, but I can't get either of these options to work.  I need help, my deadline is approaching.  Anyone have any ideas.  If so can you SPELL them out, I clueless on this one.

Thanks!
JayQueAsked:
Who is Participating?
 
TimCotteeConnect With a Mentor Head of Software ServicesCommented:
Ok, here is a starter, this uses a SQL server table as the source.

    With ActiveWorkbook.PivotCaches.Add(SourceType:=xlExternal)
        .Connection = Array(Array( _
        "ODBC;DRIVER=SQL Server;SERVER=<MYSERVERNAME>;APP=<MYAPPLICATION>;WSID=<MYWSID>;DATABASE=<MYDATABASE>;QueryLogFile=Yes;QueryLogTime=Yes;StatsLo" _
        ), Array("g_On=2;Trusted_Connection=Yes"))
        .CommandType = xlCmdSql
        .CommandText = Array( _
        "SELECT EmployeeAbsences.EmployeeNumber, EmployeeAbsences.Year, EmployeeAbsences.Code" & Chr(13) & "" & Chr(10) & "FROM <MYDATABASE>" _
        , ".dbo.EmployeeAbsences EmployeeAbsences")
        .CreatePivotTable TableDestination:=Range("A3"), TableName:= _
        "MyPivotTable"
    End With
    ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:=Array( _
        "EmployeeNumber", "Year")
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Code").Orientation = _
        xlDataField

Replace the bits in <...> with something that fits your scenario. Then obviously amend the sql statement and the fields and you should have the beginnings of the pivot table.
0
 
TimCotteeHead of Software ServicesCommented:
1) sounds to me to be the easiest. Where is the source data coming from? Is it an excel sheet or some external data source such as SQL server or MS access?
0
 
JayQueAuthor Commented:

An sql statement would be built from VB, it would be hardcoded inside the app.
0
 
JayQueAuthor Commented:

Thanks For All Your Help.
0
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.