VBA-自定义面板,使用SQL查询Excel数据

这篇具有很好参考价值的文章主要介绍了VBA-自定义面板,使用SQL查询Excel数据。希望对大家有所帮助。如果存在错误或未考虑完全的地方,请大家不吝赐教,您也可以点击"举报违法"按钮提交疑问。

需求

定制插件,实现用户打开任意一个工作簿,写sql对Excel中的数据进行查询


案例sql需求场景:

需求

筛选日期小于’2023-4-24’,按group字段分组,求和各分组下的销售额,返回结果集新建工作表写入

数据源

现在有两个表,

一个用户的销售金额表,记录用户不同日期的销售金额,其中date字段是日期字段,数据在表格名为“Sheet1”的sheet页里
VBA-自定义面板,使用SQL查询Excel数据

一个是用户分组表,表的数据第一个格子不在a1单元格

VBA-自定义面板,使用SQL查询Excel数据

SQL语句

select t2.group,sum(t1.销售额) as sales from [Sheet1$] as t1 inner join [分组$c4:d7] as t2 on t1.姓名=t2.姓名 where format(date,'yyyy/m/dd')<'2023/4/24' group by t2.group

日期筛选,如果单元格的格式是日期,可在判断时先format格式成字符串再与字符串样式的日期做比较

sql中,或者在vba代码里,日期可以用两个#包围起来表示,筛选日期也可以这样:

select * from [Sheet1$] where date < #2023-4-23#(无需用单引号括起来)

sql中的数据表表示,如果数据左上角第一个格子是a1单元格,可以直接指定sheet名,比如:[Sheet1$]

如果不是,可以指定具体的数据范围,比如:[分组$c4:d7] 表格名后面跟一个$符号,后面紧跟单元格范围;

VBA代码

Sub sql_query()
' 使用sql对excel进行查询
Dim con, rs As Object
Dim query_sql, str As String
Dim i, cols As Long

Application.ScreenUpdating = True

' 创建对象
Set con = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

' 数据连接
con.Open "Provider=Microsoft.ace.Oledb.12.0;" _
    & "Extended Properties=Excel 12.0;" _
    & "Data Source=" & ActiveWorkbook.FullName
    
' sql 查询语句,如果单元格是日期,再判断时先format格式成字符串传入判断
query_sql = "select t2.group,sum(t1.销售额) as sales from [Sheet1$] as t1 inner join [分组$c4:d7] as t2 on t1.姓名=t2.姓名 where format(date,'yyyy/m/dd')<'2023/4/24' group by t2.group "
' query_sql = "select 姓名,date,销售额 from [Sheet1$] where format(date,'yyyy/m/dd')<'2023/4/24' "
' 执行sql语句
rs.Open query_sql, con, 1, 1


' 数据写入
Worksheets.Add    ' 新建工作表
With ActiveSheet
    cols = rs.Fields.Count
    For i = 0 To cols - 1
        .Cells(1, i + 1).Value = rs.Fields(i).Name  ' 写入表头
    Next
    .Cells(2, 1).CopyFromRecordset rs    ' 数据写入
End With

rs.Close
con.Close
Set con = Nothing

' 恢复屏幕刷新|工作表自动计算
Application.ScreenUpdating = True

End Sub

返回结果

一共两个组,筛选日期后,组1销售加总40,组2销售加总30
VBA-自定义面板,使用SQL查询Excel数据

使用案例代码,只针对当前工作簿生效,如果打开其他工作簿,代码要一行一行重写

下面制作简易插件,先把基本功能搭起来,造个mvp产品


插件制作

1. 新建xlam插件文件

新建工作簿,另存为xlam插件格式的文件,这里命名为UDL.xlam
VBA-自定义面板,使用SQL查询Excel数据

2. 编辑xml文件

编辑xml文件,具体请参考EXCEL自定义功能区制作:https://blog.csdn.net/me_to_007/article/details/118260245
如下,新增了功能组"SQL",功能组里边有一个命名为SQL_QUERY的按钮,按钮回调了函数query

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
<ribbon>
    <tabs>
        <tab id="myTab" label="my tab">
            <group id="group1" label="worksheet">
                <button id="button1" label="show name" size="large" onAction="show_activesheet_name" />
            </group>
            <group id="group2" label="SQL">
                <button id="button2" label="SQL_Query" size="large" onAction="query" />
            </group>
        </tab>
    </tabs>
</ribbon>
</customUI>

定义了回调函数:按钮点击会执行该函数

'Callback for button2 onAction
Sub query(control As IRibbonControl)
End Sub

这样就把功能面板做上去了,设置加载插件后,打开任意一个工作簿,我们可以看到功能该自定义按钮:
VBA-自定义面板,使用SQL查询Excel数据

3. 制作窗体,定义窗体控件函数

弹出文本框,让用户输入sql查询,这里制作了一个简易的样例:两个文本标签+一个文本框+3个按钮
VBA-自定义面板,使用SQL查询Excel数据

三按钮的default属性都设置为false,不然回车会触发按钮执行;
文本控件的ScrollBars属性设置为2:文本框内容过长,会有垂直滚条可以拉动

定义按钮函数-清空输入sql

Private Sub CommandButton2_Click()
UserForm1.TextBox1.Value = ""     ' 把文本框内容设置为空字符串即可
End Sub

定义按钮函数-生成样例sql

Private Sub CommandButton3_Click()
' 生成一个sql样例,供用户参考
UserForm1.TextBox1.Value = "select t2.group,sum(t1.销售额) as sales from [Sheet1$] as t1 inner join [分组$c4:d7] as t2 on t1.姓名=t2.姓名 where format(date,'yyyy/m/dd')<'2023/4/24' group by t2.group"
End Sub

定义按钮函数-执行sql
用户输入sql后,提交运行,这里我们只需要将上面的案例sql改下就好了,sql串使用用户文本框输入的内容,代码如下:

Private Sub CommandButton1_Click()
' 使用sql对excel进行查询
Dim con, rs As Object
Dim query_sql, str As String
Dim i, cols As Long

On Error GoTo line1

Application.ScreenUpdating = True   ' 关闭屏幕刷新

' 创建对象
Set con = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

' 数据连接
con.Open "Provider=Microsoft.ace.Oledb.12.0;" _
    & "Extended Properties=Excel 12.0;" _
    & "Data Source=" & ActiveWorkbook.FullName
    
' 传入用户输入的sql
query_sql = UserForm1.TextBox1.Value
rs.Open query_sql, con, 1, 1


' 数据写入
Worksheets.Add    ' 新建工作表
With ActiveSheet
    cols = rs.Fields.Count
    For i = 0 To cols - 1
        .Cells(1, i + 1).Value = rs.Fields(i).Name  ' 写入表头
    Next
    .Cells(2, 1).CopyFromRecordset rs    ' 数据写入
End With

rs.Close
con.Close

Set con = Nothing
Set rs = Nothing



' 恢复屏幕刷新|工作表自动计算
Application.ScreenUpdating = True

MsgBox "query done", vbInformation, "温馨提示"

line1:
If Err <> 0 Then
    UserForm1.TextBox1.Value = Err.Description
    MsgBox "请检查异常", vbQuestion, "Error"
End If

End Sub

4. 编辑回调函数

展示窗体即可,插入模块,在模块里编辑该函数

Sub query(control As IRibbonControl)
UserForm1.Show
UserForm1.TextBox1.MultiLine = True    ' 文本框多行显示
UserForm1.TextBox1.EnterKeyBehavior = False ' 文本框允许回车换行
End Sub

5. 效果展示

点击"SQL_Query"按钮弹出窗体,再点击"生成样例sql"按钮,生成了样例sql
VBA-自定义面板,使用SQL查询Excel数据

点击"执行sql"按钮,弹出了异常提示,这里我只打开了插件,没找到相关工作簿数据;

sql语句正常执行,则会新建工作表,将查询结果写入进去。

VBA-自定义面板,使用SQL查询Excel数据


插件下载及加载

下载插件:

微云链接:https://share.weiyun.com/eVg9FeWV 密码:fn8k43

加载插件

打开任意一个工作簿,加载路径如截图:

在加载项里浏览找到插件加载确定即可

VBA-自定义面板,使用SQL查询Excel数据文章来源地址https://www.toymoban.com/news/detail-428456.html

到了这里,关于VBA-自定义面板,使用SQL查询Excel数据的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

  • python连接sql server查询数据输出excel

    要将Python连接SQL Server查询的数据输出到Excel文件,你可以使用 pyodbc 库和 pandas 库。首先,你需要安装这些库,可以使用以下命令: 接下来,你可以使用以下代码将SQL Server查询的数据导出到Excel文件: 在这个示例中,你需要将 your_server_name 、 your_database_name 、 your_username 和

    2024年01月24日
    浏览(45)
  • 使用springboot实现查询更改数据库需求

    使用springboot实现简单的数据库数据交互请求 实现:通过springboot框架,实现接口 /user/view; 查询数据库user表中的user数据, /user/insert; 新增user数据到user表 /user/update 修改user的对应user信息 集成规范数据响应: 功能实现: 定义简单的user类并添加对应的user表,用于数据交互 创建数据访问

    2024年02月16日
    浏览(41)
  • Excel VSTO开发10 -自定义任务面板

    自定义任务面板(有些地方称为侧边面板)即CustomTaskPane,这个类在Microsoft.Office.Tools下面。以下将详细讲述如何实现自定义任务面板。 [代码6] CustomTaskPane的创建和使用 向项目添加一个“用户控件”, 图 19   选择“用户控件” 在开发界面根据需要向用户控件窗体上增加相应

    2024年02月09日
    浏览(43)
  • 技巧:给excel某列数据加双引号和逗号,用于sql中in()查询

    就是需求给过来一个excel,我们要用其中的某一列作为条件去查询数据库,删数据或者改数据。 这种小需求写代码去解析excel不值得,直接写sql搞定。 在首尾加上单引号或者双引号就可以使用in(…)来查询。 excel-给某列数据加双引号和逗号,excel公式里转义 Excel中对特殊字符的

    2024年02月14日
    浏览(90)
  • 如何将Excel数据导入到MS SQL Server已定义的数据表中

    1. 前提:已经在SQL Server中定义了所要导入数据的表(如stock,salvaging和out_stock三张表)的结构。 2. 如图,选择要导入数据的数据库(例如dlqx数据库),右键-任务-导入数据。  2. 进入向导后,点击“下一步”。  3. (1)选择 数据源 ,这里选择Microsoft Excel,如图所示 (2)点击

    2024年02月04日
    浏览(44)
  • VBA技术资料MF35:VBA_在Excel中过滤数据

    【分享成果,随喜正能量】好马好在腿,好人好在嘴。不会烧香得罪神,不会讲话得罪人。慢慢的你就会发现,一颗好心,永远比不上一张好嘴。。 我给VBA的定义:VBA是个人小型自动化处理的有效工具。利用好了,可以大大提高自己的工作效率,而且可以提高数据的准确度。

    2024年02月14日
    浏览(37)
  • 【数据库Microsoft SQL Server】实验一 数据库的定义与单表查询

    一、实验目的 1.要求学生熟悉掌握在DBMS中用SQL语言定义数据库、表、主码及外码。 2.熟悉关系数据库中常用数据类型,为关系表各属性定义合理的数据类型。 3.在建立好的数据库表中输入部分虚拟数据,实现基于单表的sql查询并分析查询结果。 二、实验环境与实验准备工作

    2024年02月05日
    浏览(62)
  • 使用chatgpt写VBA程序操作EXCEL

    在chatgpt输入,我有个EXCEL,它有4列,第一列是序号,第二列是机号,第三列是日期,第四列是能耗。同一机号会对应多个日期和多个该日期的能耗。我想让同一个机号的数据下新增加一行,在这一行内算出该机号不同日期下的能耗平均值,填入表格,请帮我写VBA函数执行这个

    2024年02月19日
    浏览(37)
  • Mysql-------SQL:DDL数据定义语言、DDM数据操作语言、DQL数据库查询语言、DQL数据控制语言

    SQL语言可以分为: DDL(Data Definition Language)语言:数据定义语言,用于 创建或更改数据库中的表、视图、索引等对象 DML(Data Manipulation Language)语言:数据操作语言,用来对 数据库表中的数据进行增删改查操作; DQL(Data Query Language)语言: 数据查询语言,用来查询数据库

    2024年02月13日
    浏览(75)
  • Excel·VBA单元格区域数据对比差异标记颜色

    之前的一篇博客《Excel·VBA单元格重复值标记颜色》,是对重复的整行标记颜色 而本文是按行对比2个单元格区域的数据,并对有差异的区域(一个单元格区域有的,而另一个单元格区域没有的)标记颜色,且只要存在任意1个字符不同的,则标记颜色 代码写为自定义函数使用

    2024年02月07日
    浏览(48)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包