excel - 如何将SQL查询结果插入到工作表单元格中

Question

我想直接将 sql 查询的结果写入我使用 ADO Excel VBA 的特定单元格中。

        SetConn     ' SET THE CONNECTION TO THE DATABASE.

        sQuery = "SELECT * FROM [referenceStandard$] WHERE [Line] = 'L1' AND [Variants] = 'Choco Wafer (L1)' AND [ID] = 'CW (L1)' "

        If rs.State = adStateOpen Then
            rs.Close
        End If

        rs.CursorLocation = adUseClient

        rs.Open sQuery, myConn, adOpenKeyset, adLockOptimistic
        If rs.RecordCount > 0 Then
            MsgBox "Result is > 1"
            Do While Not rs.EOF

            'Codes that will copy the result of query and paste in worksheet cells

            Loop
        End If

Answer 1

您可以使用CopyFromRecordset:

rs.Open sQuery, myConn, adOpenKeyset, adLockOptimistic
If Not rs.EOF Then
    ActiveSheet.Range("a1").CopyFromRecordset rs
End If

如果您想要比这更具体/更有选择性的内容，那么用更多细节更新您的问题可能会很有用。