VBA中使用SQL

最新推荐文章于 2025-07-12 14:38:55 发布

yuanfang1992

最新推荐文章于 2025-07-12 14:38:55 发布

阅读量1.5w

点赞数 4

CC 4.0 BY-SA版权

分类专栏： VBA in Excel

本文链接：https://blog.youkuaiyun.com/yuanfang1992/article/details/82115565

VBA in Excel 专栏收录该内容

7 篇文章

订阅专栏

博客提供了取出同文件夹下所有表格中相关信息的SQL语句示例，聚焦信息技术领域中数据库操作方面，可帮助使用者了解如何通过SQL语句从同文件夹表格里提取所需信息。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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

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.物料编号