I have the following code below.
The PivotTable is created just fine, but it does not have the “data is added to the data model” option selected. This means that I cannot use the “distinct count” option in the values field.
How do I achieve this?
Sub test()
testpath = "C:UserDatatestfoldertestfile.xlsx"
With Workbooks.Open(testpath)
Dim ws As Worksheet
Set ws = .Worksheets(2)
ws.Activate
Dim newSheet As Worksheet
Set newSheet = .Worksheets.Add(Before:=ws)
newSheet.Name = "PivotTableSheet"
Dim pivotCache As pivotCache
Set pivotCache = .PivotCaches.Create(SourceType:=xlDatabase, SourceData:=ws.UsedRange, Version:=xlPivotTableVersion15)
Dim pivotTable As pivotTable
Set pivotTable = pivotCache.CreatePivotTable(TableDestination:=newSheet.Cells(1, 1), TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion15)
End With
End Sub
You need to add the range to the connections.
Option Explicit
Sub Test()
Dim Wb As Workbook
Dim Ws As Worksheet
Dim NewSheet As Worksheet
Dim PivotCache As PivotCache
Dim PivotTable As PivotTable
Dim Conn As WorkbookConnection
Dim Rng As Range
Dim ConnName As String
Dim TestPath As String
TestPath = "C:UserDatatestfoldertestfile.xlsx"
Rem TestPath = "D:vbasample dataSampleData.xlsx"
Rem Open the workbook
Set Wb = Workbooks.Open(TestPath)
Set Ws = Wb.Worksheets(2)
Rem Define the data range, assuming the data has headers
Set Rng = Ws.UsedRange
Rem Create a new sheet for the PivotTable
Set NewSheet = Wb.Worksheets.Add(Before:=Ws)
NewSheet.Name = "PivotTableSheet"
Rem Add the UsedRange to the Data Model by creating a WorkbookConnection
ConnName = "DataModelConnection"
On Error Resume Next ' Ignore if connection already exists
Set Conn = Wb.Connections(ConnName)
On Error GoTo 0
If Conn Is Nothing Then
Set Conn = Wb.Connections.Add2(Name:=ConnName, _
Description:="Connection to worksheet data", _
ConnectionString:="WORKSHEET;" & Wb.FullName, _
CommandText:=Rng.Address(External:=True), _
lCmdtype:=xlCmdExcel)
End If
Rem Now create a PivotCache from the Data Model (external connection)
Set PivotCache = Wb.PivotCaches.Create(SourceType:=xlExternal, SourceData:=Conn, Version:=xlPivotTableVersion15)
Rem Create the PivotTable from the data model connection
Set PivotTable = PivotCache.CreatePivotTable( _
TableDestination:=NewSheet.Cells(1, 1), _
TableName:="PivotTable1", _
DefaultVersion:=xlPivotTableVersion15)
Rem Cleanup
Set Wb = Nothing
Set Ws = Nothing
Set Rng = Nothing
Set NewSheet = Nothing
Set PivotCache = Nothing
Set PivotTable = Nothing
End Sub
Alternatively you could manually add the range.
-
Select the data range
- Highlight the range of data you want to add, including the headers.
-
Go to the Data tab
- Click on the Data tab in the Excel ribbon.
-
Click on “From Table/Range”
- In the “Get & Transform Data” group, click From Table/Range.
-
Confirm the range
- In the Create Table dialog box, confirm that your range is correct and check
the box for “My table has headers” if applicable.
- In the Create Table dialog box, confirm that your range is correct and check
-
Load to Data Model
- The Power Query Editor will open. Without making changes, click Close & Load
from the top-left corner, then select Close & Load To…. - In the Import Data dialog, choose “Only Create Connection” and check the box labeled
“Add this data to the Data Model.”
- The Power Query Editor will open. Without making changes, click Close & Load
-
Click OK
- The range is now added to the Data Model for use in PivotTables or Power Pivot.
2