经典Excel_VBA_SQL语句

### 经典Excel_VBA_SQL语句：详细解析与应用实例 #### A、根据本工作簿的1个表查询求和写法范本 这部分内容提供了两种不同的VBA脚本，用于从当前工作簿的一个表格中提取数据并进行求和操作。 **1. 查询方法一** ```vba Sub 查询方法一() Set CONN = CreateObject("ADODB.Connection") CONN.Open "provider=microsoft.jet.oledb.4.0;extendedproperties=excel8.0;datasource=" & ThisWorkbook.FullName sql = "SELECT 区域, 存货类, SUM(代销仓入库数量), SUM(代销仓出库数量), SUM(日报数量) FROM [sheet4$A:I] WHERE 区域='" & [B3] & "' AND MONTH(日期)='" & Month(Range("F3")) & "' GROUP BY 区域, 存货类" Sheets("sheet2").Range("A5").CopyFromRecordset CONN.Execute(sql) CONN.Close Set CONN = Nothing End Sub ``` - **功能概述**： - 打开当前工作簿的数据库连接。 - 构建SQL语句，根据指定的区域名称和月份筛选数据，并对特定列求和。 - 将查询结果复制到`sheet2`中的A5单元格开始的位置。 - **关键点分析**： - `CONN.Open`：使用Microsoft Jet数据库引擎打开数据库连接，这里指定了Excel 8.0的数据源路径。 - SQL语句使用了`WHERE`子句来限定条件（如区域名称和月份），并且通过`GROUP BY`子句按`区域`和`存货类`进行分组汇总。 - `CopyFromRecordset`方法将查询结果直接写入Excel的工作表中。 **2. 查询方法二** 此方法与查询方法一类似，但使用不同的数据源连接字符串： ```vba Sub 查询方法二() Set CONN = CreateObject("ADODB.Connection") CONN.Open "dsn=excelfiles;dbq=" & ThisWorkbook.FullName sql = "SELECT 区域, 存货类, SUM(代销仓入库数量), SUM(代销仓出库数量), SUM(日报数量) FROM [sheet4$A:I] WHERE 区域='" & [B3] & "' AND MONTH(日期)='" & Month(Range("F3")) & "' GROUP BY 区域, 存货类" Sheets("sheet2").Range("A5").CopyFromRecordset CONN.Execute(sql) CONN.Close Set CONN = Nothing End Sub ``` - **功能概述**： - 与查询方法一类似，但是数据源使用了不同的连接字符串（`dsn=excelfiles;dbq=`）。 - **区别**： - 第一种方法使用的是OLE DB提供者连接方式，而第二种方法使用的是DSN名称来建立连接。这种方式可能需要预先在系统中设置数据源。 #### B、根据本工作簿2个表的不同类别查询求和写法范本 这部分展示了如何从同一工作簿中的两个不同表格中提取数据，并基于共同字段进行合并与求和。 **示例脚本**： ```vba Sub 根据入库表和回款表的区域名和月份分别求存货类发货数量和本月回款数量查询() Set conn = CreateObject("ADODB.Connection") conn.Open "provider=microsoft.jet.oledb.4.0;extendedproperties=excel8.0;datasource=" & ThisWorkbook.FullName Sheet3.Activate Sql = "SELECT A.存货类, A.FH, B.HK FROM (SELECT 存货类, SUM(本月发货数量) AS FH FROM [入库$] WHERE 存货类 IS NOT NULL AND 区域='" & [B2] & "' AND MONTH(日期)=" & [D2] & " GROUP BY 存货类) AS A LEFT JOIN (SELECT 存货类, SUM(数量) AS HK FROM [回款$] WHERE 存货类 IS NOT NULL AND 区域='" & [B2] & "' AND MONTH(开票日期)=" & [D2] & " GROUP BY 存货类) AS B ON A.存货类 = B.存货类" Range("A5").CopyFromRecordset conn.Execute(Sql) End Sub ``` - **功能概述**： - 分别从“入库”和“回款”表中提取数据，根据区域名称和月份筛选，并计算存货类别的发货数量与回款数量。 - 使用LEFT JOIN将两组数据合并。 - 最终结果输出到`Sheet3`中的A5单元格起始位置。 - **关键点分析**： - SQL语句中包含了两个子查询：一个用于从“入库”表中获取数据，另一个从“回款”表中获取数据。 - 子查询的结果通过LEFT JOIN合并在一起，确保所有存货类都能出现在最终结果中。 - 条件筛选包括了区域名称、月份等维度。 #### C、根据本文件夹下其他工作簿1个表区域的区域求和 这部分介绍了如何从当前文件夹下的另一个Excel文件中读取数据并进行求和操作。 **示例脚本**： ```vba Sub 在工作表1汇总本文件夹下001工作薄的表1分数列查询汇总() Set conn = CreateObject("ADODB.Connection") conn.Open "dsn=excelfiles;dbq=" & ThisWorkbook.Path & "\001.xls" sql = "SELECT SUM(分数) FROM [Sheet1$]" Sheets(1).Range("A2").CopyFromRecordset conn.Execute(sql) conn.Close Set conn = Nothing End Sub ``` - **功能概述**： - 从当前文件夹下的名为“001.xls”的文件中的“Sheet1”表中读取数据。 - 对“分数”列进行求和操作。 - 结果输出到当前工作簿的Sheet1中的A2单元格。 - **关键点分析**： - 使用`ThisWorkbook.Path`动态获取当前文件夹路径，确保脚本的灵活性。 - SQL语句简单明了，仅针对单个列进行求和操作。 - 数据输出位置为当前工作簿的Sheet1中的A2单元格。 以上三个部分涵盖了从Excel内部或外部文件中提取数据、进行复杂数据处理及汇总的多种场景，为用户提供了灵活且强大的数据处理工具。通过这些脚本的应用，可以大大提高数据分析和报表制作的效率。