4

I'm using VBA to programmatically create excel workbooks with embedded pivottable objects. My current project is utilizing the Data Model as pivot cache source.

I need to be able to drill into details beyond the first 1,000 records (default max records returned.) Excel provides capability to reset this value, up to a maximum of 1,048,576 records. However I don't see a way to address this via code (I consider this a requirement for my project - these are not necessarily savvy end-users.)

Excel exposes the OLEDBConnection.MaxDrillthroughRecords property to presumably control this behavior. However, I cannot set this property because connections to the Data Model don't seem to allow me to access OLEDBconnection objects.

I tried Excel's macro recorder, to capture the change that occurs when I set it via the UI. It generates this code:

Sub Macro1()
'
' Macro1 Macro
'

'
10: With ActiveWorkbook.Connections("ThisWorkbookDataModel").OLEDBConnection
        .CommandText = Array("Model")
        .CommandType = xlCmdCube
        .Connection = "OLEDB;"
        .RefreshOnFileOpen = False
        .SavePassword = False
        .SourceConnectionFile = ""
        .MaxDrillthroughRecords = 1048576
        .ServerCredentialsMethod = xlCredentialsMethodIntegrated
        .AlwaysUseConnectionFile = False
        .RetrieveInOfficeUILang = True
    End With
    With ActiveWorkbook.Connections("ThisWorkbookDataModel")
        .Name = "ThisWorkbookDataModel"
        .Description = "Data Model"
    End With
End Sub

Unfortunately, executing the recorded macro, in the same workbook from whence it recorded, generates a run-time error on the very first With section (Label '10'): '1004': Application-defined or object-defined error.

Perusing the connection object through the locals window shows this, which explains the error message: OLEDBConnection isn't available

How can I programmatically set the maxDrillthroughRecords property in an existing sheet. Or, alternatively, how can I specify the value of this property either in the application defaults, or at the time the connection is created?

Thanks

Chris Meurer
  • 459
  • 3
  • 13
  • This is probably too simplistic of a suggestion and a long shot, but have you tried explicitly referencing the workbook instead of using `ActiveWorkbook`? – Stavros Jon Apr 13 '19 at 10:20
  • Yes, I create the workbook with `Workbooks.Add` and assign it to an Excel.workbook object. In the real code I don't use activeworkbook at all.. thanks for looking – Chris Meurer Apr 13 '19 at 12:31
  • Do you really need `WorkbookConnection`? I'd prefer to use `ADODB.Connection`... – Maciej Los Apr 15 '19 at 12:57
  • As is stated here: [Microsoft Excel Pivot Table Limitations](http://www.constellationhb.com/Builder360Web/B360_Common/OnlineHelp/Builder360/03_Pivot_Tables/III._F._Microsoft_Excel_Pivot_Table_Limitations.htm), maximum records in pivot table depends on MS Excel version. Excel 2010 returns 1,048,576 records, but the number of calculated items depends on memory limitation. So, your problem might be related to that. – Maciej Los Apr 16 '19 at 09:24
  • Not encountering a limit, I simply wish to manage the olap max drill through records feature via code – Chris Meurer Apr 16 '19 at 15:01

0 Answers0