首页 > 其他分享 >VBA学习(48):利用ADO操作Excel工作表数据

VBA学习(48):利用ADO操作Excel工作表数据

时间:2024-08-19 13:25:50浏览次数:9  
标签:Dim VBA Set 48 rs Excel sql ADODB conn

VBA可以利用ADO创建SQL,从而实现在不打开Excel工作簿的情况下,对工作表的数据进行读写等操作。对于数据量比较大的Excel工作簿,利用ADO模型可以大大提高性能。

案例数据

D盘有ExcelSQL数据库.xlsx工作簿,工作簿内有【员工信息】工作表,其字段包含工号、姓名、部门、性别和年龄。

图片

使用ADO的两种方式

1、CreateObject函数创建

Dim conn As Object
Set conn = CreateObject("ADODB.Connection")

2、外部引用

VBE界面上选择:工具-引用-Microsoft ActiveX Data Object x.x Library

Dim conn As ADODB.Connection
Set conn = New ADODB.Connection

 连接数据源(Excel工作簿)

conn.Open "provider=microsoft.ace.oledb.12.0;extended properties='excel 12.0';data source='D:\ExcelSQL数据库.xlsx'"

 增加记录

Sub 增加记录()
    Dim conn As Object
    Set conn = CreateObject("ADODB.Connection")
    
    conn.Open "provider=microsoft.ace.oledb.12.0;extended properties='excel 12.0';data source='D:\ExcelSQL数据库.xlsx'"

    Dim sql As String
    sql = "insert into [员工信息$] values  ('999','VBAMatrix','开发部','男',19)"
    conn.Execute (sql)
    
    sql = "insert into [员工信息$](工号,姓名,部门,性别) values ('666','PythonMatrix','开发部','女')"
    conn.Execute (sql)
    
    sql = "insert into [员工信息$a1:e65536] values ('888','SQLMatrix','开发部','女',30)"
    conn.Execute (sql)

    conn.Close
    Set conn = Nothing
End Sub

 注:(1)SQL语句中的表可以是工作表[员工信息$],或工作表的部分区域[员工信息$a1:e65536] ;(2)目标字段数量和values值的数量要一致。

删除记录

不支持,会报错。

图片

更新记录

Sub 更新记录()
    Dim conn As Object
    Set conn = CreateObject("ADODB.Connection")
    
    conn.Open "provider=microsoft.ace.oledb.12.0;extended properties='excel 12.0';data source='D:\ExcelSQL数据库.xlsx'"
    Dim sql As String
    dep = InputBox("更新张三所在的部门")
    sql = "update [员工信息$] set 部门='" & dep & "' where 姓名='张三'"
    conn.Execute (sql)
    
    conn.Close
    Set conn = Nothing
End Sub

注:变量在sql语句中要保留单引号。

查询记录

Sub 查询男性员工并写入新的工作表()
    Dim conn As Object
    Set conn = CreateObject("ADODB.Connection")

    conn.Open "provider=microsoft.ace.oledb.12.0;extended properties='excel 12.0';data source='D:\ExcelSQL数据库.xlsx'"

    Dim sql As String
    sql = "select * from [员工信息$] where 性别='男'"
    
    '创建记录集对象
    Dim rs As ADODB.Recordset
    Set rs = conn.Execute(sql)
    
    '将记录集的数据写入工作表中,获取字段名
    Dim i As Integer
    For i = 0 To rs.Fields.Count - 1
        ThisWorkbook.Sheets("男性员工").Cells(1, i + 1) = rs.Fields(i).Name
    Next
    
    ThisWorkbook.Sheets("男性员工").Range("a2").CopyFromRecordset rs
    
    rs.Close
    Set rs = Nothing
    conn.Close
    Set conn = Nothing
End Sub

总结

1、使用ADO对象创建数据库连接;

2、打开数据库:conn.open方法;

3、编写sql语句;

4、执行sql语句:conn.Execute ;若为查询语句,则创建记录集对象用于存储查询结果;

5、关闭记录集对象、释放空间;

6、关闭数据库连接对象,释放空间。

 技术交流,软件开发,欢迎微信沟通:

标签:Dim,VBA,Set,48,rs,Excel,sql,ADODB,conn
From: https://blog.csdn.net/nianfen/article/details/141322867

相关文章

  • Excel使用
    01Excel入门跨列居中:对齐方式-水平对齐-选定“跨列居中”(Office365版本)自动调整栏宽单栏调整:双击间隔线。多栏同宽调整:选定栏数,拖动任一栏宽度调整。(调整列亦同)。日期输入输入使用斜线格式,方便excel函数等功能计算使用使用“右键单元格格式-数字-日期”更换日期格式显......
  • python-深层遍历文件夹通过Excel某一列匹配文件夹中的图片(png\jpg)+写入Excel+超链
    目录专栏导读库的介绍背景库的安装完整代码总结专栏导读......
  • [开题报告]FLASK框架长株潭旅游舆情系统e48wf(源码+论文)
    本系统(程序+源码+数据库+调试部署+开发环境)带论文文档1万字以上,文末可获取,系统界面在最后面。系统程序文件列表开题报告内容研究背景长株潭地区作为湖南省的经济与文化中心,拥有丰富的自然风光和深厚的人文底蕴,吸引了大量游客前来观光旅游。然而,随着旅游业的蓬勃发展,旅游舆......
  • P2048 [NOI2010] 超级钢琴
    题意在一个数组中选择\(k\)个长度为\([l,r]\)的序列,对每个序列求和,使每个序列的和的和最大。思路首先,我们可以将序列之和转化为前缀和,如果固定左端点\(l\),那么我们只需要在\([l+len_l,l+len_r]\)中寻找最大的右端点,减去\(sum[l-1]\)就是在长度为\([len_l,le......
  • Excel vba自定义函数公式智能提示
    效果如下第一步下载,智能提示显示xll文件Releases·Excel-DNA/IntelliSense(github.com)xll文件报病毒解决办法默认情况下,Excel正在阻止不受信任的XLL加载项-Microsoft支持 第二步,加载xll文件,xll文件放在excel插件目录:%appdata%\Microsoft\AddIns 第三步编写e......
  • HTML实现2048小游戏
    游戏概述 实现一个基本的2048游戏涉及综合运用HTML、CSS和JavaScript这三种关键技术。 HTML用于构建游戏的结构框架,包括游戏容器、网格布局以及可能的用户交互元素(如按钮或得分显示)。 CSS则负责美化游戏界面,通过样式表定义网格的样式、瓷砖的外观、动画效果以及整体布......
  • 【漫谈C语言和嵌入式004】深入理解RS232、RS422和RS485:嵌入式系统中的串行通信协议
            在嵌入式系统设计中,串行通信协议是设备间数据传输的重要方式。其中,RS232、RS422和RS485是三种常用的标准。这些协议不仅在工业控制、仪器仪表、网络通信等领域得到广泛应用,也在许多嵌入式系统项目中扮演着重要角色。在本文中,我们将深入探讨这三种串行通信标准......
  • FastReport Net 自动把excel数据文件转为打印模版
    给FastReportNet报表工具补充了一个功能。自动生成模版,然后再用Designer精细调整。很方便。privatevoidbutton5_Click(objectsender,EventArgse){pReport=newReport();//实例化一个Report报表//registeralldatatablesandrelationspReport.RegisterData(ds)......
  • 传奇:发布网www.SouFu6.cn,新开传奇世界来袭!48
           传奇:发布网www.SouFu6.cn,新开传奇世界来袭!78       私服SF有着许多独特的品质,使其与正版游戏区别开来。首先,私服SF通常会提供大量的游戏元素和功能,比如新增的职业、装备、地图等,让玩家能够体验到更丰富的游戏内容。其次,私服SF还通常会调整......
  • VBA语法基础
    注释是用单引号开头‘定义子程序Sub方法名字()...endSub弹出提示框MsgBox"xxxx"变量申明DimxxxAsInterger’可以一次申明多个变量Dimvar1AsString,var2AsInteger如果不指定数据类型,就是自动类型(Variant),会自动变长,会不会自动变小?Dimvar......