Creating a PivotTable from an external database using VBA involves a few key steps. You’ll need to:
- Set up a connection to the database.
- Fetch the necessary data.
- Place the data into a worksheet.
- Generate a PivotTable from that data.
Here’s a basic example that connects to an SQL Server database using an OLE DB connection, fetches data into an Excel worksheet, and then creates a PivotTable from that data:
Step 1: Open Excel and Press `ALT + F11` to Open the VBA Editor
Step 2: Add the Required References
Before writing the code, ensure the required references are added:
- Go to `Tools` > `References`.
- Add:
- `Microsoft ActiveX Data Objects x.x Library` (x.x is the version number, e.g., 6.1)
- `Microsoft Excel xx.x Object Library`
Step 3: Write the VBA Code
Below is the VBA code that connects to a database, retrieves data, and creates a PivotTable:
Sub CreatePivotTableFromDatabase()
Dim conn As Object
Dim rs As Object
Dim connectionString As String
Dim sql As String
Dim ws As Worksheet
Dim pc As PivotCache
Dim pt As PivotTable
Dim dataSheet As Worksheet
Dim pivotSheet As Worksheet
Dim lastRow As Long
' Set the connection string
connectionString = "Provider=SQLOLEDB;Data Source=YourServerName;Initial Catalog=YourDatabaseName;User ID=YourUserID;Password=YourPassword;"
' Set the SQL query
sql = "SELECT * FROM YourTableName"
' Create a connection object
Set conn = CreateObject("ADODB.Connection")
conn.Open connectionString
' Create a recordset object
Set rs = CreateObject("ADODB.Recordset")
rs.Open sql, conn
' Create a new worksheet for data (optional)
On Error Resume Next
Set dataSheet = ThisWorkbook.Worksheets("DataSheet")
If dataSheet Is Nothing Then
Set dataSheet = ThisWorkbook.Worksheets.Add
dataSheet.Name = "DataSheet"
End If
On Error GoTo 0
' Clear previous data
dataSheet.Cells.Clear
' Copy the recordset to the worksheet
dataSheet.Range("A1").CopyFromRecordset rs
' Close the recordset and connection
rs.Close
conn.Close
Set rs = Nothing
Set conn = Nothing
' Determine last row of data
lastRow = dataSheet.Cells(dataSheet.Rows.Count, 1).End(xlUp).Row
' Create a new worksheet for the PivotTable (optional)
On Error Resume Next
Set pivotSheet = ThisWorkbook.Worksheets("PivotSheet")
If pivotSheet Is Nothing Then
Set pivotSheet = ThisWorkbook.Worksheets.Add
pivotSheet.Name = "PivotSheet"
End If
On Error GoTo 0
' Clear previous PivotTable
pivotSheet.Cells.Clear
' Create a PivotCache
Set pc = ThisWorkbook.PivotCaches.Create( _
SourceType:=xlDatabase, _
SourceData:=dataSheet.Range("A1").CurrentRegion)
' Create a PivotTable
Set pt = pc.CreatePivotTable( _
TableDestination:=pivotSheet.Range("A3"), _
TableName:="PivotTable1")
' Set up the PivotTable layout
With pt
' Example of setting a row field
.PivotFields("FieldName1").Orientation = xlRowField
' Example of setting a column field
.PivotFields("FieldName2").Orientation = xlColumnField
' Example of adding a data field
.PivotFields("NumericField").Orientation = xlDataField
.PivotFields("NumericField").Function = xlSum
End With
MsgBox "PivotTable created successfully!"
End Sub
Step 4: Customize the Code
- Replace `”YourServerName”`, `”YourDatabaseName”`, `”YourUserID”`, `”YourPassword”`, and `”YourTableName”` with the actual server name, database name, user credentials, and table name.
- Adjust the field names in the PivotTable layout (e.g., `”FieldName1″`, `”FieldName2″`, `”NumericField”`) to match the columns in your database.
- Modify the layout (`xlRowField`, `xlColumnField`, `xlDataField`, etc.) to suit your data and reporting needs.
This script is a basic template and assumes the database is accessible using OLE DB. Your database settings and provider may differ, so adjust the connection string accordingly. Also, ensure your database allows the connection using the provided credentials and that you handle appropriate security concerns like encrypting password and sensitive information.
Unlock Your Potential
Excel
Access
Power BI
