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

ynztwxqy

辛苦辛苦，楼主好文，在此谢过。

爱歌学习

实例 15：格式相同的多文件多表去除空行后顺序汇入一张表中
http://club.excelhome.net/viewthread.php?tid=183140&extra=&page=1#490466 第5楼

Sub CommandButton1_Click()
    Dim sName$, Sql$, strTbl$, filecount%, sheetcount%
    Dim Filename As Variant
   
    指定数据区域.Hide
    Filename = Application.GetOpenFilename("Microsoft Office Excel Files (*.xls), *.xls", , "请选取文件", , MultiSelect:=True)
    If Not IsArray(Filename) Then Exit Sub
   
    [a7:iv7000].ClearContents
    filecount = 0: sheetcount = 0
    For Each fn In Filename
        Application.ScreenUpdating = False        '不显示处理过程
        sName = Dir(fn)                            '从文件的全路径中取出文件名
        Workbooks.Open fn                          '打开文件以进行表遍历
        Set cn = CreateObject("ADODB.Connection")               '（1）
        cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties='Excel 8.0;IMEX=1;HDR=NO';Data Source=" & fn
        For Each sh In ActiveWorkbook.Worksheets
            strTbl = sh.Name                         '当前表的名称
            rowend = ThisWorkbook.Sheets("subdetail").[a65536].End(xlUp).Row + 1
            Sql = "Select * FROM [" & strTbl & "$" & TextBox1.Text & TextBox2.Text & ":" & TextBox3.Text & TextBox4.Text & "] where f1 is not null  "
            ThisWorkbook.Sheets("subdetail").Range(TextBox1.Text & rowend).CopyFromRecordset cn.Execute(Sql)
            sheetcount = sheetcount + 1
        Next
        cn.Close
        Workbooks(sName).Close False
        filecount = filecount + 1
    Next
    [M3] = filecount: [M4] = sheetcount
    Set cn = Nothing
    Application.ScreenUpdating = True
End Sub

爱歌学习

实例 16 ：替代 VLOOUP、SUMPRODUCT 的VBA编码－多重 Join 的使用
http://club.excelhome.net/viewthread.php?tid=183367&px=0

Sub findd()
    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
    For i = 2 To [IV1].End(xlToLeft).Column
        sql1 = sql1 & "数量" & i & ","
        sql2 = sql2 & "("
        sql3 = sql3 & " left join (select 时间,数量 as 数量" & i & " from [sheet3$] where id=" & Sheet2.[a:a].Find(Cells(1, i), LookIn:=xlValues, LookAt:=xlWhole).Offset(0, 1) & ") as b" & i & " on a.时间=b" & i & ".时间)"
    Next
    sql1 = Left(sql1, Len(sql1) - 1)
    SQL = "select " & sql1 & " from (" & sql2 & "select 时间 from [sheet1$]) as a " & sql3
    'MsgBox SQL
    [B2].CopyFromRecordset CNN.Execute(SQL)
    CNN.Close
    Set CNN = Nothing
End Sub

循环后形成的查询字符串 SQL 为：
select 数量2,数量3,数量4,数量5 from (((((select 时间 from [sheet1$]) as a  left join (select 时间,数量 as 数量2 from [sheet3$] where id=712) as b2 on a.时间=b2.时间) left join (select 时间,数量 as 数量3 from [sheet3$] where id=713) as b3 on a.时间=b3.时间) left join (select 时间,数量 as 数量4 from [sheet3$] where id=714) as b4 on a.时间=b4.时间) left join (select 时间,数量 as 数量5 from [sheet3$] where id=715) as b5 on a.时间=b5.时间)

用 left join 对12个月同时汇总：

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

http://club.excelhome.net/dispbbs.asp?boardid=2&replyid=169351&id=128977&page=1&skin=0&Star=4

left join 的另三个例子及解释：

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

http://club.excelhome.net/dispbbs.asp?boardid=2&replyid=633116&id=205934&page=1&skin=0&Star=3

http://club.excelhome.net/viewthread.php?tid=218216&replyID=705633&skin=1

left join 的括号配对：

http://club.excelhome.net/viewthread.php?tid=211186&extra=&page=2#661249

从语法上来看，LEFT JOIN 只允许一层，也即：

SELECT 字段 FROM 基准表 LEFT JOIN 后续表 ON 基准表.同型字段=后续表.同型字段

而不能在后面直接继续其它的 LEFT JOIN ：

SELECT 字段 FROM 基准表 LEFT JOIN 后续表 ON 基准表.同型字段=后续表.同型字段 LEFT JOIN ................

但如果我们加上括号，就可以把第一层 LEFT JOIN 变成“新基准表”，而在此“新基准表”后面就可以连接 LEFT JOIN 了：

SELECT 字段 FROM (基准表 LEFT JOIN 后续表 ON 基准表.同型字段=后续表.同型字段) LEFT JOIN 后续表2 ON 基准表.同型字段=后续表2.同型字段
                                                                                      ↑
                                                                                 新基准表

所以使用括号，就可以连接多个 LEFT JOIN ，只要保证括号配对，从整体上看只有一个基准表、一个 LEFT JOIN、一个后续表、一个ON即可。

[此贴子已经被作者于2007-2-1 22:26:51编辑过]

zhujp888

先做个标记。

爱歌学习

实例17：列出库中欠缺编号-NOT IN 的应用
http://club.excelhome.net/viewthread.php?tid=183743&extra=&page=1#494934

Sub READ()
    Dim Sql$, endrow%, t
    t = Timer
    Set conn = CreateObject("ADODB.Connection")
    conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & ThisWorkbook.Path & "\编号不完整的库.mdb;"
   
    '产生连续数字
    [c2].FormulaR1C1 = "1"
    [d2].CopyFromRecordset conn.Execute("select cint(max(编号)) from 物价") '得到库中最大编号
    endrow = [d2].Value + 1
    [c2].DataSeries Rowcol:=xlColumns, Type:=xlLinear, Step:=1, Stop:=endrow - 1 '对应于菜单“编辑”－填充－序列
   
    '转为文本型的“应有编号”
    Range("b2:b" & endrow).FormulaR1C1 = "=TEXT(RC[1],""00000"")"
    Range("b2:b" & endrow).Copy
    Range("b2:b" & endrow).PasteSpecial Paste:=xlPasteValues '去掉公式
   
    '读出欠缺编号
    Sql = "select 应有编号 as 欠缺编号 from [Excel 8.0;DATABASE=" & ThisWorkbook.FullName & ";].[sheet1$] as a where a.应有编号  NOT IN  (select 编号 from 物价)"
    Range("a2:a" & [a65536].End(xlUp).Row).ClearContents
    [a2].CopyFromRecordset conn.Execute(Sql)
   
    '读出实际编号
    Sql = "select b.编号 from (select 应有编号 from [Excel 8.0;DATABASE=" & ThisWorkbook.FullName & ";].[sheet1$]) as a left join (select 编号 from 物价) as b on a.应有编号=b.编号"
    Range("c2:d" & [c65536].End(xlUp).Row).ClearContents
    [c2].CopyFromRecordset conn.Execute(Sql)
    conn.Close: Set conn = Nothing
    MsgBox Timer - t
End Sub

能否直接使用SQL语句得到“应有编号”呢？

lodgeren

非常厉害,我非常的佩服。我从事数字分析工作，平常也经常利用VBA编程，今天终于在这里解决了Excel利用ADO处理数据的问题。

原来我是准备调用Access来处理的。现在不用了。谢谢《爱歌学习》！

Robinhuang

QUOTE:

以下是引用lodgeren在2006-8-21 0:13:28的发言：

非常厉害,我非常的佩服。我从事数字分析工作，平常也经常利用VBA编程，今天终于在这里解决了Excel利用ADO处理数据的问题。

原来我是准备调用Access来处理的。现在不用了。谢谢《爱歌学习》！

数据比较大的话,还是用access比较好啊.

在分析的时候再写到excel上啊.

Robinhuang

QUOTE:

以下是引用tongtong96在2006-8-4 11:13:24的发言：
我想知道在VBa中通过ADO调用SQL语言，如何实现对Excell工作表行或者列的引用，比如，如何引用列A、列B，或1行，2行，甚至对对某一个单元格的引用，我的意思是指在SQL那一条语句中引用工作表的行列或单元格

从数据库的角度来说,行列是没有意义的.只有字段和记录.当然字段对应列,记录对应行...

但是,字段和记录的顺序在数据库里是无关紧要的,用个排序,就可以按你想要的顺序来.

oriole

爱歌老师：６４楼改为ＡＤＯ，其中查找代码：

将找到的记录写入临时表temp中，再连接临时表，下面代码怎么写不进去呢？（打开临时表看不到任何记录写入）

Private Sub CommandButton8_Click()           

CommandButton11.Caption = "返回主界面"
Application.ScreenUpdating = False
   ListView1.ListItems.Clear
    Call mycnn
    cnn.Execute "delete from temp"
    Strsql = "Select * From 主表"
    rst.Open Strsql, cnn, 1, 3
    Strsql = "insert into temp select * from 主表　where "
    For i = 1 To 28
     If Controls("textbox" & i) <> "" Then
      Strsql = Strsql & rst.Fields(i).Name & " like '*" & Controls("textbox" & i).Text & "*' and "
     End If
    Next
   Strsql = Left(Strsql, Len(Strsql) - 4)
   strsqlfind = Strsql                     

   cnn.Execute strsqlfind                  ＇将查找到记录写入临时表   
   Set rst = Nothing
   Set cnn = Nothing
end sub

oriole

搞好了，原来是通配符的问题，DAO中用＊，而改用ADO则要换为％

Strsql = Strsql & rst.Fields(i).Name & " like '%" & Controls("textbox" & i).Text & "%' and "