[分享]vb控制excel及access

gdjxwz

vb控制excel及access

（2007年6月2日）

   

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

    （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语句前加上相应的对象名(或将原来的workbooks、sheet替换为自己设置的变量Exwbook、Exsheet)即可。如：

Exwbook.sheet.add      ‘增加工作表

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

……

gdjxwz

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_Wjlj、P_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 值。决定该方法是在连接建立之后（异步）还是连接建立之前（同步）返回。可以是如下某个常量：

gdjxwz

 

常量	说明
adConnectUnspecified	（默认）同步打开连接。
adAsyncConnect	异步打开连接。ConnectComplete 事件可以用于决定连接何时可用。

 

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

Source：主要用到两种，一种打开所有记录（直接是表名），一种是部分查询（需要用到SQL语句）

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

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

Options：说明source的类型。主要用到：adCmdText：source是命令；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

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

gdjxwz

参数

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

（例子见：四、EXCEL与ACCESS记录的相互转换）

 

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

gdjxwz

四、EXCEL与ACCESS记录的相互转换

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编辑过]

gdjxwz

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 编程和XML，ASP技术》第十五章）

‘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"

gdjxwz

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

已完成

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

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

xuzong

这样的帖子要顶

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

  期待啊！

wsri

如果有事例就更好了！

gdjxwz

呵呵，不知道还要什么样的事例啊