菜单 [工程]-[引用] 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