Excel Microsoft Query, query vertica issues

Question

I'm trying to query vertica db from excel, I can query simple SQL statements fine, but if I want to do more complex queries I run into trouble.

I have altered the macro I have but I am being confused on why they are separating the connection part and query part. When I paste my query into vba module I am just getting red text. If anyone has a easy to follow template when querying vertica it would be appreciated.

I get compiled error, expectation: expression on my query on my as parts of queries.

Thanks.

Sub stackoverflow()

    ActiveWorkbook.Worksheets.Add
     ActiveSheet.Name = "stackoverflow"
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array(Array( _
        "CONNECTION HERE" _
        ), Array( _
        "NEXT PART OF CONNECTION" _
        ), Array("NEXT PART OF CONNECTION")), Destination:= _
        Range("$A$1")).QueryTable
        ''.CommandType = 0
        .CommandText = Array( _
        "QUERY" _
        , _
        "QUERY CONTINUED" _
        )
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Table NAME"
        .Refresh BackgroundQuery:=False
    End With

End Sub

including the query part

        Range("$A$1")).QueryTable
         ''.CommandType = 0
        .CommandText = Array( _
        "SELECT stackoverflow.date AS date,
      stackoverflow.website AS site
 FROM XXXX.Stack.stackoverflow WHERE (site = 'stackoverflow.com')"
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .BackgroundQuery = True
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .PreserveColumnInfo = True
            .ListObject.DisplayName = "Table Name"
            .Refresh BackgroundQuery:=False
        End With