使用 ADO-SQL 处理 EXCEL 文件的程序架构（实例注释）

烈日 · 发表于 2006-10-19 22:10

不懂，但惊叹，爱歌老师太了不起了

耐心诚恳，向老师学习

[此贴子已经被作者于2006-10-19 22:10:54编辑过]

SWFWIND · 发表于 2006-10-20 08:45

原来可以这样写啊！~

爱歌老师好厉害啊！~崇拜ING！

爱歌学习 · 发表于 2006-10-21 22:41

站在前人肩膀上....

[此贴子已经被作者于2006-10-21 22:42:08编辑过]

紫贝 · 发表于 2006-10-26 11:37

向爱歌学习学习，谢谢 stx1980710老师的指引，我找到家了！

爱歌学习 · 发表于 2006-10-31 18:27

实例18：用 Union 从字段不相同的两个 Access 表中筛选记录后按相似字段排序：

http://club.excelhome.net/viewthread.php?tid=196804&replyID=574405&skin=0

    CNN.Open "provider=Microsoft.jet.OLEDB.4.0;data source=" & ThisWorkbook.Path & "\sales.mdb"
    SQL = "SELECT 年,月,日,发货单号,产品名称,规格,数量,单位,金额 as 销售金额,'' as 回款金额 FROM sale " & _
            " WHERE (((CDate([月] & '-' & [日] & '-' & [年])) Between #" & CDate(TextBox1.Text) & "# And #" & CDate(TextBox2.Text) & "#)) " & _
            "and 发货单号 not like '%★%' and 客户名称 like '%" & TextBox3.Text & "%' " & _
            "UNION ALL SELECT nian as 年,yue as 月,ri as 日,'' as 发货单号,con as 产品名称,'' as 规格,'' as 数量,'' as 单位,'' as 销售金额,je as 回款金额 FROM skd " & _
            "WHERE khmc like '%" & TextBox3.Text & "%' " & _
            "AND (((CDate([yue] & '-' & [ri] & '-' & [nian])) Between #" & CDate(TextBox1.Text) & "# And #" & CDate(TextBox2.Text) & "#)) ORDER BY 年,月,日,发货单号"
    RST.Open SQL, CNN, adOpenKeyset, adLockOptimistic
    Do Until RST.EOF
        With ListView1.ListItems.Add(, , RST.Fields("年"))
            .SubItems(1) = RST.Fields("月")
            .SubItems(2) = RST.Fields("日")
            .SubItems(3) = RST.Fields("发货单号")
            .SubItems(4) = IIf(RST.Fields("产品名称") = "收到货款", "收到货款", RST.Fields("产品名称") & RST.Fields("规格") & " " & RST.Fields("数量") & RST.Fields("单位"))
            .SubItems(5) = Format(RST.Fields("销售金额"), "#,##0.00")
            .SubItems(6) = Format(RST.Fields("回款金额"), "#,##0.00")
            xsje = GetxsjeSum(ListView1.ListItems.Count)
            hkje = GethkjeSum(ListView1.ListItems.Count)
           .SubItems(7) = Format(qcje + xsje - hkje, "#,##0.00")
            RST.MoveNext
        End With
    Loop
    RST.Close
    With ListView1.ListItems.Add
        .SubItems(4) = "             本月合计"
        .SubItems(5) = IIf(IsNull(xsje), "", Format(xsje, "#,##0.00"))
        .SubItems(6) = IIf(IsNull(hkje), "", Format(hkje, "#,##0.00"))
    End With
    Set CNN = Nothing

gZkSsJOO.rar (117.45 KB, 下载次数: 204)

UINON 的另一个例子及解说：

http://club.excelhome.net/dispbbs.asp?boardid=2&replyid=624979&id=205529&page=1&skin=0&Star=2 第 12 楼

[此贴子已经被作者于2006-12-18 18:04:41编辑过]

zhoujibin1 · 发表于 2006-10-31 23:53

谢谢。

爱歌学习 · 发表于 2006-11-1 19:13

实例19：用 Union 进行忽略某种数值的汇总

http://club.excelhome.net/viewthread.php?tid=197236&extra=&page=1#577405

Sub totalno0()
    Dim SQL$
    Set cnn = CreateObject("ADODB.Connection")
    cnn.Open "provider=microsoft.jet.oledb.4.0;extended properties='Excel 8.0;imex=1';data source=" & ThisWorkbook.FullName
    SQL = "select a.摘要,SUM(a.总收入笔数),SUM(a.总收入金额),SUM(a.总支出笔数),SUM(a.总支出金额) from (select 摘要,COUNT(收入金额) as 总收入笔数,SUM(收入金额) as 总收入金额,0 as 总支出笔数,0 as 总支出金额 from [sheet1$] " & _
        "where 收入金额<>0 group by 摘要 " & _
        "Union All select 摘要,0 as 总收入笔数,0 as 总收入金额,COUNT(支出金额) as 总支出笔数,SUM(支出金额) as 总支出金额 from [sheet1$] " & _
        "where 支出金额<>0 group by 摘要) as a group by 摘要 order by 摘要"
    Sheets("汇总").Activate
    [3:100].ClearContents
    [a3].CopyFromRecordset cnn.Execute(SQL)
    SQL = "select 摘要,COUNT(收入金额) as 总收入笔数,SUM(收入金额) as 总收入金额,COUNT(支出金额) as 总支出笔数,SUM(支出金额) as 总支出金额 " & _
        " from [sheet1$] group by 摘要 order by 摘要"
    [g3].CopyFromRecordset cnn.Execute(SQL)
    cnn.Close: Set cnn = Nothing
End Sub

zLC0f5Ue.rar (9.03 KB, 下载次数: 168)

aiker8 · 发表于 2006-11-2 22:07

爱歌姐姐，实在是棒极了。。。。。

受益无穷。。。。

haisizzm · 发表于 2006-11-4 22:49

谢谢各位热心的朋友给我的帮助。

爱歌学习 · 发表于 2006-11-7 16:51

实例20：用ADO进行联动选择填写单据－ADO、数组与 LISTBOX

1. 单击工作表中的填充区域，弹出选择框

2. 选择完毕，单击“规格”框，即在单击行填入数据

Dim cnn, temp, arr, Sql$

Sub UserForm_Initialize()          '数组法赋值记录集给列表框
    Set cnn = CreateObject("adodb.connection")
    cnn.Open "provider=microsoft.jet.oledb.4.0;extended properties=Excel 8.0;data source=" & ThisWorkbook.FullName
    Sql = "select distinct 大类 from [库存$] order by 大类"
    Set temp = cnn.Execute(Sql)
    arr = temp.GetRows                                '将记录存入数组
    ListBox大类.List = Application.Index(arr, 1, 0)   '取出数组第一行，并赋予列表框。若数组中包括 NULL ，则此句不能通过。
    ListBox名称.Clear
    ListBox规格.Clear
End Sub

Sub ListBox名称_Click()         '循环法赋值记录集给列表框
    Application.ScreenUpdating = False
    ListBox规格.Clear
    Sql = "select distinct 规格,单位 from [库存$] where 大类='" & ListBox大类 & "' and 名称='" & ListBox名称 & "' order by 规格"
    Set temp = cnn.Execute(Sql)
    Cells(ActiveCell.Row, "E") = temp.fields("单位")
    Do While Not temp.EOF         '因记录中包含 NULL ，无法直接使用数组赋值给列表框，故用此循环法赋值
        ListBox规格.AddItem IIf(IsNull(temp.fields("规格")), "", temp.fields("规格"))
        temp.MoveNext
    Loop
End Sub

PXI8fZpt.rar (101.43 KB, 下载次数: 202)

http://club.excelhome.net/viewthread.php?tid=198445&extra=&page=1#584655

联动选择的动态数据有效性解法：

http://club.excelhome.net/viewthread.php?tid=160679&replyID=&skin=0

[此贴子已经被作者于2007-2-28 19:28:50编辑过]

		自动登录	找回密码
密码			免费注册