CODEWITHSUNDEEP
excelpowerpivotvba
Erik Dahlen
Erik Dahlen

Reputation: 72

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

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)

Upvotes: 0

Views: 260

Answers (1)

Steven
Steven

Reputation: 781

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

Upvotes: 1

Related Questions