数据源有合并单元格在SQL中的应用
 用SQL求出两个月都没有销售过的业务员记录（结果表返回所有字段）

http://s16/mw690/001f8HsBzy6IUbIbvFt8f&690

http://s8/mw690/001f8HsBzy6IUbJOt5Jb7&690

知识点:
1、HDR=Yes改为：HDR=NO (因为从第3行开始,第一至二行有合并单元格)
2、无标题字段将采用F1,F2,F3....表示字段
3、SQL语句就一句
如下：
SELECT F1 AS 区域, F2 AS 门店, F3 AS 业务员, F4 AS 1月数量, F5 AS 1月金额, F6 AS 2月数量, F7 AS 2月金额 FROM[数据源$A3:G] WHERE F5&F7 IS NULL
完整的VBA代码如下：
Sub Test4()
    Dim Conn As Object, Rst As Object
    Dim strConn As String, strSQL As String
    Dim i As Integer, PathStr As String
    Set Conn = CreateObject("ADODB.Connection")
    Set Rst = CreateObject("ADODB.Recordset")
    PathStr = ThisWorkbook.FullName   '设置工作簿的完整路径和名称
    Select Case Application.Version * 1    '设置连接字符串,根据版本创建连接
    Case Is <= 11
        strConn = "Provider=Microsoft.Jet.Oledb.4.0;Extended Properties=excel 8.0;Data source=" & PathStr
    Case Is >= 12
        strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & PathStr & ";Extended Properties=""Excel 12.0;HDR=NO"";"""
    End Select
    Conn.Open strConn    '打开数据库链接
   
    '设置SQL查询语句
    strSQL = "SELECT F1 AS 区域, F2 AS 门店, F3 AS 业务员, F4 AS 1月数量, F5 AS 1月金额, " & _
             "F6 AS 2月数量, F7 AS 2月金额 FROM[数据源$A3:G] WHERE F5&F7 IS NULL"
            
    Set Rst = Conn.Execute(strSQL)    '执行查询，并将结果输出到记录集对象
    With Sheet3
        .Cells.Clear
        For i = 0 To Rst.Fields.Count - 1    '填写标题
            .Cells(1, i + 1) = Rst.Fields(i).Name
        Next i
        .Range("A2").CopyFromRecordset Rst
        .Cells.EntireColumn.AutoFit  '自动调整列宽
    End With
    Rst.Close    '关闭数据库连接
    Conn.Close
    Set Conn = Nothing
    Set Rst = Nothing
End Sub

新浪博客:http://weibo.com/u/1139851561
百度空间:http://hi.baidu.com/335081548
往期精彩在:
腾讯(QQ)微博:http://t.qq.com/huangshifang?preview
更多分享请关注微信号
微信号：Excel335081548 或:
雪山飞狐Excel
喜欢本文，请分享本文。
或扫扫二维码

http://s3/mw690/001f8HsBzy6IUbLslfId2&690