Export Powerpivot to CSV - macro error "Method 'Open' of object '_Recordset' failed"

Hi

I am seeking to apply the VBA code (shown below) from an article (link below) in Powerpivot for Excel 2013 (64 bit) and am getting the error:

"macro returns error "Method 'Open' of object '_Recordset' failed"

http://www.powerpivotblog.nl/export-...csv-using-vba/ (1 person got this working, 1 did not)

As a beginner in VBA, I must be failing in some obvious way, e.g. not applying the correct references or something? Big thanks in advance for any help!


VBA_error.jpg
-----------------------------------------------------------------------------
Option Explicit
Public Sub ExportToCsv()

Dim wbTarget As Workbook
Dim ws As Worksheet
Dim rs As Object
Dim sQuery As String

'Suppress alerts and screen updates
With Application
.ScreenUpdating = False
.DisplayAlerts = False
End With

'Bind to active workbook
Set wbTarget = ActiveWorkbook

Err.Clear

On Error GoTo ErrHandler

'Make sure the model is loaded
wbTarget.Model.Initialize

'Send query to the model
sQuery = "EVALUATE <MOVERS_1of6_MoveHistory>"
Set rs = CreateObject("ADODB.Recordset")
rs.Open sQuery, wbTarget.Model.DataModelConnection.ModelConnection.ADOConnection
Dim CSVData As String
CSVData = RecordsetToCSV(rs, True)

'Write to file
Open "C:\temp\MOVERS_1of6_MoveHistory.csv" For Binary Access Write As #1
Put #1, , CSVData
Close #1

rs.Close
Set rs = Nothing

ExitPoint:
With Application
.ScreenUpdating = True
.DisplayAlerts = True
End With
Set rs = Nothing
Exit Sub

ErrHandler:
MsgBox "An error occured - " & Err.Description, vbOKOnly
Resume ExitPoint
End Sub



Public Function RecordsetToCSV(rsData As ADODB.Recordset, _
Optional ShowColumnNames As Boolean = True, _
Optional NULLStr As String = "") As String
'Function returns a string to be saved as .CSV file
'Option: save column titles

Dim K As Long, RetStr As String

If ShowColumnNames Then
For K = 0 To rsData.Fields.Count - 1
RetStr = RetStr & ",""" & rsData.Fields(K).Name & """"
Next K

RetStr = Mid(RetStr, 2) & vbNewLine
End If

RetStr = RetStr & """" & rsData.GetString(adClipString, -1, """,""", """" & vbNewLine & """", NULLStr)
RetStr = Left(RetStr, Len(RetStr) - 3)

RecordsetToCSV = RetStr
End Function