菜单 [工程]-[引用] Microsoft ActiveX Data Objects 2.x

VB中操作Excel的一般做法

1、定义Excel操作变量
Dim objExcelFile As Excel.Application
Dim objWorkBook As Excel.Workbook
Dim objImportSheet As Excel.Worksheet

2、打开Excel进程，并打开目标Excel文件
Set objExcelFile = New Excel.Application
objExcelFile.DisplayAlerts = False
Set objWorkBook = objExcelFile.Workbooks.Open(strFileName)
Set objImportSheet = objWorkBook.Sheets(1)

3、获取Excel有效区域的行和列数
intLastColNum = objImportSheet.UsedRange.Columns.Count
intLastRowNum = objImportSheet.UsedRange.Rows.Count

4、逐行读取Excel中数据

由于前两行为Header部分，所以需要从第三行读取
如果第1到第10个单元格的值均为空或空格，则视为空行
For intCountI = 3 To intLastRowNum
\'\'Check if Empty Data Row
blnNullRow = True
For intI = 1 To 10
If Trim$(objImportSheet.Cells(intCountI, intI).Value) <> "" Then
blnNullRow = False
End If
Next intI
若不是空行，则进行读取动作，否则继续向后遍历Excel中的行
If blnNullRow = False Then
获取单元格中的数据，做有效性Check，并将合法数据创建为实体存入对象数组中
objImportSheet.Cells(intCountI, 1).Value
……
End If
Next intCountI

5、退出Excel进程，并关闭Excel相关操作对象
objExcelFile.Quit
Set objWorkBook = Nothing
Set objImportSheet = Nothing
Set objExcelFile = Nothing

/////////////////////////////////////////////////////////////////////
VB中操作Access的例子

\'Microsoft ADO Ext. 2.8 for DDL and Security
\'Microsoft ActiveX Data Objects 2.8 Library
\'COM+ Services Type Library
\'Microsoft DAO 3.6 Object Library
Sub CreateDatabase(mdbPath, mdbPassword)

Dim cat As New ADOX.Catalog
If mdbPassword = "" Then
cat.Create "Provider=Microsoft.Jet.OLEDB.4.0;Password=;Data Source" & mdbPath & ";"
Else
cat.Create "Provider=Microsoft.Jet.OLEDB.4.0;Password=" & mdbPassword & ";Data Source=" & mdbPath & ";"
\'cat.Create "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & mdbPath & ";"
End If
MsgBox "数据库" & mdbPath & "建立成功", vbOKOnly
\'Set wspDefault = DBEngine.Workspaces(0)
\'Set dbs = wspDefault.CreateDatabase("Newdb.mdb", _
\'dbLangGeneral & ";pwd=NewPassword", dbEncrypt)

End Sub

Sub CreateTable(mdbPath, mdbTableName, mdbSqlColumns, mdbPrimaryKey)
\'建立列的sql语句
\'mdbPath="c:\test.mdb"
\'mdbTableName="User"
\'mdbSqlColumns="ID,adInteger;UserName,adVarWChar,20;Password,adVarWChar,20"
\'mdbPrimaryKey="ID"
Dim tbl As New Table
Dim cat As New ADOX.Catalog
Dim con As ADODB.Connection
cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Password=;Data Source=" & mdbPath & ";"


Dim ArrayColumn As String
tbl.Name = mdbTableName


\'添加数据表字段(字段格式:字段名 字段类别，字段长度；最末字段没有分号)
    ArrayTotalColumn = Split(mdbSqlColumns, ";", -1, 1)
    For i = 0 To UBound(ArrayTotalColumn)
    ArraySingleColumn = Split(ArrayTotalColumn(i), ",", -1, 1)
    If UBound(ArraySingleColumn) = 1 Then
    \'tbl.Columns.Append ArraySingleColumn(0), ArraySingleColumn(1)
    tbl.Columns.Append ArraySingleColumn(0), adInteger
    Else
    \'tbl.Columns.Append ArraySingleColumn(0), ArraySingleColumn(1), ArraySingleColumn(2)
    tbl.Columns.Append ArraySingleColumn(0), adVarWChar, CInt(ArraySingleColumn(2))
    End If
    Next

\'设置数据表主键
\'tbl.Columns(mdbPrimaryKey).Properties("AutoIncrement") = True
cat.Tables.Append tbl

\'设置列的必填属性为“否”
\'tbl.Columns("Weight").Attributes = adColNullable
\'设置列的允许空字符串为“是”
\'tbl.Columns("FirstName").Properties("Jet OLEDB:Allow Zero Length") = True
    Set tbl = Nothing
    Set cat = Nothing
MsgBox "数据表" & mdbPath & "-" & mdbTableName & "建立成功", vbOKOnly
End Sub