将 copyfromrecordset 写入范围 [英] writing copyfromrecordset to range

问题描述

我有以下 vba，它从单元格 C10 开始读入 MCO，直到它为空，并将从 SQL 数据库中获取机器数量、解密数量和升级机器.

I've got the following vba, it reads in the MCO from cell C10 onwards until its empty and will grab the number of machines, number of decrypts and upgrading machines from a SQL database.

这工作正常，但我无法获取相应行中的数据.目前它总是将数据写入 D10，因为我已经对其进行了硬编码，但我不确定如何使其递增或写入已读取 MCO 的同一行.如果记录集为空，我也会遇到同样的问题.我想在 3 列中插入 0 0 0

That works fine but I'm having trouble getting the data in the corresponding row. At the moment it always write the data to D10 coz I've hard coded it but I'm not sure how to get this to increment or write to the same row that the MCO has been read in from. I'm having the same problem if the record set is empty. I'd like to insert 0 0 0 in to the 3 columns

任何帮助将不胜感激

Sub Summary_Click()

Dim MyConnObj As New ADODB.Connection 'ADODB Connection Object
Dim myRecSet As New ADODB.Recordset 'Recordset Object
Dim sqlStr As String ' String variable to store sql command

Range("D9:F34").Select
Range("D9:F34").Clear

Range("C10").Select

Set myRecSet = New ADODB.Recordset

Do Until IsEmpty(ActiveCell)
    strMCO = ActiveCell.Value

    MyConnObj.Open _
        "Provider = sqloledb;" & _
        "Data Source=xxx;" & _
        "Initial Catalog=xxx;" & _
        "User ID=xxx;" & _
        "Password=xxx;"

    strqa = " SELECT Count (distinct DeviceData.machinename) As [Number Of Devices], sum(case buildstatus when 'Decrypted' then 1 else 0 end) Decrypted, sum(case buildstatus when 'Upgrading' then 1 else 0 end) Upgrading, SiteList.Region "
    strqb = " FROM dbo.DeviceData JOIN dbo.SiteList ON dbo.DeviceData.CurrentSite = dbo.SiteList.SiteCode"
    strqc = " where MCO = '" & strMCO & "' "
    strqd = " group by DeviceData.Country, SiteList.Region"

    sqlStr = strqa & strqb & strqc & strqd

    myRecSet.Open sqlStr, MyConnObj, adOpenKeyset

    ActiveCell.Offset(0, 1).Select

    ActiveSheet.Range("D10").CopyFromRecordset myRecSet
    'ActiveSheet.Range("D<10 + 1>).CopyFromRecordset myRecSet

    If myRecSet.RecordCount = 0 Then
        ActiveSheet.Range("D10, E10, F10") = "0"
    End If

    ActiveCell.Offset(1, -1).Select
    MyConnObj.Close
Loop

End Sub

推荐答案

将 Excel 工作表包含为联接表可能是最简单的.例如:

It might be easiest to include the Excel sheet as a joined table. For example:

Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset

''Not the best way to get the name
strFile = ActiveWorkbook.FullName

''2007 / 2010 connection
strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strFile _
    & ";Extended Properties=""Excel 12.0 xml;HDR=Yes;"";"

cn.Open strCon

''ODBC Connection for sql server
scn = "[ODBC;DRIVER=SQL Server;SERVER\Instance;" _ 
    & "Trusted_Connection=Yes;DATABASE=Test]"

sSQL = "SELECT a.Stuff, b.ID, b.AText FROM [Sheet5$] a " _
& "INNER JOIN " & scn & ".table_1 b " _             
& "ON a.Stuff = b.AText"
rs.Open sSQL, cn

ActiveWorkbook.Sheets("Sheet7").Cells(1, 1).CopyFromRecordset rs

对于任何指向 SQL Server 的链接，您需要相当确信您正在处理干净的数据.

With any links to SQL Server, you need to be fairly confident that you are working with clean data.

请注意，我提到了 Cells.如果不喜欢连接sheet的思路，也可以参考cells和step，例如For i=1 To MaxRows

Note that I have referred to Cells. If you do not like the idea of connecting the sheet, you can also refer to cells and step, for example For i=1 To MaxRows

这篇关于将 copyfromrecordset 写入范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！