VBA中使用SQL

 取出同文件夹下所有表格中相关信息

Dim Cnn As Object, MyCat As Object, rst As Object, sql$, SheetName$, f$, ph$, r&, strConn$
ph = ThisWorkbook.Path & "\": f = Dir(ph & "*.xls?") '注意写法，创建路径
strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=excel 12.0;Data Source="
Application.ScreenUpdating = False

[A:C].ClearContents: [a1:c1] = [{"文件名","类别","员数"}]

Set MyCat = CreateObject("ADOX.Catalog")
Set Cnn = CreateObject("ADODB.Connection")
Cnn.Open strConn & ThisWorkbook.FullName
Do While f <> "" '循环所有文件
    If (f <> ThisWorkbook.Name) * (f <> "0030.xlsx") Then '如果不是本文件，进行提取
        Set rst = CreateObject("ADODB.recordset")
        MyCat.ActiveConnection = strConn & ph & f
        SheetName = Replace(MyCat.Tables(0).Name, "'", "")
        sql = "select f1,f7 from [Excel 12.0;Hdr=no;Database=" & ph & f & "].[" & SheetName & "i3:o30] where f1 is not null" '写SQL
        rst.Open sql, Cnn, 3, 1: r = Range("a" & Rows.Count).End(3).Row + 1  '执行SQL
        Range("a" & r).Resize(rst.RecordCount) = "'" & Left(f, InStrRev(f, ".") - 1) '
        Range("b" & r).CopyFromRecordset rst '取出记录
    End If
    f = Dir
Loop

Application.ScreenUpdating = True

SQL语句示例

Sql=select a.物料编号,sum(iif(isnull(b.库存),0,b.库存)) as 本周库存,sum(iif(isnull(c.库存),0,c.库存)) as 上周库存,sum(iif(isnull(b.库存),0,b.库存))-sum(iif(isnull(c.库存),0,c.库存)) as 差异 from ((select distinct 物料编号 from (select 物料编号 from [本周$] union all select 物料编号 from [上周$])) a left join [本周$] b on a.物料编号=b.物料编号) left join [上周$] c on a.物料编号=c.物料编号 group by a.物料编号