ADODB.Connection via VBA versus PowerPivot

[lovallee]

Hello,

Very often, my data is in one a several Excel tables and I use VBA as follow (except only):

...
Set MyCon = CreateObject("ADODB.Connection")
Set MyData = CreateObject("ADODB.Recordset")

MyConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                    "Data Source=" & MainSourceFile & ";" & _
                    "Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"

MyCon.Open MyConnectionString
MyData.Open MySQL, MyCon, adCmdText

Set objPivotCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlExternal)
Set objPivotCache.Recordset = MyData
...

...which allows me to manually write an SQL query to join Excel tables, add calculated fields and export the results in a PivotTable cache. As an experiement, I converted one of my projects to PowerPivot, replicating the manually written SQL query using the PowerPivot "wizard". Conclusion: Both ADODB.Recordset and PowerPivot methods take the exact same time to refresh (about 30 seconds).

My question:
What are the advantages of using PowerPivot versus ADODB.Recordset and a manual SQL query? Is it just that the PowerPivot interface is more user-friendly (i.e. works like a "wizard")? Thanks

 

[TimRodman]

You probably want to compare what you're doing in VBA to Power Query, not Power Pivot.

Power Query is a data extraction and ETL utility like SSIS whereas Power Pivot is a data modeling utility that allows you to relate multiple data sets to each other and write cube formulas against them.