ExcelHome技术论坛

 找回密码
 免费注册

QQ登录

只需一步,快速开始

EH搜索     
EH云课堂-专业的职场技能充电站 Excel转在线管理系统,怎么做看这里 Excel服务器-会Excel,做管理系统 Excel Home精品图文教程库
Excel不给力? 何不试试FoxTable! Excel 2016函数公式学习大典 高效办公必会的Office实战技巧 免费下载Excel行业应用视频
300集Office 2010微视频教程 Tableau-数据可视化工具 精品推荐-800套精选PPT模板,点击获取 ExcelHome出品 - VBA代码宝免费下载
你的Excel 2010实战技巧学习锦囊 欲罢不能, 过目难忘的 Office 新界面 Excel VBA经典代码实践指南
查看: 30651|回复: 68

[分享]vb控制excel及access

[复制链接]

TA的精华主题

TA的得分主题

发表于 2007-6-2 15:15 | 显示全部楼层 |阅读模式
本帖已被收录到知识树中,索引项:其他专业开发

vb控制excelaccess

200762

   

近期写了一段程序:从网上下载数据,写入数据库,查询、分析、打印。找了很多的资料,现作一回顾总结,本篇主要写vb控制excelaccess。(以下均为本人的一些心得,写的不对的,请各位师傅指正。希望本文能对和我一样菜的朋友有一些帮助。)

    VBA的控制方法与VB差不多,使用VBA时可参考)

 

 

一、概述

使用ACCESS存取数据肯定比EXCEL快,数据量越大越明显(本程序当数据量突破1000时,感觉比较明显)。

 

 

二、vb控制excel

1、“创建实例法”:

⑴、定义:

Public Ex As Object

Public Exwbook As Object

Public Exsheet As Object

 

⑵、打开与关闭:

    Set Ex = CreateObject("Excel.Application")

    Ex.Visible = False

    Set Exwbook = Nothing

    Set Exsheet = Nothing

    Ex.DisplayAlerts = False        '默认回答

    Set Exwbook = Ex.Workbooks.Open(P_csszqwjm)    ‘打开P_csszqwjm文件

    Set Exsheet = Exwbook.Sheets(P_cssz)             '设置当前工作表为P_cssz

    ……

 

    Exwbook.Close

    Ex.DisplayAlerts = True

    Ex.Quit

    Set Exsheet = Nothing

    Set Exwbook = Nothing

    Set Ex = Nothing

   

⑶、EXCEL控制命令:

VB中控制EXCEL只需在VBA语句前加上相应的对象名(或将原来的workbookssheet替换为自己设置的变量ExwbookExsheet)即可。如:

Exwbook.sheet.add      增加工作表

Exsheet.cells(1,1)=”试验

……

TA的精华主题

TA的得分主题

 楼主| 发表于 2007-6-2 15:16 | 显示全部楼层

2、使用ADO方法:

(具体参见下一部分)

Public Adocon As ADODB.Connection        ‘链接

Public Adorst As ADODB.Recordset         ‘记录集

   Adocon.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _

     "Data Source=C:\Book1.xls;" &  "Extended Properties=""Excel 8.0;"""

   Adorst.Open "Select * from [Sheet1$]", Adocon, adOpenStatic        '打开Sheet1,也可以是定义的名称,或一个Range

 

 

三、VB(VBA)控制ACCESS

VB(VBA)控制ACCESS有几种方法,这里只讨论ADO

1、定义:

Public Adocon As ADODB.Connection       ‘键接

Public Adorst As ADODB.Recordset         ‘记录集

 

2、打开与关闭:

    Set Adocon = New ADODB.Connection     

    Set Adorst = New ADODB.Recordset

    Adocon.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & P_Wjlj & P_lssjwjm       P_WjljP_lssjwjm分别为文件路径与文件名

    Adorst.Open P_lssj, Adocon, adOpenStatic, adLockReadOnly, adCmdTable    P_lssj:变量,表名

    P_lssjjlzs = Adorst.RecordCount     '记录总数

    If P_lssjjlzs > 0 Then

        P_sjqsj = Adorst!f_rq       '第一条记录日期字段内容

        Adorst.MoveLast

        P_zxsjrq = Adorst!f_rq      '最后一条记录日期字段内容

    End If

    Adorst.Close

    Adocon.Close

    Set Adorst = Nothing

    Set Adocon = Nothing

 

说明:

Adocon.Open方法中参数:ConnectionString, UserID, Password, Options

ConnectionString   可选,连接字符串

UserID   可选,字符串,包含建立连接时所使用用户名。

Password   可选,字符串,包含建立连接时所使用密码。

Options   可选,ConnectOptionEnum 值。决定该方法是在连接建立之后(异步)还是连接建立之前(同步)返回。可以是如下某个常量:

TA的精华主题

TA的得分主题

 楼主| 发表于 2007-6-2 15:17 | 显示全部楼层

 

常量

说明

adConnectUnspecified

(默认)同步打开连接。

adAsyncConnect

异步打开连接。ConnectComplete 事件可以用于决定连接何时可用。

 

Adorst.Open方法中参数:Source, ActiveConnection, CursorType, LockType, Options

Source:主要用到两种,一种打开所有记录(直接是表名),一种是部分查询(需要用到SQL语句)

CursorType:游标类型。主要用到:AdOpenKeyset:打开键集类型游标;AdOpenStatic:打开静态类型游标。

LockType:锁定类型。主要用到:AdLockReadOnly:只读;AdLockOptimistic:使用开放式锁定,只在调用Update 方法时才更新记录。

Options:说明source的类型。主要用到:adCmdTextsource是命令;adCmdTable:表名。

 

3、查询:

select * from 表名 where 字段=order by 字段

where:条件。可使用>  <  =  >=  <=  <>  like  between

例:ID>5 and F_LR like ‘%中国%’         ‘序号大于5并且内容中包含“中国”

order by:排序

例:order by ID desc    ‘按序号降序排列

 

日期的使用:

Cxtj = "select * from " & P_lssj & " where F_rq between #2005-10-10 10:00:00# and #2005-11-10 10:00:00#"

 

查询前10条记录:

Cxtj = "select top 10 * from " & P_lssj & " order by ID"

Adorst.open cxtj,adocon, adOpenKeyset, adLockOptimistic, adCmdText

(" order by ID"可以省略)

 

查询最后10条记录:

Cxtj = "select top 10 * from " & P_lssj & " order by ID desc"

(当数据库中数据太大时,排序需要花很长时间,此时不如先读数据,取得数据总量,然后关闭记录集,再设置合适的条件,打开记录集)

 

4、查找:

Adorst.Find criteria, SkipRows, searchDirection, start

AdoFind方法不支持组合条件查找(And  Or),因此是一种很鸡肋的方法,不如使用select

TA的精华主题

TA的得分主题

 楼主| 发表于 2007-6-2 15:17 | 显示全部楼层

参数

criteria   字符串,包含指定用于搜索的列名、比较操作符和值的语句。

SkipRows    可选,长整型值,其默认值为零,它指定当前行或 start 书签的位移以开始搜索。

searchDirection    可选的 SearchDirectionEnum 值,指定搜索应从当前行还是下一个有效行开始。其值可为 adSearchForward adSearchBackward。搜索是在记录集的开始还是末尾结束由 searchDirection 值决定。

start    可选,变体型书签,用作搜索的开始位置

 

5、删除:

Adocon.Execute ("delete from " & Gzbm)        ‘删除GZBM表中的所有记录

或:

Cxtj=”delete * from ” & gzbm & “ where ID>5 and F_LR like ‘%美国%’”

Adocon.Execute(cxtj)

 

6、增加:

Adorst.addnew

Adorst.update

(例子见:四、EXCELACCESS记录的相互转换)

 

7、压缩数据库:

Sub Yssjk(ByVal Ywjm As String)

'压缩数据库

    Dim Jrojet  As New JRO.JetEngine

    Dim Mbwjm As String

    Dim Yml As String, Mbml As String

   

    Randomize

    Mbwjm = P_Wjlj & P_lssj & CStr(Int(Rnd(1) * 1000000)) & ".mdb"

    Yml = "provider=Microsoft.Jet.OLEDB.4.0;data source='" & Ywjm & "'"

    Mbml = "provider=Microsoft.Jet.OLEDB.4.0;data source='" & Mbwjm & "';"

    On error goto cw

    Jrojet.CompactDatabase Yml, Mbml

    On error goto 0

 

    Kill Ywjm              ‘删除

    Name Mbwjm As Ywjm     ‘重命名

    Exit Sub

Cw:

    On Error GoTo 0

    aa = MsgBox("请先关闭数据库文件", vbOKOnly, "提示")

End Sub

TA的精华主题

TA的得分主题

 楼主| 发表于 2007-6-2 15:18 | 显示全部楼层

四、EXCELACCESS记录的相互转换

1、定义

Public Type P_Sjjg                     ‘定义数组类型

    Sxlx As String

    Sxsj As String

    Sxms As String

    Rq As Date

    Bdz As String

End Type

Public P_sj() As P_Sjjg                 ‘数组

Public P_sjzs As Integer                ‘数据总数

Dim Adofd as Adodb.Field           字段

 

 

2、将EXCEL中的记录写入ACCESS

 ‘数组赋值

    P_sjzs=Exsheet.cells(65536,1).end(xlup).row-1

    ReDim P_sj(P_sjzs)      重定义数组大小

    For I = 0 To P_sjzs - 1

        P_sj(I).Sxlx = Exsheet.Cells(I + 2, 2)

        P_sj(I).Sxsj = Exsheet.Cells(I + 2, 3)

        P_sj(I).Sxms = Exsheet.Cells(I + 2, 4)

        ……

Next I

‘写入数据库

    For I = 0 To P_sjzs - 1

        Adorst.AddNew

        Adorst!f_sxlx = P_sj(I).Sxlx

        Adorst!f_rq = P_sj(I).Rq

        Adorst!f_bdz = P_sj(I).Bdz

        ……

        Adorst.Update

    Next I

[此贴子已经被作者于2007-6-2 17:58:55编辑过]

TA的精华主题

TA的得分主题

 楼主| 发表于 2007-6-2 15:19 | 显示全部楼层

3、将ACCESS中的记录写入EXCEL

⑴、使用记录集

‘打开记录集

……

‘写入EXCEL

For Each Adofd in adorst.Fields

    Exsheet.cells(1,J)=Adofd.Name       字段名

    J=J+1

next

Exsheet.[a2].CopyFromRecordset Adorst

 

⑵、使用数组:

数组赋值

……

exsheet.Range(左上单元格,右下单元格).value=数组名

 

⑶、使用文本文件过渡(摘自:《学习微软 Excel 2002 VBA 编程和XMLASP技术》第十五章)

ACCESS写入文本文件(Tab键分隔)

Cxtj = "SELECT * FROM " & P_lssj & " WHERE ID > 50"

Set Adorst = Adocon.Execute(CommandText:=Cxtj, Options:=adCmdText)

strData = adorst.GetString(StringFormat:=adClipString, ColumnDelimeter:=vbTab, RowDelimeter:=vbCr, nullExpr:=vbNullString)

 

Open "C:\ProductsOver50.txt" For Output As #1

For Each Adofd In Adorst.Fields

strHeader = strHeader + Adofd.Name & vbTab

Next

Print #1, strHeader

Print #1, strData

Close #1

‘用EXCEL打开文本文件(录制宏即可)

……

删除文本文件

kill "C:\ProductsOver50.txt"

TA的精华主题

TA的得分主题

 楼主| 发表于 2007-6-2 15:20 | 显示全部楼层

第二篇为网页控制与下载,完成后上传。

已完成

http://club.excelhome.net/viewthread.php?tid=245773

[此贴子已经被作者于2007-6-10 6:33:08编辑过]

TA的精华主题

TA的得分主题

发表于 2007-6-2 16:34 | 显示全部楼层

这样的帖子要顶

   第二篇为网页控制与下载,完成后上传。

  期待啊!

TA的精华主题

TA的得分主题

发表于 2007-6-2 23:29 | 显示全部楼层

TA的精华主题

TA的得分主题

 楼主| 发表于 2007-6-3 18:43 | 显示全部楼层
您需要登录后才可以回帖 登录 | 免费注册

本版积分规则

关闭

最新热点上一条 /1 下一条

关注官方微信,每天学会一个新技能

手机版|关于我们|联系我们|ExcelHome

GMT+8, 2019-8-26 00:47 , Processed in 0.212740 second(s), 19 queries , Gzip On, MemCache On.

Powered by Discuz! X3.4

© 1999-2020 Wooffice Inc.

   

沪公网安备 31011702000001号 沪ICP备11019229号

本论坛言论纯属发表者个人意见,任何违反国家相关法律的言论,本站将协助国家相关部门追究发言者责任!     本站特聘法律顾问:徐怀玉律师 李志群律师

快速回复 返回顶部 返回列表