ado

Function sql_to_sht(sql, sht, Optional ByVal rng = "a1")

    sht.Range(rng).CurrentRegion.ClearContents

    For Each piv In sht.PivotTables
        piv.PivotSelect "", xlDataAndLabel, True
        Selection.ClearContents
    Next
'
'    sht.Cells.Clear
    Set conn1 = sql_ado(sql)
    cols = table_col(sql)
    width_num = conn1.Fields.Count
    
    With sht.Range(rng)
    '    表头
        .Resize(1, width_num) = cols
        .Resize(1, width_num).Interior.Color = 12566463 '单元格颜色
        
    '    内容
        .Offset(1, 0).CopyFromRecordset conn1
        
'        边框
        .CurrentRegion.Borders.LineStyle = xlContinuous
        
        .CurrentRegion.HorizontalAlignment = xlCenter '横向居中
        .CurrentRegion.VerticalAlignment = xlCenter '纵向居中
        
    
    End With
    conn1.Close
    
    Set conn1 = Nothing
    
    For Each xx In ThisWorkbook.Connections
        xx.Delete
    Next
    
End Function


Private Function table_col(sql)
    Dim arr_col
    Set result = sql_ado(sql)
    abc = result.Fields().Count
    ReDim arr_col(abc - 1)
    For y = 0 To abc - 1
        arr_col(y) = result.Fields(y).Name
    Next
    table_col = arr_col
End Function

Private Function sql_ado(sql)
    Dim conn As Object
    Dim conn_result As Object
    Dim arr_conn, arr2, arr3
    
    Set conn = CreateObject("ADODB.Connection")
    Set conn_result = CreateObject("adodb.recordset")
    
    conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.FullName & ";Extended Properties=""excel 12.0;HDR=YES"""
    Set sql_ado = conn.Execute(sql)

    Set conn = Nothing
    Set conn_result = Nothing
    
    For Each xx In ThisWorkbook.Connections
        xx.Delete
    Next
End Function


Function sql_to_arr(sql, Optional ByVal flag As String = "-_")

    Dim arr2, arre
    Set conn1 = sql_ado(sql)
    
    If flag = "-_" Then
        arr_conn = conn1.getrows()
    Else
        arr2 = Array()
        arr3 = Split(flag, "_")
        ReDim arr2(UBound(arr3))
        For z = LBound(arr2) To UBound(arr2)
            arr2(z) = arr3(z)
        Next
        
        arr_conn = conn1.getrows(, , arr2)
    End If
    
    conn1.movefirst
    sql_to_arr = TransposeArray(arr_conn)
    
    Set conn1 = Nothing
    Set arr_conn = Nothing
    
    
End Function

Private Function TransposeArray(arrA)
    Dim aRes()
    If IsArray(arrA) Then
        ReDim aRes(LBound(arrA, 2) To UBound(arrA, 2), LBound(arrA, 1) To UBound(arrA, 1))
        For i = LBound(arrA, 1) To UBound(arrA, 1)
            For j = LBound(arrA, 2) To UBound(arrA, 2)
                aRes(j, i) = arrA(i, j)
            Next
        Next
    Else
    
    End If
    
    TransposeArray = aRes
End Function