How to create a PivotTable based on a SQL query using VBA?

Creating a PivotTable based on a SQL query using VBA involves several key steps: connecting to the database, running the query to extract data, and then creating the PivotTable in Excel. Below is a step-by-step guide with sample VBA code to accomplish this task.

Set up the References:

Ensure that you have set references for ADO and Excel Object Library in the VBA editor.

VBA Code:

The following code acts as a guide. You need to modify the connection string and SQL query according to your database and requirements.

   Sub CreatePivotTableFromSQL()
       Dim conn As ADODB.Connection
       Dim rs As ADODB.Recordset
       Dim connString As String
       Dim sqlQuery As String
       Dim excelWS As Worksheet
       Dim pivotWS As Worksheet
       Dim pivotCache As PivotCache
       Dim pivotTable As PivotTable
       Dim destCell As Range

       ' Connection string - modify based on your database
       connString = "Provider=SQLOLEDB;Data Source=YourServerName;Initial Catalog=YourDatabaseName;User ID=YourUserId;Password=YourPassword;"
       
       ' SQL query
       sqlQuery = "SELECT * FROM YourTableName"  ' Adjust accordingly

       ' Initialize objects
       Set conn = New ADODB.Connection
       Set rs = New ADODB.Recordset

       ' Open the connection
       conn.Open connString

       ' Execute SQL command
       rs.Open sqlQuery, conn

       ' Create a new worksheet for data
       Set excelWS = ThisWorkbook.Worksheets.Add
       excelWS.Name = "SQLData"

       ' Copy data to Excel
       excelWS.Range("A1").CopyFromRecordset rs

       ' Close the recordset and connection
       rs.Close
       conn.Close

       ' Create a new worksheet for PivotTable
       Set pivotWS = ThisWorkbook.Worksheets.Add
       pivotWS.Name = "PivotTableSheet"

       ' Define the range of the source data
       Dim sourceRange As Range
       Set sourceRange = excelWS.Range("A1", excelWS.Cells.SpecialCells(xlCellTypeLastCell))

       ' Create a PivotCache
       Set pivotCache = ThisWorkbook.PivotCaches.Create( _
           SourceType:=xlDatabase, _
           SourceData:=sourceRange)

       ' Define where to place the PivotTable
       Set destCell = pivotWS.Range("A1")

       ' Create the PivotTable
       Set pivotTable = pivotCache.CreatePivotTable( _
           TableDestination:=destCell, _
           TableName:="PivotTableFromSQL")

       ' Example of setting up the fields—modify based on your needs
       With pivotTable
           .PivotFields("Column1").Orientation = xlRowField
           .PivotFields("Column2").Orientation = xlColumnField
           .PivotFields("Column3").Orientation = xlDataField
       End With

       ' Clean up
       Set rs = Nothing
       Set conn = Nothing
       MsgBox "PivotTable created successfully!"

   End Sub

Adjust the Setup:

Replace placeholders in `connString` and `sqlQuery` with actual server/database details and desired query.
Adjust the fields in the PivotTable creation section to match your data.
Customize the worksheet names if needed.

Running the Macro:

Open the VBA editor (`Alt + F11`).
Insert a new module if necessary (`Insert -> Module`).
Copy and paste the code into the module.
Run the macro (`F5` or use the `Run` submenu).

This code will create a new worksheet to store data from the SQL query and another worksheet for the PivotTable. The example assumes basic column setups, but you should customize the field orientations and calculations based on your specific requirements.

facebooklinkedin