Sub Test()
    Dim wb As String, strSQL As String
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim arrData As Variant
    Dim rng As Range

    wb = ThisWorkbook.FullName
    strSQL = "SELECT * FROM [Sheet1$]"
    
    Set cn = New ADODB.Connection
    cn.Open "DRIVER={Microsoft Excel Driver (*.xls)};" & _
        "DriverId=790;ReadOnly=True;DBQ=" & wb & ";"

    Set rs = New ADODB.Recordset
    rs.Open strSQL, cn, adOpenForwardOnly, _
        adLockReadOnly, adCmdText

    arrData = rs.GetRows
    
    With Sheets("Sheet2")
        Set rng = .Range(.Cells( 1 ,  1 ), .Cells(UBound(arrData,  2 ),  1 ))
    End With
    
    With Application
        rng.Offset(,  0 ) = .Transpose(.Index(arrData,  2 ))
        rng.Offset(,  1 ) = .Transpose(.Index(arrData,  4 ))
        rng.Offset(,  2 ) = .Transpose(.Index(arrData,  7 ))
    End With
    
    rs.Close
    Set rs = Nothing
    cn.Close
    Set cn = Nothing
End Sub

Sub Test()
    Dim wb As String, strSQL As String
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim arrData As Variant

    wb = ThisWorkbook.FullName
    strSQL = "SELECT * FROM [Sheet1$]"
    
    Set cn = New ADODB.Connection
    cn.Open "DRIVER={Microsoft Excel Driver (*.xls)};" & _
        "DriverId=790;ReadOnly=True;DBQ=" & wb & ";"

    Set rs = New ADODB.Recordset
    rs.Open strSQL, cn, adOpenForwardOnly, _
        adLockReadOnly, adCmdText

    arrData = rs.GetRows(, , Array( 0 ,  2 ,  4 ,  7 ))
     
    With Sheets("Sheet2")
        .Range(.Cells( 1 ,  1 ), .Cells(UBound(arrData,  2 ) +  1 , UBound(arrData,  1 ) +  1 )) = _
            Application.Transpose(arrData)
    End With
    
    rs.Close
    Set rs = Nothing
    cn.Close
    Set cn = Nothing
End Sub