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:\UserData\testfolder\testfile.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:\UserData\testfolder\testfile.xlsx"
Rem TestPath = "D:\vba\sample data\SampleData.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
Go to the Data tab
Click on "From Table/Range"
Confirm the range
Load to Data Model
Click OK