VBA入门到进阶常用知识代码总结73

最新推荐文章于 2026-08-01 14:20:08 发布

原创最新推荐文章于 2026-08-01 14:20:08 发布 · 508 阅读

1 ·

本内容遵循CC 4.0 BY-SA版权协议

收录于

VBA

本文介绍了如何使用VBA结合SQL在Excel中进行多表合并、汇总和对比操作。通过实例展示了Union All、Inner Join、Left Join等SQL语句的运用，帮助读者从入门到进阶掌握VBA进行高级查询的技术。

第73集 SQL高级查询技术
357、多表合并汇总
Union all，Union多表合并
类模块中：
Sub 执行筛选(sq, Rg As String)
Dim conn As New Connection
With ActiveSheet 'Sheets(“sheet1”)
.Range(Rg).Resize(100, 7) = “”
conn.Open “provider=Microsoft.ACE.OLEDB.12.0;extended properties=excel 8.0;data source=” & ThisWorkbook.FullName
.Range(Rg).CopyFromRecordset conn.Execute(sq)
End With
conn.Close
Set conn = Nothing
End Sub
模块中：
Union AlL 多个select查询结果合并在一起，加ALL不进行去重，不加进行去重
Sub 合并工作表数据()
Dim data As New 类1
Dim sql As String
sql = “select * from [Sheet1 $a : c] u n i o n a l l s e l e c t * f r o m [s h e e t 2$ a:c]”
data.执行筛选 sql, “a2”
End Sub

Sub 合并工作表数据2()
Dim data As New 类1
Dim sql As String
sql = “select * from [Sheet1 $a : c] u n i o n s e l e c t * f r o m [s h e e t 2$ a:c]”
data.执行筛选 sql, “a2”
End Sub
查找两个表中相同的
Select 字段 from 表1,表2 where 表1.字段=表2.字段
Sub 列出相同()
Dim data As New 类1
Dim sql As String
sql = “select [Sheet1 $a : c] . * f r o m [S h e e t 1$ a:c],[Sheet2 $a : c] w h e r e [S h e e t 1$ a:c].类别=[Sheet2$a:c].类别”
data.执行筛选 sql, “a2”
End Sub

Select 字段 from 表1 Inner Join 表2 on 条件
Sub 列出相同2()
Dim data As New 类1
Dim sql As String
sql = “select [Sheet1 $] . *, [s h e e t 2$ ].库别 from [Sheet1 $] I n n e r J o i n [s h e e t 2$ ] on [Sheet1 $] . 类别 = [s h e e t 2$ ].类别”
data.执行筛选 sql, “a2”
End Sub

'两表汇总
Sub 汇总()
Dim data As New 类1
Dim sql As String
Dim sq As String
sql = “select * from [sheet1 $a : c] u n i o n a l l s e l e c t * f r o m [s h e e t 2$ a:c]”
sq = “select 类别,sum(数量),sum(金额) from (” & sql & “) group by 类别”
data.执行筛选 sq, “a2”
End Sub

’
Sub 合并()
Dim data As New 类1
Dim sql As String
sql = “select [Sheet1 $] . *, [s h e e t 2$ ].库别 from [Sheet1 $] l e f t J o i n [s h e e t 2$ ] on [Sheet1 $] . 类别 = [s h e e t 2$ ].类别”
data.执行筛选 sql, “a2”
End Sub