VBA从SQL记录集复制整列 [英] VBA Copy Whole Column From SQL Recordset

问题描述

我有一个固定的数据集，其中有6行要在SQL中查询.我想将此查询中的数据传输到Excel中.我的问题分为两个部分:

1-我可以一次性撤回多列吗?还是必须逐列进行?我有17列和6行.当这被转移到我的Excel模板中时，我希望将其分解为三分之二，以便Excel中的第1列到第7列在另一个部分的第8列到第11列，而在另一部分的第12列到第17列.

2-使用我当前的代码:

Range("F2").Value = rst.Fields("ACCOUNT") 

我只退回一行，是否有可能获得整列(全部6行)，还是我必须循环所有6行才能获得整列?

预先感谢

解决方案

使用 Option Explicit Sub Test() Dim sConnection As String Dim sQuery As String Dim oConnection As Object Dim oRecordset As Object Dim aData() sConnection = _ "Provider=Microsoft.ACE.OLEDB.12.0;" & _ "User ID=Admin;" & _ "Data Source='" & ThisWorkbook.FullName & "';" & _ "Mode=Read;" & _ "Extended Properties=""Excel 12.0 Macro;"";" sQuery = _ "SELECT * FROM [Sheet1$] " & _ "IN '" & ThisWorkbook.Path & "\Src1.xlsx' " & _ "[Excel 12.0;Provider=Microsoft.ACE.OLEDB.12.0;Mode=Read;Extended Properties='HDR=YES;'] " & _ "WHERE Country='UK';" Set oConnection = CreateObject("ADODB.Connection") oConnection.Open sConnection Set oRecordset = oConnection.Execute(sQuery) oRecordset.MoveFirst aData = oRecordset.GetRows(, , Array("CustomerID", "ContactName")) With ThisWorkbook.Sheets(1) .Cells.Delete Output2DArray .Cells(1, 1), WorksheetFunction.Transpose(aData) .Cells.EntireColumn.AutoFit End With oConnection.Close End Sub Sub Output2DArray(oDstRng As Range, aCells As Variant) With oDstRng .Parent.Select With .Resize( _ UBound(aCells, 1) - LBound(aCells, 1) + 1, _ UBound(aCells, 2) - LBound(aCells, 2) + 1) .NumberFormat = "@" .Value = aCells End With End With End Sub

还有Src1.xlsx工作簿，其中包含客户作为数据源与此工作簿在同一文件夹中:

结果工作表如下，您只能看到CustomerID和ContactName字段:

I have a fixed data set of 6 rows being queried in SQL. I want to transfer the data in this query into Excel. My question is in two parts:

1 - Can I pull back multiple columns in one go or do I have to do it column by column? I have 17 columns and 6 rows. As this is transferred into my Excel template I'm looking to break this up into thirds so columns 1 to 7 go in one space 8 to 11 in another section and 12 to 17 in another section in Excel.

2 - When using my current code of:

Range("F2").Value = rst.Fields("ACCOUNT") 

I only pull back a single row, is it possible to get the whole column (all 6 rows) or do I have to loop all 6 rows to get the full column?

Thanks in advance

解决方案

Use .GetRows() method, it allows to set a number of records to retrieve, first record to begin, and single field name or ordinal position, or an array of field names or ordinal position numbers.

The below example shows how to get data from external Excel workbook into recordset, retrieve specified fields into array, and paste result array to the worksheet.

Option Explicit

Sub Test()

    Dim sConnection As String
    Dim sQuery As String
    Dim oConnection As Object
    Dim oRecordset As Object
    Dim aData()

    sConnection = _
        "Provider=Microsoft.ACE.OLEDB.12.0;" & _
        "User ID=Admin;" & _
        "Data Source='" & ThisWorkbook.FullName & "';" & _
        "Mode=Read;" & _
        "Extended Properties=""Excel 12.0 Macro;"";"

    sQuery = _
        "SELECT * FROM [Sheet1$] " & _
        "IN '" & ThisWorkbook.Path & "\Src1.xlsx' " & _
        "[Excel 12.0;Provider=Microsoft.ACE.OLEDB.12.0;Mode=Read;Extended Properties='HDR=YES;'] " & _
        "WHERE Country='UK';"

    Set oConnection = CreateObject("ADODB.Connection")
    oConnection.Open sConnection
    Set oRecordset = oConnection.Execute(sQuery)
    oRecordset.MoveFirst
    aData = oRecordset.GetRows(, , Array("CustomerID", "ContactName"))
    With ThisWorkbook.Sheets(1)
        .Cells.Delete
        Output2DArray .Cells(1, 1), WorksheetFunction.Transpose(aData)
        .Cells.EntireColumn.AutoFit
    End With
    oConnection.Close

End Sub

Sub Output2DArray(oDstRng As Range, aCells As Variant)

    With oDstRng
        .Parent.Select
        With .Resize( _
                UBound(aCells, 1) - LBound(aCells, 1) + 1, _
                UBound(aCells, 2) - LBound(aCells, 2) + 1)
            .NumberFormat = "@"
            .Value = aCells
        End With
    End With

End Sub

Also there is Src1.xlsx workbook containing Customers as data source in the same folder as this workbook:

The resulting worksheet is as follows, you can see there are CustomerID and ContactName fields only:

这篇关于VBA从SQL记录集复制整列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！