How to copy a powerpivot table down to an excel sheet with vba?

Question

I need to get my table up in the powerpivot model down to the excel worksheet.

So far I have tried to use a Recordset but I cant get an ActiveConnection to the powerpivot table. Is it possible? Or is there an other better way to do this?

I use the following code:

Dim name As ADODB.Recordset
Set name = New ADODB.Recordset

With name
       .ActiveConnection = ConnectionName
       .Source = "TableName"
       .LockType = adLockReadOnly
       .CursorType = adOpenForwardOnly
       .Open
End With

But with this piece of code I get an error at .ActiveConnection. (Run-time error 3001, it complains about non-allowed connection interval)

Try setting .ActiveConnection = "Microsoft.ACE.OLEDB.12.0" (if you are on Win 7 onwards) otherwise "Microsoft.Jet.OLEDB.4.0". — Steven
Do I need to add some references then? because now it complains about no standard drivers or that it could not find the data source. — Erik Dahlen
Sorry Eric, I'll reply with an answer as the code is quite long. — Steven

Steven Steven · Accepted Answer · 2015-04-20T13:52:08

This is an example of how to read the records from a named range (assuming 'TableData' is named range).

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset

With cn
    .Provider = "Microsoft.ACE.OLEDB.12.0"
    .ConnectionString = "Data Source=" & ThisWorkbook.FullName & ";" & _
        "Extended Properties=Excel 8.0;"
    .Open
End With

rs.Open "SELECT * FROM [TableName]", cn

Dim r

For Each r In rs.GetRows

    'Do whatever you want per record
    Debug.Print r

Next r

rs.Close
cn.Close

How to copy a powerpivot table down to an excel sheet with vba?

1 Answers