精华内容
下载资源
问答
  • 首先,必须严肃脸说明的是,对于普通 Excel 使用者而言,VBASQL 以及以 后提及的 ADO 并不是非学不可的,非学不可的...存仓库,进而储存、分析远超 Excel 体积的数据量,本书描述了ADOVBASQL in Excel平台的运用
  • VBA ADO+SQL实例集锦

    2019-02-26 09:44:59
    VBAADO链接以及数据库SQL链接方法介绍,以及实例讲解
  • Excel_VBAADOSQL入门教程看见星光.docx
  • 之前的100多篇文章,解决案例的大部分手段,均为数组、字典或数组与字典的组合,估计大家都看腻了。...源数据:VBA+ADO+SQL语句,小试牛刀。-1.jpg (138.42 KB, 下载次数: 0)2020-8-30 03:33 上传查询内容:...

    之前的100多篇文章,解决案例的大部分手段,均为数组、字典或数组与字典的组合,估计大家都看腻了。今天开始更新使用SQL语句的文章。注意SQL语句貌似只能在Excel里面使用,不能在WPS里面使用。如果有其它方法让SQL在wps里运行,也欢迎大家留言补充。

    源数据:

    a3318c941136e31422f9e0be7c9e8802.gif

    VBA+ADO+SQL语句,小试牛刀。-1.jpg (138.42 KB, 下载次数: 0)

    2020-8-30 03:33 上传

    查询内容:

    a3318c941136e31422f9e0be7c9e8802.gif

    VBA+ADO+SQL语句,小试牛刀。-2.jpg (54.71 KB, 下载次数: 0)

    2020-8-30 03:33 上传

    问题描述:

    1:通过SQL语句,获取表格中 条码、仓位、货号的数据;

    解决的思路详解:

    SQL的核心语句

    Sql = "SELECT 条码,仓位,货号 FROM [商品信息目录$] ";

    代码运行的结果如下:

    a3318c941136e31422f9e0be7c9e8802.gif

    VBA+ADO+SQL语句,小试牛刀。-3.jpg (77.17 KB, 下载次数: 0)

    2020-8-30 03:33 上传

    代码如下:

    a3318c941136e31422f9e0be7c9e8802.gif

    VBA+ADO+SQL语句,小试牛刀。-4.jpg (53.12 KB, 下载次数: 0)

    2020-8-30 03:33 上传

    SQL语句汇总,查询获取多列的数据,SQL语句如下:

    SELECT 列字段名1,列字段名2,列字段名3 FROM [工作表名称$]注意事项: 1.在SQL语句中SQL语句英文不区分大小写,但标点符号必须是英文半角状态下输入,字段名也必须跟原来的一样。

    2.使用SQL语句的时候,必须避免列字段中使用下面的特殊字符:

    空格、双引号(")、撇(')、数字标记(#)、百分号(%)、大于号(>)、小于号(

    如果在源数据表的列字段使用了这些特殊字符,那么在使用SQL语句列出各字段的数据时,就会发生错误。为了规范使用SQL语句,在对数据源字段命名时,尽量避免使用这些特殊字符。Sub DoSql_Execute() Dim cnn As Object, rst As Object Dim Mypath As String, Str_cnn As String, Sql As String Dim i As Long Set cnn = CreateObject("adodb.connection") '以上是第一步,后期绑定ADO  Mypath = ThisWorkbook.FullName '以上获取当前工作簿的路径及名称  If Application.Version < 12 Then '格式为.xls,调用下述语句 Str_cnn = "Provider=Microsoft.jet.OLEDB.4.0;Extended Properties=Excel 8.0;Data Source=" & Mypath Else '格式为.xlsx,调用下述语句 Str_cnn = "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;Data Source=" & Mypath End If cnn.Open Str_cnn '判断一下当前Excel的版本,12.0以下使用“Provider=Microsoft.Jet.Oledb.4.0”,以上使用“Microsoft.ACE.OLEDB.12.0” '以上是第二步,建立链接 ' Sql = "SELECT 条码,仓位,货号 FROM [商品信息目录$] " 'Sql语句,查询商品信息目录表中条码,仓位,货号的数据 [a2:c1000].ClearContents '清空[a2:c1000]区域内容 Range("a2").CopyFromRecordset cnn.Execute(Sql) 'Execute语句先执行SQL语句 '使用单元格对象的CopyFromRecordset方法将SQL查询到的内容复制到D2单元格为左上角的单元格区域 '以上是第三步,执行SQL语句并将数据读入表格指定区域 cnn.Close '关闭链接 Set cnn = Nothing '释放内存End Sub

    代码解析:

    本例代码,基本都有注释,所以简单的说明一下:

    1:5~18行 语句为通用语句,其它表格运用,可以直接复制此代码;

    2:21行 SQL语句,根据不同的查找内容,进行设置;

    3:25行 用单元格对象CopyFromRecordset复制 SQL查询到的内容;但是CopyFromRecordset方法,不会返回列头和列表。返回列头和列表的方法,明天再更新。

    4:29~31行 代码为通用语句。

    点击关注可以更方便的查看Excel VBA的案例文章

    私信 视频 可以获取54集VBA入门视频

    私信 VBA或 vba 可以获取文章中含VBA代码的Excle文件

    展开全文
  • 【VBAPlanet】VBA+ADO+SQL,这样取数真香

    千次阅读 2020-07-24 12:23:39
    更重要的原因是,在EXCEL VBA中,一般只有通过ADO,才可以使用强大的SQL查询语言访问外部数据源,进而查、改、增、删相关数据源中的数据。 延伸在具体编程操作上,就形成了四步走发展战略: VBA引用ADO类库

    ADO是什么

    首先,我们要介绍下ADO。
    ADO (ActiveX Data Objects,ActiveX数据对象)是微软提出的应用程序接口,用以实现访问关系或非关系数据库中的数据……更多概念信息可以自行搜索。

    之所以要学习ADO,一个原因是ADO自身的一些属性和方法对于数据处理是极其有益的;更重要的原因是,在EXCEL VBA中,一般只有通过ADO,才可以使用强大的SQL查询语言访问外部数据源,进而查、改、增、删相关数据源中的数据。

    延伸在具体编程操作上,就形成了四步走发展战略:

    1. VBA引用ADO类库。
    2. ADO建立对数据源的链接。
    3. ADO执行SQL语言。
    4. VBA处理SQL查询结果。

    如何引用ADO

    VBA中引用ADO类库一般有两种方式。

    一种是前期绑定。

    所谓前期绑定,是指在VBE中手工勾选引用Microsoft ADO相关类库。

    在Excel中,按<Alt+F11>快捷键打开VBA编辑窗口,依次单击【工具】→【引用】,打开【引用-VBAProject】对话框。在【可使用的引用】列表框中,勾选“Microsoft ActiveX Data Objects 2.8 Library”库,或“Microsoft ActiveX Data Objects 6.1 Library”库,单击【确定】按钮关闭对话框。

    在这里插入图片描述

    另一种是使用代码后期绑定。

    Sub 后期绑定()
        Dim cnn As Object
        Set cnn = CreateObject("adodb.connection")
    End Sub
    

    两种方式的主要区别是,前期绑定后,在代码编辑过程中,VBE的“自动列出成员”功能,可以提供ADO的属性和方法,这便于代码快捷、准确的编写。但当他人的Excel工作簿并没有手工前期绑定ADO类库时,相关代码将无法运行。因此后期代码绑定ADO的通用性会更强些,它不需要手工绑定相关类库。

    比较好的用法是,代码编写及调试时,使用前期绑定,代码编写完善后,再修改为后期绑定发布使用。

    不论我们使用SQL语言对数据源作何操作,都得首先使用ADO创建并打开一个到数据源的链接;这就好比得先修路,才能使用汽车运输货物。

    在VBA中,我们通常使用ADO的Connection.Open语句来显式建立一个到数据源的链接。

    Connection.Open语法如下:

    connection.Open ConnectionString, UserID, Password, Options
    
    • ConnectionString可选,字符串,包含连接信息。
    • UserID可选,字符串,包含建立连接时所使用用户名。
    • Password可选,字符串,包含建立连接时所使用密码。
    • Options可选,决定该方法是在连接建立之后(异步)还是连接建立之前(同步)返回,默认是同步,adAsyncConnect是异步。

    虽然不同的数据库或文件有不同的连接字符串,但常用的数据库或文件的连接字符串均是固定的。

    举个例子,如果将代码所在的Excel(2019版)作为一个外部数据源建立链接,代码如下:

    Sub Testcnn()
        Dim cnn As Object	' 定义变量
        Set cnn = CreateObject("adodb.connection")	' 后期绑定ADO
        cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties='Excel 12.0;HDR=yes;IMEX=0';Data Source=" & ThisWorkbook.FullName
        ' 建立链接
        cnn.Close	' 关闭链接
        Set cnn = Nothing	' 释放内存
    End Sub
    

    简单说下上面代码连接字符串中各关键字(第4行代码)的意思。

    • Provider是Connection
      对象提供者名称的字符串值,03版Excel是“Microsoft.jet.OLEDB.4.0”,其它版本可以使用“Microsoft.ACE.OLEDB.12.0”;
    • Extended Properties是Excel版本号及其它相关信息,03版本是Excel 8.0,其它版本可以使用Excel
      12.0。
    • 其中HDR项是引用工作表是否有标题行,默认值HDR=Yes,意思是引用表的第一行是标题行。标题只能一行,不能多行,亦不能存在合并单元格。HDR=no,意思是引用表不存在标题行,也就是说第一行开始就是数据记录了;此时,相关字段名在SQL语句中可以使用f加序列号表示,第1列字段名是f1,第2列字段名是f2,其余以此类推,f是英文field(字段)的缩写。
    • IMEX项是汇入模式,默认为0(只读模式),1是只写,2是可读写。当参数设置为1时,除了只写,还有默认全部记录数据类型为文本的用途,关于这一点及其限制前提我们以后再谈。
    • Data Source是数据来源工作薄的完整路径。

    VBA代码Application.Version可以获取计算机的Excel版本号,因此以下代码兼顾了03及各高级版本Excel的情况:

    Sub Testcnn2()
        Dim cnn As Object
        Dim strPath As String
        Dim str_cnn As String
        Set cnn = CreateObject("adodb.connection")
        strPath = ThisWorkbook.FullName '当前工作簿的完整路径
        If Application.Version < 12 Then '判断Excel版本号,以使用不同的连接字符串
            str_cnn = "Provider=Microsoft.jet.OLEDB.4.0;Extended Properties=Excel 8.0;Data Source=" & strPath
        Else
            str_cnn = "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;Data Source=" & strPath
        End If
        cnn.Open str_cnn
        cnn.Close
        Set cnn = Nothing
    End Sub
    

    最后,需要提醒大家的是,链接是一种昂贵的资源(官方语),因此在代码运行完毕后,请养成关闭链接(cnn.Close)并释放内存(Set cnn = Nothing)的好习惯。

    如何使用ADO执行SQL语句

    聊完了如何绑定ADO以及建立与数据源的链接,最后说下如何使用ADO执行SQL语句。

    我们可以使用ADO的Connection对象或Recordset、Commannd执行SQL语句;这里大家只需要先了解Connection对象的Execute方法就可以了。

    这是一个最常用的VBA+ADO+SQL套路化查询代码。

    Sub DoSql_Execute1()
        Dim cnn As Object, rst As Object
        Dim strPath As String, str_cnn As String, strSQL As String
        Dim i As Long
        Set cnn = CreateObject("adodb.connection")
        '以上是第一步,后期绑定ADO
        '
        strPath = ThisWorkbook.FullName
        If Application.Version < 12 Then
            str_cnn = "Provider=Microsoft.jet.OLEDB.4.0;Extended Properties=Excel 8.0;Data Source=" & strPath
        Else
            str_cnn = "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;Data Source=" & strPath
        End If
        cnn.Open str_cnn
        '以上是第二步,建立链接
        '
        strSQL = "SELECT 姓名,成绩 FROM [Sheet1$] WHERE 成绩>=80"
        'Sql语句,查询Sheet1表成绩大于80……姓名和成绩的记录
        Set rst = cnn.Execute(strSQL)
        'Execute()执行SQL语句,始终得到一个新的记录集rst
        '以上是第三步,编写并执行SQL
        '
        Worksheets("结果表").Select '选中存放结果的工作表
        Cells.ClearContents '清空值
        For i = 0 To rst.Fields.Count - 1
        '利用fields属性获取所有字段名,fields包含了当前记录有关的所有字段,fields.count得到字段的数量
        '由于Fields.Count下标为0,又从0开始遍历,因此总数-1
            Cells(1, i + 1) = rst.Fields(i).Name
        Next
        Range("a2").CopyFromRecordset rst
        '使用单元格对象的CopyFromRecordset方法将rst内容复制到A2单元格为左上角的单元格区域
        '以上是第四步,将SQL查询结果和字段名写入表格指定区域
        '
        cnn.Close '关闭链接
        Set cnn = Nothing '释放内存
    End Sub
    

    简洁版(不考虑复制表头列名称)

    Sub DoSql_Execute2()
        Dim cnn As Object, rst As Object
        Dim strPath As String, str_cnn As String, strSQL As String
        Dim i As Long
        Set cnn = CreateObject("adodb.connection")
        '以上是第一步,后期绑定ADO
        '
        strPath = ThisWorkbook.FullName
        If Application.Version < 12 Then
            str_cnn = "Provider=Microsoft.jet.OLEDB.4.0;Extended Properties=Excel 8.0;Data Source=" & strPath
        Else
            str_cnn = "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;Data Source=" & strPath
        End If
        cnn.Open str_cnn
        '以上是第二步,建立链接
        '
        strSQL = "SELECT 姓名,成绩 FROM [Sheet1$] WHERE 成绩>=80"
        'Sql语句,查询Sheet1表成绩大于80……姓名和成绩的记录
        Worksheets("结果表").Select '选中存放结果的工作表
        Cells.ClearContents '清空值
        Range("a2").CopyFromRecordset cnn.Execute(strSQL)
        'Execute语句先执行SQL语句
        '使用单元格对象的CopyFromRecordset方法将SQL查询到的内容复制到A2单元格为左上角的单元格区域
        '此法代码简洁,但未能获取标题栏信息
        '以上是第三步,执行SQL语句并将数据读入表格指定区域
        '
        cnn.Close '关闭链接
        Set cnn = Nothing '释放内存
    End Sub
    

    以上。通常,我们只需要修改SQL语言以及放置查询结果的工作表名称。

    展开全文
  • ? ? ? 本课程属性Excel?VBA中比较高阶的课程,主要讲解关于各种事件的应用,各种控件的应用,如果...第2章:窗体控件应用第3章:ActiveX控件应用第4章:类模块应用第5章:vba+ado+sql第6章:实战应用(考试系统开发)
  • VBA程序用ADO读取CSV

    2016-02-05 11:35:06
    代码很短 实例10-2.xls 学生成绩.csv
  • 1.上期我们聊了SQL常用查询语句中的字段查询,其简化版语法如下:SELECT 字段名 FROM 表名当时我们说,FROM关键词指明了要获取字段信息的表的名称。倘若数据源是Excel表格,则需要在表名后增加美元符号$,并用中括号...

    1.

    上期我们聊了SQL常用查询语句中的字段查询,其简化版语法如下:

    SELECT 字段名 FROM 表名

    当时我们说,FROM关键词指明了要获取字段信息的表的名称。倘若数据源是Excel表格,则需要在表名后增加美元符号$,并用中括号包起来,例如[Sheet1$]……

    事实上,上述例子是SQL In Excel 对工作表引用最简单的一种情况,也就是整表引用;此外还有单元格区域引用、跨工作簿引用表等。

    所以我们今天就再来聊一下SQL语句中的Excel表。

    2.区域成表

    Excel工作表和数据库的数据表有很多不同之处,最显著的地方在于,数据库的数据表是由行列构成的,而Excel工作表则是由一个又一个单元格构成的,且这些单元格拥有独特的地址表述方法(A1和R1C1),它们还可以构成数据相连的单元格区域,例如A2:H8。

    于是问题来了,如果我们只需要使用SQL语言计算某张Excel工作表的部分区域该怎么表述呢?

    这种问题是很常见的。

    比如,很多人的Excel标题行并不是处于表格的第一行,而是第2行……

    如下图:

    123774905_1_20180202114939675

    此时,我们希望SQL可以使用A2:F列的单元格区域作为表,而不是整个Excel工作表,这样我们更容易使用字段名处理数据……对吧?(我转头看看书柜,书柜不说话……)

    再比如,一张表里存在两个或更多个“表”……这句话什么意思呢?

    见下图。

    123774905_2_20180202114939784

    图中所示的表格中,既存在一份“教师表”,又存在一份“学生表”;如果我们只希望SQL引用计算A2:D8的教师表数据……

    123774905_3_20180202114939909

    呃,请把刀放下,君子动手不动刀……

    ……好伐,Excel中的SQL其实是支持将工作表的单元格区域作为“表”使用的。

    上图所示的问题,SQL可以写成:

    SELECT 姓名,学科 FROM [数据表$A2:D8]

    查询结果如下:

    123774905_4_201802021149403

    而第1种情况,我们知道数据开始于A2单元格,但不知道结束于F列的哪个单元格,SQL可以写成:

    SELECT 姓名,爱好 FROM [学生表$A2:F]

    另外,如果我们需要SQL引用计算表格D:G整列的数据:

    SELECT * FROM [学生表$D:G]

    总结以上几种Excel工作表区域的表述方式,也就是,工作表名称 美金符号$ 相对引用状态下的单元格地址,最后使用中括号包起来。

    就酱紫。

    本节小贴士:

    [学生表$A2:F],我们说该语句可以引用从A2至F列最后存在数据的单元格区域,但这是有一个限制条件的,即非自连接状态。所谓自连接是指SQL应用于链接自身的工作簿。自链接状态下,A2:F的表达方式最多是A2:F65536行;倘若此时需要的引用行超过65536行,请使用整表模式。

    3.跨工作簿的表

    一个众所周知的问题是,Excel函数在处理跨工作薄数据时很是疲态,除了个别几个查找引用类函数(例如VLOOKUP),绝大部分函数都需要打开相关工作簿后才可以使用计算。

    是的,VLOOKUP函数并不需要打开相关工作簿也可以跨工作薄使用,而且在VLOOKUP公式书写完成后,即便你把它所引用的工作簿给删了,也不妨碍它计算,这是因为它已经把相关数据缓存到了公式所在的工作簿中,不过VLOOKUP这种模式并不支持函数嵌套使用……打个响指,关于这一点,如果你感兴趣,我们改天单独聊一下。

    说回SQL……我觉得我今天精神老是不集中,码个字也老是跑题,且慢的像蜗牛,也许有人在想我,也许有人在骂我,不管了,我先去刷会微博,哦,对了,星光俺的微博是@EXCELers,没事记得关注下……

    123774905_5_2018020211494081

    ……说回SQL~~

    ……我们之前分享的SQL语句都是处理当前工作簿的表格,如果我们所需要处理的数据位于其它工作簿时,SQL该怎么表述呢?

    例如,获取位于计算机D盘的“EH小学”文件夹下的“学生表.xlsx”工作簿中的“成绩表”的所有数据——一口气读完这话的,送你一脸坏笑 暴击点赞。

    如果是OLE DB法(该方法参考第1章)使用SQL,语句如下:

    SELECT * FROM [D:\EH小学\学生表.xlsx].[成绩表$]

    FROM后的指定表字符串有两个部分构成,第一个中括号内是指定工作簿的存放路径 带后缀的完整工作簿名称,后一个中括号内是工作表名称,两个中括号之间使用英文句号(.)相连。

    如果是通过VBA ADO使用SQL语句……

    嘟嘟

    敲书柜前方预警:VBA基础差的童鞋请自行跳过以下内容……

    相比于OLE DB法,VBA ADO的方法要灵活的多,它可以使用ADO直接创建并打开和指定工作簿的链接,因此SQL语句便无需再指定工作簿完整名称等。

    代码参考如下:

    Sub ADO_Sql()

    '适用于除2003版以外的高版本Excel

    Dim cnn As Object, rst As Object

    Dim Mypath As String, Str_cnn As String, Sql As String

    Dim i As Long

    Set cnn = CreateObject('adodb.connection')

    Mypath = 'D:\EH小学\学生表.xlsx''指定工作簿

    Str_cnn = 'Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;Data Source=' & Mypath

    cnn.Open Str_cnn'创建并打开到指定工作簿的链接

    Sql = 'SELECT * FROM [成绩表$]' 'Sql语句,查询成绩表的所有数据

    Set rst = cnn.Execute(Sql) '执行SQL

    Cells.ClearContents

    For i = 0 To rst.Fields.Count - 1

    Cells(1, i 1) = rst.Fields(i).Name

    Next

    Range('a2').CopyFromRecordset rst

    cnn.Close

    Set cnn = Nothing

    End Sub

    但更多的情况是,ADO创建的链接是一个工作簿,需要获取的数据在另一个或多个工作簿,例如跨表格数据查询统计。此时通常使用的代码如下:

    Sub ADO_Sql2()

    '适用于除2003版以外的高版本Excel

    Dim cnn As Object, rst As Object

    Dim Mypath As String, Str_cnn As String, Sql As String

    Dim i As Long

    Set cnn = CreateObject('adodb.connection')

    Mypath = ThisWorkbook.FullName '代码所在工作簿的完整名称

    Str_cnn = 'Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;Data Source=' & Mypath

    cnn.Open Str_cnn'创建到代码所在工作簿的链接

    Sql = 'SELECT * FROM [Excel 12.0;DATABASE=D:\EH小学\学生表.xlsm].[成绩表$]'

    'Sql语句,查询成绩表的所有数据

    Set rst = cnn.Execute(Sql)'执行SQL

    Cells.ClearContents

    For i = 0 To rst.Fields.Count - 1

    Cells(1, i 1) = rst.Fields(i).Name

    Next

    Range('a2').CopyFromRecordset rst

    cnn.Close

    Set cnn = Nothing

    End Sub

    (代码详细注释及适用于03~16版本Excel的VBA代码模版请参考第2章,回复关键词SQL可以获取过往系列文章)

    代码中的SQL语句是:

    SELECT * FROM [Excel 12.0;DATABASE=D:\EH小学\学生表.xlsx].[成绩表$]

    FROM指定表的字符串有两部分组成。第一个中括号中,Excel 12.0是目标工作簿的版本号,第2章时我们讲过,Excel 12.0适用于除了2003以外的所有Excel版本。DATABASE指定的是数据源工作簿的路径和名称。第2个中括号内是工作表名。两个中括号之间使用英文句号相连。

    ……

    ……

    看起来似乎VBA ADO方法的SQL语句比OLE DB法更复杂?确实如此,不过前者的功能也更强大。比如,它可以通过VBA对象的属性和方法及循环、判断语句等,有条件的筛选工作簿和工作表等……相比之下,OLE DB中的SQL语句就是纯手工原始模式了。当然,更重要的是,前者不但可以查数据,还可以增改删数据,后者却只限于查。

    ……

    ……

    展开全文
  • 主要介绍了VBA中连接SQLSERVER数据库例子,VBA是指Visual Basic for Applications,是Visual Basic的一种宏语言,需要的朋友可以参考下
  • 原标题:Excel VBA+ADO+SQL入门教程003:SQL查询中字段技巧的总结本章概要:1,几个概念1.1, 数据库和Excel工作簿1.2,数据表和Excel工作表1.3,记录、字段和Excel的行与列2,SELECT基本语法3,如何使用SQL查询工作...

    原标题:Excel VBA+ADO+SQL入门教程003:SQL查询中字段技巧的总结

    本章概要:

    1,几个概念

    1.1, 数据库和Excel工作簿

    1.2,数据表和Excel工作表

    1.3,记录、字段和Excel的行与列

    2,SELECT基本语法

    3,如何使用SQL查询工作表中的字段

    3.1,单字段查询

    3.2,多字段查询

    3.3,别名的使用

    3.4,使用通配符查询所有字段

    3.5,含有特殊字符的字段查询

    3.6,无字段的字段查询

    1.

    诸君好~

    我们……过去……

    第1章讲述了SQL In Excel的优劣。

    第2章讲述了如何在VBA中执行SQL,也就是简单认识了下ADO。

    现在……总算可以开始逐步分享SQL语句和实例了……。

    不过在这之前,还是需要对一些名词做出解释;这些名词是SQL语句释义中经常使用到的,所以请先有一个大概的了解。

    1.1数据库

    数据库是按照数据结构来组织、储存和管理数据的仓库,例如最基础的ACCESS软件等。通常情况下,我们将一个Excel工作簿视为一个数据库。是的,如您所知,Excel并不是数据库,但这并不妨碍我们把它看成一个数据库,进而使用SQL语言的某些功能处理数据。这就好比尽管男人不是女人,但一样可以织毛衣、做月嫂……不过,得承认,生孩子之类的就确实太富有挑战性了。

    1.2数据表

    数据表,或称之为表,是数据库最重要的组成部分之一,一个数据库由一个或一组数据表组成。如果我们把这句话的“数据库”,替换成“Excel工作簿”,就变成这样:表是Excel工作簿最重要的组成部分之一,一个Excel工作簿由一个或一组表组成,你看,同样也是成立的——因此,可以先将Excel工作表视为数据库中的“表”,至于异同,以后再聊。

    1203abd138da4f1c1022e4b83e8dd8de.png

    1.3记录和字段

    如上图所示,和Excel表格一样,数据库的表由行和列组成,只是不存在行号和列标,相关值自然就不能使用A6、F4之类的Excel单元格地址来表述。表的每一行描述实体的一个实例,称之为记录;每一列描述实体的一个特征或属性,称之为字段,列的标题称之为字段名或列标题,例如上图中的“姓名”。

    主键和外键……以后用到再说。

    没了。

    2.

    SQL的数据查询和操纵语句包括SELECT、UPDATE、INSERT、DELETE等,也就是所谓的查改增删,其中最重要、使用最频繁的是SELECT查询语句。查询按照复杂性划分,可以从一个简单的语句返回所有表中所有的记录,到用一个语句链接多个表并定义多种搜索条件……

    现在,就让我们从最简单的部分走起吧——走你,少年。

    0a20b63c31868b02ca8bce6a57f5a895.png

    SELECT语法如下(简化版):

    SELECT 字段名 FROM 表名

    SELECT关键字指明了要查询的字段名称,FROM关键字指明了要获取字段信息的表的名称。

    倘若数据源是Excel表格,需要在表名后增加美元符号$,并用中括号包起来,例如[Sheet1$]。

    另外需要说明的是,SQL语言对关键字大小写不敏感。SELECT、select、Select都可以使用,作用等同;基于书写规范化的原因,可能有些老师会要求关键字统一大写;嗯,我不是老师,我还小;我的意见是新手期怎么喜欢怎么顺眼怎么来——你好就好,宠溺脸。

    3.

    分享一下SQL In Excel 常用字段查询语句以及常见问题的解决方法。

    如下图所示,是一份Excel表格,表名为学生表,内容是一些看见星光之类小学生的信息数据。

    d80481f81296d698a3d644241d462641.png

    3.1,单个字段查询

    假设我们需要查询上图所示表格的姓名列数据。语句如下:

    SELECT 姓名 FROM [学生表$]

    结果如下:

    b59bce980ddf359247e54830014a5d12.png

    3.2,多个字段查询

    假设我们需要查询字段为姓名/性别/爱好的记录,语句如下:

    SELECT 姓名,性别,爱好 FROM [学生表$]

    不同字段名之间使用英文逗号间隔,但最后一个字段名不用。另外需要格外注意的是,英文逗号千万别写成中文逗号,当然,初学阶段,别说千万,亿万万也没办法阻止咱们错输中文逗号的情况发生……反复切换输入法的中英文状态真的是很烦的一匹……。

    上述语句查询结果如下:

    3c4042d562563a4ed7ece001cd00a104.png

    3.3,查询所有字段的快捷方式

    如果我们需要提取所有字段的数据呢?一种方法是将所有字段名写入SELECT语句中:

    SELECT ID,姓名,性别,年龄,爱好,得分 FROM [学生表$]

    另外一种方法是,使用星号(*)选取所有列:

    SELECT * FROM [学生表$]

    两者的区别在于,前者只选取指定字段的数据,后者包括了指定表的所有数据。对于Excel表格而言,如果字段名确定,出于安全考虑,通常使用前者更靠谱点儿。

    3.4,别名的使用

    如果我们需要更改查询结果中的字段名称,例如我不喜欢字段名“爱好”,我想把它改成“特长”,可以使用关键字as,as是英文alias 的缩写,也就是“别名”的意思。语句如下:

    SELECT 姓名,爱好 AS 特长 FROM [学生表$]

    c7dd2e91f743c6552ecb30974c8a4c56.png

    3.5,特殊字段名的处理

    当字段名存在某些特殊字符,例如空格,或者当字段名存在SQL语言的保留字,例如AS,该字段名需要使用中括号括起来,避免因语法识别混乱,系统产生错误信息的提示。

    b377cf64d5e61afa44926fc999d7c993.png

    举个例子,如上图所示,Excel表名为调查表,该表字段名中有的存在空格(姓 名),有的使用了SQL的保留字as,如果需要查询该表格姓名和地址的信息,SQL语句如下:

    select [姓 名] , [as] from [调查表$]

    查询结果如下:

    968f4d9689486d9d66615071a29e5493.png

    3.6,无字段名情况的处理

    之前讲过,Excel并不是数据库,对数据结构并没有强制性要求,因为这样的缘故,在使用SQL处理数据时,难免会碰上各种有趣的现象。

    例如,数据表纯数据没有标题行,再或者,标题行存在合并单元格等,以至于字段名无法使用,此时,倘若需要获取字段数据,对字段名通常使用f加序列号表示,引用表的第1列字段名就是f1,第2列字段名是f2,其余依次类推。

    04ecd321980f1202e38bfbce92e8fce8.png

    如上图所示,该表格没有标题栏,如果我们需要查询姓名和性别,代码如下:

    select f2 as 姓名,f3 as 性别 from [学生表$]

    查询结果如下:

    b3f4fb8e4862fceb0fcb911f63b6a136.png

    砸桌子:还记得大明湖畔使用VBA+ADO执行SQL语句时,如果引用表不存在标题栏,需要修改链接字符串的哪个参数的项吗?——出门右拐请重看第2章吧~

    4.

    今就聊这吧,上一章聊的太多了……所以今就简单点,说话的方式简单点……~

    嗯,篇首增加了章节说明,是希望新手能够更好的把握章节要点和目标;别被文章中细节繁多的枝桠部分哄住了,上帝作证,我的长相决定了吓人真不是我的本意,我只是习惯了把一些知识点交待清楚,嗯,星光俺是一个态度严谨气质活泼组织放心……省略一千字……的五有好少年。

    点赞,安。

    图文作者:看见星光返回搜狐,查看更多

    责任编辑:

    展开全文
  • ‘订单生成系统.xls ‘f6-第6列,f2-第2列 Private Sub Worksheet_Activate() On Error Resume Next Dim x As Object, yy As Object, sql As String Set x = CreateObject("ADODB....2,用ADO Connection对象查询
  • VBA中用ADO访问SQL数据库常用方法 Dim conn As New ADODB.Connection '创建一个 Connection 实例,在这里使用New等于将Dim和Set合并为一段代码执行 Dim rs As ADODB.Recordset '创建一个 Recordset 实例,不使用...
  • VBAADO连接excel配合SQL操作

    千次阅读 2018-05-07 10:22:57
    纯表操作用sql来做非常方便,整个方法跑下来只需要3秒就跑完了,没有VBA众多循环判断关系,简单的select update两句即可。需要注意的重点:vba里外连接异常困难,3个表或以上的内加外连接难以实现。我尝试了很久最终...
  • vba ado sql 创建连接

    2019-09-16 16:45:56
    sub 绑定() dim conn as object set conn=createobject("adodb.connection") end sub 数据库的打开 connection.open connectionstring,userid,password,options ...Sub Mycnn() Dim cnn As Object '定义变量 ...
  • 利用Excel的VBAADOSQL技术相结合实现财务报表自动生成——基于用友ERP-U8总账系统.pdf
  • Dim CnStr As String, Sql As String '创建两个字符串变量分别存放两个集合的SQL语句代码段 1、装载数据库(不属于Recordset集合) ============= Dim FileNamw$, DbIp$, DbName$, DbUser$, DbPw$ '以上5...
  • 自定义修改:数据库地址、sa账户密码、数据库表名,表字段可自定义添加(必须和数据库表字段相同)
  • 本程序的作用是:使用数据对象ADO访问SQL数据库,从而进行数据查询,并将查询的数据返回到EXCEL中,由于此程序需用到SQL数据库,所以只供大家参考,不便运行,但大家可以看出其精华!Private Sub CommandButton1_...
  • 绝佳excel使用sql
  • VBA基于ADO访问SQL的策略.pdf
  • 同时也介绍了如何在VBA中引用ADO类库、创建同Excel文件的连接,并分享了一个使用ADO执行SQL语言的小套路等;打个响指,对面的女孩看过来……就像我们所熟悉的Workbook对象一样,大对象ADO内部也有多个小对象,不过...
  • 利用Excel VBA_ADO创建SQL Server应用数据库.pdf
  • 我有一个数据库,其中有数百个记录已被复制。但是,重复的信息在所有字段...例如,它看起来像这样:使用SQL(VBA/ADO)命令填充来自重复记录的相应非空值的NULL字段ID Deleted Reference Name Case_Date Outcome Outco...
  • 1.添加ADO接口 工具→引用→找到Microsoft ActiveX Data Objects→确定 2.连接ADO Dim conn As New ADODB.Connection conn.Open “Provider = Microsoft.ACE.OLEDB.12.0;Data Source=D:\data\Edata.xlsx;extended ...
  • Excel+vba通用SQL查询输出器源码 用于对Excel表格的SQL查询,被SQL操作的Excel表格必须有表头,作为类似数据库的字段,并位于表格第一行,SHEET1名称被指定时请注意替换默认的from后的表名称。
  • 使用ADO对象编程访问数据库,一般按以下步骤编写代码:(1)?使用Connection对象连接到数据源(2)?使用Recordset对象的打开记录集方法获得记录集对象(也可使用Command对象执行SQL语句获得记录集对象)(3)?在程序中访问...
  • VBA通用ADO数据访问模型

    千次阅读 2019-04-16 16:11:36
    ADO是基于OLE DB的数据访问技术。它不直接与数据交互,而是把这个任务交给了...由于ADO是基于COM实现的,所以基本上任何语言都可以使用这种数据访问技术,VBA也不例外。 一、ADO对象模型 ADO对象库中主要有9个对象...
  • 使用ADO连接外部excel数据源补充小知识:在不打开文件的情况下,抓取数据1)打开数据-现有连接-浏览更多,然后导入你要导的数据,就能在不打开该文件的条件下,进行透视,操作等。然后就是通过VBA来实现这个小功能Sub...
  • 新增一个类模块'class name: adosql for vba useOption ExplicitPrivate ObjConnection As New ADODB.ConnectionPrivate ObjCommand As New ADODB.CommandPublic ObjRecordSet As New ADODB.Recor...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 1,383
精华内容 553
关键字:

adosqlvba