• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 193
  • Last Modified:

Creating an Excel Piviot Table with a SQL Parameter Query


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.

  • 2
  • 2
1 Solution
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?
JayQueAuthor Commented:

An sql statement would be built from VB, it would be hardcoded inside the app.
Ok, here is a starter, this uses a SQL server table as the source.

    With ActiveWorkbook.PivotCaches.Add(SourceType:=xlExternal)
        .Connection = Array(Array( _
        ), 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:= _
    End With
    ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:=Array( _
        "EmployeeNumber", "Year")
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Code").Orientation = _

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.
JayQueAuthor Commented:

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

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now