VBA：Excel 中利用 ADO 查询数据

'功能-更 B1 中Sales Month 查询该月 数据
Sub QureyForMonth()
    Dim cnn As New ADODB.Connection, rs As New ADODB.Recordset
    Dim cnnStr As String, sql As String
    Dim curMonth As Date, firstDay As Date, lastDay As Date
    Dim i As Integer, nRow As Integer, nCol As Integer
    
'    On Error GoTo hErr
    With ThisWorkbook.Worksheets(1)      '--sheet1 表
        .Activate
        '--cnn
        cnnStr = "driver={SQL Server};server=XXXXX;uid=sa;pwd=****;database=XXXX"
        cnn.CommandTimeout = 60
        cnn.CursorLocation = adUseClient
        cnn.Open cnnStr

        curMonth = CDate(.Range("B1").Value)                               '--当前月份
        firstDay = CDate(Year(curMonth) & "-" & Month(curMonth) & "-1")                                     '--第一天
        lastDay = DateAdd("m", 1, firstDay)                                                                 '--下月第一天
        lastDay = CDate(Year(curMonth) & "-" & Month(curMonth) & "-" & DateDiff("d", firstDay, lastDay))    '--最后一天
         
         .Range(Cells(3, 1), Cells(.UsedRange.Rows.Count, .UsedRange.Columns.Count)).Clear           '--清除数据
        
        '--rs
        sql = "SELECT A.InvoiceNo, A.InvDate, A.InvSeqNo, A.VNumber, A.PickDate, A.CustPO AS [Cust P.O.], A.ItemID, A.Enduser, A.EMS," & _
              " A.OEM, A.CustItemID,A.Category, A.Qty AS [Inv.Qty], A.Currency, A.Price, B.Quantity, B.Warehouse,B.Location, " & _
              " B.CustID, B.CustPO, B.PurchPO, B.InvoiceNO AS [B.Inv No.],B.VMINo " & _
              " FROM VMISalesInvX A LEFT OUTER JOIN  VMIStockIOX B ON A.VID = B.VID " & _
              " WHERE (A.WareHouse IN ('H02', 'H03')) AND (A.InvDate BETWEEN '" & firstDay & "' AND '" & lastDay & "') AND (A.InvoiceNo <> '') " & _
              " ORDER BY A.InvDate, A.InvoiceNo, A.InvSeqNo, B.ID"
        rs.Open sql, cnn, adOpenKeyset, adLockReadOnly, adCmdText
        nRow = rs.RecordCount + 2
        nCol = rs.Fields.Count
        .Range("A3").CopyFromRecordset rs   '--导入数据库数据
        
        '--设置格式，交叉背景色
        If nRow >= 3 Then
            For i = 3 To nRow
                If i Mod 2 = 1 Then
                    .Range(Cells(i, 1), Cells(i, nCol)).Interior.Color = RGB(210, 230, 250)
                Else
                    .Range(Cells(i, 1), Cells(i, nCol)).Interior.Color = RGB(230, 210, 250)
                End If
            Next
        End If
    End With
    '--查询结束
hErr:
    If Not rs.State = adStateClosed Then rs.Close
    If Not cnn.State = adStateClosed Then cnn.Close
    Set rs = Nothing
    Set cnn = Nothing
    
End Sub