project1.frm

Visual Basic开发实用编程200例 数据库编程实例

VERSION 5.00
Begin VB.Form Form1 
   Caption         =   "从Excel中读取数据到数据库"
   ClientHeight    =   2970
   ClientLeft      =   60
   ClientTop       =   345
   ClientWidth     =   4845
   LinkTopic       =   "Form1"
   ScaleHeight     =   2970
   ScaleWidth      =   4845
   StartUpPosition =   3  '窗口缺省
   Begin VB.TextBox txtAccessFile 
      Height          =   285
      Left            =   360
      TabIndex        =   3
      Top             =   1560
      Width           =   4095
   End
   Begin VB.CommandButton cmdLoad 
      Caption         =   "Load Data"
      Default         =   -1  'True
      Height          =   495
      Left            =   1680
      TabIndex        =   2
      Top             =   2160
      Width           =   1215
   End
   Begin VB.TextBox txtExcelFile 
      Height          =   285
      Left            =   360
      TabIndex        =   1
      Top             =   720
      Width           =   4095
   End
   Begin VB.Label Label1 
      Caption         =   "Access Database："
      Height          =   255
      Index           =   1
      Left            =   360
      TabIndex        =   4
      Top             =   1200
      Width           =   1935
   End
   Begin VB.Label Label1 
      Caption         =   "Excel Spreadsheet："
      Height          =   255
      Index           =   0
      Left            =   360
      TabIndex        =   0
      Top             =   360
      Width           =   1815
   End
End
Attribute VB_Name = "Form1"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False

Option Explicit

Private Sub cmdLoad_Click()
    Dim excel_app As Object
    Dim excel_sheet As Object
    Dim db As Database
    Dim new_value As String
    Dim row As Integer
    Screen.MousePointer = vbHourglass
    DoEvents
    '建立Excel application对象
    Set excel_app = CreateObject("Excel.Application")
    '打开指定的工作薄
    excel_app.Workbooks.Open FileName:=txtExcelFile.Text
    '判断版本
    If Val(excel_app.Application.Version) >= 8 Then
        Set excel_sheet = excel_app.ActiveSheet
    Else
        Set excel_sheet = excel_app
    End If
    '打开Access数据库
    Set db = OpenDatabase(txtAccessFile.Text)
    '删除表中原有记录
    db.Execute "delete from TestValues"
    ' 从Excel工作表中获取数据并插入到数据库的TestValues表中
    row = 1
    Do
        ' 得到工作表中的值
        new_value = Trim$(excel_sheet.Cells(row, 1))
        '如果某行空，则停止
        If Len(new_value) = 0 Then Exit Do
        '将获取的值插入到数据库的表TestValues中
        db.Execute "INSERT INTO TestValues VALUES (" & new_value & ")"
        '下一行
        row = row + 1
    Loop
    ' 关闭数据库
    db.Close
    '释放内存
    Set db = Nothing
    '关闭工作表，不重新保存
    excel_app.ActiveWorkbook.Close False
    '关闭Excel
    excel_app.Quit
    '释放内存
    Set excel_sheet = Nothing
    Set excel_app = Nothing
    '鼠标指针形状还原
    Screen.MousePointer = vbDefault
    '弹出提示框
    MsgBox "Copied " & Format$(row - 1) & " values.", , "Finished"
End Sub

Private Sub Form_Load()
    '初始化文件路径
    Dim file_path As String
    file_path = App.Path
    If Right$(file_path, 1) <> "\" Then file_path = file_path & "\"
    txtExcelFile.Text = file_path & "XlsToMdb.xls"
    txtAccessFile.Text = file_path & "XlsToMdb.mdb"
End Sub