Record macro in excel, when I try to run it I get an error

I am trying to change the SQL command to an odbc database connection

Sub Macro1()
'
' Macro1 Macro
'

'
    With ActiveWorkbook.Connections("YearBS").ODBCConnection
        .BackgroundQuery = False
        .CommandText = Array( _
        "sp_report BalanceSheetStandard show Label, Amount parameters DateFrom = {d'2009-01-01'}, DateTo = {d'2010-12-31'}, " _
        , "SummarizeColumnsBy = 'Month'")
        .CommandType = xlCmdSql
        .Connection = Array(Array( _
        "ODBC;DSN=QuickBooks Data;DFQ=I:\ACCOUNTING\Quickbooks\Red Tail Networks, Inc.QBW;SERVER=QODBC;OptimizerDBFolder=%UserProfile%\QODBC " _
        ), Array( _
        "Driver for QuickBooks\Optimizer;OptimizerAllowDirtyReads=N;SyncFromOtherTables=N" _
        ))
        .RefreshOnFileOpen = False
        .SavePassword = False
        .SourceConnectionFile = ""
        .ServerCredentialsMethod = xlCredentialsMethodIntegrated
        .AlwaysUseConnectionFile = False
    End With
    With ActiveWorkbook.Connections("YearBS")
        .Name = "YearBS"
        .Description = ""
    End With
    ActiveWorkbook.Connections("YearBS").Refresh
End Sub

I recorded that in excel and then when I try to run it I get an error of:

Application-defined or object-defined error

When I debug it highlights this line:

        .CommandText = Array( _
        "sp_report BalanceSheetStandard show Label, Amount parameters DateFrom = {d'2009-01-01'}, DateTo = {d'2010-12-31'}, " _
        , "SummarizeColumnsBy = 'Month'")

Any help would be appreciated thank you.