用VBA SQL统计筛选文件夹下多个文档的数据

hhaa999 · 发表于 2011-4-6 10:54

hhaa999 · 发表于 2011-4-6 12:29

太感谢了，学习

zhaogang1960 · 发表于 2011-4-6 11:39

每个工作表加上标题，否则会造成第一行数据丢失
请参考：
Sub Macro1()
Dim cnn As Object, rs As Object, SQL$, Mypath$, MyFile$, n%, f$
Set cnn = CreateObject("adodb.connection")
Mypath = ThisWorkbook.Path & "\DD-0\"
MyFile = Dir(Mypath & "*.xls")
Do While MyFile <> ""
      n = n + 1
      If n = 1 Then
         cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;Data Source=" & Mypath & MyFile
         Set rs = cnn.Execute("[" & Split(MyFile, ".")(0) & "$]")
         f = rs.Fields(1).Name
         SQL = "select * from [" & Split(MyFile, ".")(0) & "$] where " & f & ">=100"
      Else
         SQL = SQL & " union all select * from [Excel 8.0;Database=" & Mypath & MyFile & "].[" & Split(MyFile, ".")(0) & "$] where  " & f & ">=100"
      End If
      MyFile = Dir()
Loop
[a2].CurrentRegion.ClearContents
[a2].CopyFromRecordset cnn.Execute(SQL)
cnn.Close
Set cnn = Nothing
End Sub

zhaogang1960 · 发表于 2011-4-6 11:40

数据汇总.rar (11.25 KB, 下载次数: 113)

onthetrip · 发表于 2011-4-6 12:31

向老师学习了.这种用法还是第一次看到,直接execute表名:cnn.Execute("[" & Split(MyFile, ".")(0) & "$]"
它返回的是什么呢?等价于"select * from 表名"?

hhaa999 · 发表于 2011-4-6 12:40

第二行B列的值大于等于100，为何汇总不出来，请zhaogang1960老师看看

zhaogang1960 · 发表于 2011-4-6 12:42

原帖由 onthetrip 于 2011-4-6 12:31 发表
向老师学习了.这种用法还是第一次看到,直接execute表名:cnn.Execute("[" & Split(MyFile, ".")(0) & "$]"
它返回的是什么呢?等价于"select * from 表名"?

应该是吧，这种用法来自于创建(access)数据表记录集:
rs.Open 数据表名,cnn
或
Set rs=cnn.Execute(数据表名)
但好像对于非连接数据源不能使用，还得用"select * from ……"

zhaogang1960 · 发表于 2011-4-6 12:43

原帖由 hhaa999 于 2011-4-6 12:40 发表
第二行B列的值大于等于100，为何汇总不出来，请zhaogang1960老师看看

2楼已经注明：
“每个工作表加上标题，否则会造成第一行数据丢失”

hhaa999 · 发表于 2011-4-6 12:56

工作表名一改变，就出现错误，是不是以工作表名的第一个字符为选择，如果改变了又如何呢，请老师解惑

zhaogang1960 · 发表于 2011-4-6 15:55

原帖由 hhaa999 于 2011-4-6 12:56 发表
工作表名一改变，就出现错误，是不是以工作表名的第一个字符为选择，如果改变了又如何呢，请老师解惑

这个附件不同于上面的，工作表名没有规律了，上面的工作表名就是工作簿名
还有就是表头(或字段)不能是空的
Sub Macro1() '引用Microsoft AD0 Ext 2.8 for DDL and Security
Dim cnn As Object, rs As Object, SQL$, Mypath$, MyFile$, n%, f$, s$
Dim cat  As New ADOX.Catalog, tb1 As Table
Set cnn = CreateObject("adodb.connection")
Mypath = ThisWorkbook.Path & "\DD-0\"
MyFile = Dir(Mypath & "*.xls")
Do While MyFile <> ""
      n = n + 1
      cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties='Excel 8.0;HDR=No';Data Source=" & Mypath & MyFile
      For Each tb1 In cat.Tables
         If tb1.Type = "TABLE" Then
            s = Replace(tb1.Name, "'", "")
            If Right(s, 1) = "$" Then
                  If n = 1 Then
                     cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;Data Source=" & Mypath & MyFile
                     Set rs = cnn.Execute("[" & s & "]")
                     f = rs.Fields(1).Name
                     SQL = "select * from [" & s & "] where " & f & ">=100"
                  Else
                     SQL = SQL & " union all select * from [Excel 8.0;Database=" & Mypath & MyFile & "].[" & s & "] where  " & f & ">=100"
                  End If
            End If
         End If
      Next
      MyFile = Dir()
Loop
[a2].CurrentRegion.ClearContents
[a2].CopyFromRecordset cnn.Execute(SQL)
rs.Close
cnn.Close
Set rs = Nothing
Set cnn = Nothing
End Sub

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

[求助] 用VBA SQL统计筛选文件夹下多个文档的数据

回复 3楼 zhaogang1960 的帖子

评分