如何使用SQL查询Excel跨表数据? (VBA版)

发表于： 2021年8月3日 2021年8月3日
分类：未分类

大家好. 老衲又回来了.

大家在处理Excel表格的时候, 想必都遇到过一种情况, 例如身份证号在一个表, 而姓名则在另外一个表里. 通常的解决方案是使用Vlookup将另外一个表中的数据列匹配回来. 很不幸的是, vlookup函数性能出名的低下, 如果两边都是几万行数据, 那运行的时间都够喝咖啡了.

老衲在这里展示一个更快捷的方式, 也就是使用SQL, 而且是使用VBA来操作SQL, 这样更高效, 且显得非常有*格.

废话不说, 直接上图.

我们先创建一个文件, 需要使其能支持VBA.

然后打开开发工具.

勾选成功之后Ribbon上就会出现开发工具的字样.

然后老衲准备一点点样例数据:

这是两个表哈, 里面只有年份这一项是能对上的.

然后来看看我们期待的输出结果是什么. 年份在两个表里都有, GDP一项则是在第一个表里, 政府消费占比则在第二个表里.

好了, 现在我们就可以添加控件了. 选择插入即可. 这次老衲使用的控件都是ActiveX控件. 一个是TextBox, 用来支持按年份查询的功能. 另外一个是按钮, 点击后再启动查询. (老衲顺道做了一点外观美化的工作)

对, 添加完控件可以给他们重新起名字, 或者改变其标题文字.

最重要的就是编程啦. 双击那个按钮, 就会自动开启编程界面, 而且自动生成一个事件. 我们的代码就放到这个事件中间去.

代码取出来放大一下, 老衲自认注释还算清楚哈.

Private Sub btnStartQuery_Click()

'Basic vars declaim

    Dim conn As Object, rs As Object
    Set conn = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.RecordSet")
    Dim ConnStr As String
    Dim sqlStr As String
    Dim FullPath As String
    
    FullPath = Application.ActiveWorkbook.FullName
    
'connection command
    ConnStr = "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0; Data Source=" & FullPath
    
'Input Check
    If TextBoxQuery.Text = "" Then
        MsgBox ("请重新输入")
        Exit Sub
    End If
    
'open the connection and execute the SQL query
    sqlStr = "SELECT `RAWData1$`.`年份`, `RAWData1$`.`国内生产总值(亿元)` , `RAWData2$`.`政府消费支出占最终消费支出比例(%)`  FROM `RAWData1$`  inner join `RAWData2$`on `RAWData1$`.`年份` = `RAWData2$`.`统计年度`  WHERE (`RAWData1$`.`年份` = " & Trim(TextBoxQuery.Text) & ")"
    conn.Open ConnStr
    Set rs = conn.Execute(sqlStr)
    
'Output
    Sheets("Output").Range("A6").CopyFromRecordset rs
    
'GC
    Set rs = Nothing
    conn.Close
    Set conn = Nothing


End Sub

写完保存, 然后回去随便输入一个年份, 然后点击一下按钮.

是不是很简单? 是不是很有意思?

模板放到云盘上供大家参考吧.

链接: http://pan.baidu.com/s/1eSop52E 密码: phgp

各位晚安, 下期再贱~

admin

2126