1180
社区成员
- 主页
请教：如何用VB编程实现将EXCEL中的数据导入ACCESS数据库
我想用VB编程实现：把EXCEL中的数据全部导入到ACCESS中，水平很菜，找不着头绪，请高手指教。哪里能找到类似的代码，那样就更好了。多谢了！
...全文
请发表友善的回复…
发表回复
fortitude 2002-05-24
非常感谢各位，由于刚来到这里，刚才还不知道如何多设置些分数，否则肯定给大家100分。
谢谢！谢谢！
谢谢！谢谢！
xxlroad 2002-05-24
请把下面的保存为Form1.frm
VERSION 5.00
Begin VB.Form Form1
Caption = "Form1"
ClientHeight = 1590
ClientLeft = 60
ClientTop = 345
ClientWidth = 5760
LinkTopic = "Form1"
ScaleHeight = 1590
ScaleWidth = 5760
StartUpPosition = 3 '窗口缺省
Begin VB.TextBox txtAccessFile
Height = 285
Left = 1560
TabIndex = 3
Top = 480
Width = 4095
End
Begin VB.CommandButton cmdLoad
Caption = "Load Data"
Default = -1 'True
Height = 495
Left = 2280
TabIndex = 2
Top = 960
Width = 1215
End
Begin VB.TextBox txtExcelFile
Height = 285
Left = 1560
TabIndex = 1
Top = 120
Width = 4095
End
Begin VB.Label Label1
Caption = "Access Database"
Height = 255
Index = 1
Left = 120
TabIndex = 4
Top = 480
Width = 1335
End
Begin VB.Label Label1
Caption = "Excel Spreadsheet"
Height = 255
Index = 0
Left = 120
TabIndex = 0
Top = 120
Width = 1335
End
End
Attribute VB_Name = "Form1"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
'download at: http://vb.cn99.com
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
' Create the Excel application.
Set excel_app = CreateObject("Excel.Application")
' Uncomment this line to make Excel visible.
' excel_app.Visible = True
' Open the Excel spreadsheet.
excel_app.Workbooks.Open FileName:=txtExcelFile.Text
' Check for later versions.
If Val(excel_app.Application.Version) >= 8 Then
Set excel_sheet = excel_app.ActiveSheet
Else
Set excel_sheet = excel_app
End If
' Open the Access database.
Set db = OpenDatabase(txtAccessFile.Text)
' Get data from the Excel spreadsheet and insert
' it into the TestValues table.
row = 1
Do
' Get the next value.
new_value = Trim$(excel_sheet.Cells(row, 1))
' See if it's blank.
If Len(new_value) = 0 Then Exit Do
' Insert the value into the database.
db.Execute "INSERT INTO TestValues VALUES (" & _
new_value & ")"
row = row + 1
Loop
' Close the database.
db.Close
Set db = Nothing
' Comment the rest of the lines to keep
' Excel running so you can see it.
' Close the workbook without saving.
excel_app.ActiveWorkbook.Close False
' Close Excel.
excel_app.Quit
Set excel_sheet = Nothing
Set excel_app = Nothing
Screen.MousePointer = vbDefault
MsgBox "Copied " & Format$(row - 1) & " values."
End Sub
' Note that this project contains a reference to
' Microsoft DAO 3.51 Object Library.
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
VERSION 5.00
Begin VB.Form Form1
Caption = "Form1"
ClientHeight = 1590
ClientLeft = 60
ClientTop = 345
ClientWidth = 5760
LinkTopic = "Form1"
ScaleHeight = 1590
ScaleWidth = 5760
StartUpPosition = 3 '窗口缺省
Begin VB.TextBox txtAccessFile
Height = 285
Left = 1560
TabIndex = 3
Top = 480
Width = 4095
End
Begin VB.CommandButton cmdLoad
Caption = "Load Data"
Default = -1 'True
Height = 495
Left = 2280
TabIndex = 2
Top = 960
Width = 1215
End
Begin VB.TextBox txtExcelFile
Height = 285
Left = 1560
TabIndex = 1
Top = 120
Width = 4095
End
Begin VB.Label Label1
Caption = "Access Database"
Height = 255
Index = 1
Left = 120
TabIndex = 4
Top = 480
Width = 1335
End
Begin VB.Label Label1
Caption = "Excel Spreadsheet"
Height = 255
Index = 0
Left = 120
TabIndex = 0
Top = 120
Width = 1335
End
End
Attribute VB_Name = "Form1"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
'download at: http://vb.cn99.com
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
' Create the Excel application.
Set excel_app = CreateObject("Excel.Application")
' Uncomment this line to make Excel visible.
' excel_app.Visible = True
' Open the Excel spreadsheet.
excel_app.Workbooks.Open FileName:=txtExcelFile.Text
' Check for later versions.
If Val(excel_app.Application.Version) >= 8 Then
Set excel_sheet = excel_app.ActiveSheet
Else
Set excel_sheet = excel_app
End If
' Open the Access database.
Set db = OpenDatabase(txtAccessFile.Text)
' Get data from the Excel spreadsheet and insert
' it into the TestValues table.
row = 1
Do
' Get the next value.
new_value = Trim$(excel_sheet.Cells(row, 1))
' See if it's blank.
If Len(new_value) = 0 Then Exit Do
' Insert the value into the database.
db.Execute "INSERT INTO TestValues VALUES (" & _
new_value & ")"
row = row + 1
Loop
' Close the database.
db.Close
Set db = Nothing
' Comment the rest of the lines to keep
' Excel running so you can see it.
' Close the workbook without saving.
excel_app.ActiveWorkbook.Close False
' Close Excel.
excel_app.Quit
Set excel_sheet = Nothing
Set excel_app = Nothing
Screen.MousePointer = vbDefault
MsgBox "Copied " & Format$(row - 1) & " values."
End Sub
' Note that this project contains a reference to
' Microsoft DAO 3.51 Object Library.
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
xxlroad 2002-05-24
http://202.98.116.66/amtd/code/index.asp?action=read&id=177
如何把Excel文件转换成mdb数据库？
如何把Excel文件转换成mdb数据库？
gyang 2002-05-24
Dim extend_str As String
Dim execl_cnn As New ADODB.Connection
Dim execl_rst As New ADODB.Recordset
Dim execl_count_rst As New ADODB.Recordset '纪录数量
Dim execl_field_rst As New ADODB.Recordset
Dim execl_strconn As String
Dim execl_strcmd As String
Dim execl_count_str As String
Dim insertStr As String
'操作oracle数据库的记录集
Dim rst As ADODB.Recordset
CommonDialog1.ShowOpen
extend_str = Right(CommonDialog1.FileName, 3)
If (extend_str = "xls") Then
execl_cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & CommonDialog1.FileName & _
";Extended Properties=Excel 8.0;"
ElseIf (extend_str = "dbf") Then
execl_cnn.ConnectionString = "Provider=MSDASQL.1;Driver=Microsoft Visual Foxpro Driver;SourceType=DBF;DBQ=" & getDir(CommonDialog1.FileName)
execl_cnn.Open
End If
If (extend_str = "xls") Then
execl_strcmd = "SELECT * from `Sheet1$` "
execl_count_str = "SELECT count(*) from `Sheet1$` "
execl_field_rst.Open execl_count_str, execl_cnn
If (execl_field_rst.Fields(0).Value = 0) Then
execl_strcmd = "SELECT * from [" & getFileNoExt(getfile(CommonDialog1.FileName)) & "$] "
execl_count_str = "SELECT count(*) from [" & getFileNoExt(getfile(CommonDialog1.FileName)) & "$] "
End If
execl_field_rst.Close
ElseIf (extend_str = "dbf") Then
execl_strcmd = "SELECT * from " & CommonDialog1.FileName
execl_count_str = "SELECT count(*) from " & CommonDialog1.FileName
End If
execl_rst.Open execl_strcmd, execl_cnn
execl_count_rst.Open execl_count_str, execl_cnn
If (CommonDialog1.FileName <> "") Then
'进度条设置
ProgressBar1.Min = 0
ProgressBar1.max = execl_count_rst.Fields(0).Value
If (execl_rst.RecordCount) Then '如果有记录
If (Option1.Value) Then '如果是仪器总库
If (execl_rst.Fields.Count = 8) Then
Set rst = yg_gain_table_recordset("yg_device")
execl_rst.MoveFirst
Do While (Not execl_rst.EOF)
rst.AddNew
rst.Fields(0).Value = yg_getNextId("yg_device_id")
rst.Fields(1).Value = execl_rst.Fields(0).Value & ""
rst.Fields(2).Value = execl_rst.Fields(1).Value & ""
rst.Fields(3).Value = execl_rst.Fields(2).Value & ""
rst.Fields(4).Value = execl_rst.Fields(3).Value & ""
rst.Fields(5).Value = execl_rst.Fields(4).Value & ""
rst.Fields(6).Value = execl_rst.Fields(5).Value & ""
rst.Fields(7).Value = execl_rst.Fields(6).Value & ""
rst.Fields(8).Value = execl_rst.Fields(7).Value & ""
rst.Update
execl_rst.MoveNext
ProgressBar1.Value = ProgressBar1.Value + 1
DoEvents
Loop
yg_free_recordset rst
Else
MsgBox "结构不一致"
Exit Sub
End If
End If
If (Option2.Value) Then '如果是外借仪器
If (execl_rst.Fields.Count = 12) Then
Set rst = yg_gain_table_recordset("yg_out_device")
execl_rst.MoveFirst
Do While (Not execl_rst.EOF)
rst.AddNew
rst.Fields(0).Value = yg_getNextId("yg_out_device_id")
rst.Fields(1).Value = execl_rst.Fields(0).Value & ""
rst.Fields(2).Value = execl_rst.Fields(1).Value & ""
rst.Fields(3).Value = execl_rst.Fields(2).Value & ""
rst.Fields(4).Value = execl_rst.Fields(3).Value & ""
rst.Fields(5).Value = execl_rst.Fields(4).Value & ""
rst.Fields(6).Value = execl_rst.Fields(5).Value & ""
rst.Fields(7).Value = execl_rst.Fields(6).Value & ""
rst.Fields(8).Value = execl_rst.Fields(7).Value & ""
rst.Fields(9).Value = execl_rst.Fields(8).Value & ""
rst.Fields(10).Value = execl_rst.Fields(9).Value & ""
rst.Fields(11).Value = execl_rst.Fields(10).Value & ""
rst.Fields(12).Value = execl_rst.Fields(11).Value & ""
rst.Update
execl_rst.MoveNext
ProgressBar1.Value = ProgressBar1.Value + 1
DoEvents
Loop
yg_free_recordset rst
Else
MsgBox "结构不一致"
Exit Sub
End If
End If
Dim execl_cnn As New ADODB.Connection
Dim execl_rst As New ADODB.Recordset
Dim execl_count_rst As New ADODB.Recordset '纪录数量
Dim execl_field_rst As New ADODB.Recordset
Dim execl_strconn As String
Dim execl_strcmd As String
Dim execl_count_str As String
Dim insertStr As String
'操作oracle数据库的记录集
Dim rst As ADODB.Recordset
CommonDialog1.ShowOpen
extend_str = Right(CommonDialog1.FileName, 3)
If (extend_str = "xls") Then
execl_cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & CommonDialog1.FileName & _
";Extended Properties=Excel 8.0;"
ElseIf (extend_str = "dbf") Then
execl_cnn.ConnectionString = "Provider=MSDASQL.1;Driver=Microsoft Visual Foxpro Driver;SourceType=DBF;DBQ=" & getDir(CommonDialog1.FileName)
execl_cnn.Open
End If
If (extend_str = "xls") Then
execl_strcmd = "SELECT * from `Sheet1$` "
execl_count_str = "SELECT count(*) from `Sheet1$` "
execl_field_rst.Open execl_count_str, execl_cnn
If (execl_field_rst.Fields(0).Value = 0) Then
execl_strcmd = "SELECT * from [" & getFileNoExt(getfile(CommonDialog1.FileName)) & "$] "
execl_count_str = "SELECT count(*) from [" & getFileNoExt(getfile(CommonDialog1.FileName)) & "$] "
End If
execl_field_rst.Close
ElseIf (extend_str = "dbf") Then
execl_strcmd = "SELECT * from " & CommonDialog1.FileName
execl_count_str = "SELECT count(*) from " & CommonDialog1.FileName
End If
execl_rst.Open execl_strcmd, execl_cnn
execl_count_rst.Open execl_count_str, execl_cnn
If (CommonDialog1.FileName <> "") Then
'进度条设置
ProgressBar1.Min = 0
ProgressBar1.max = execl_count_rst.Fields(0).Value
If (execl_rst.RecordCount) Then '如果有记录
If (Option1.Value) Then '如果是仪器总库
If (execl_rst.Fields.Count = 8) Then
Set rst = yg_gain_table_recordset("yg_device")
execl_rst.MoveFirst
Do While (Not execl_rst.EOF)
rst.AddNew
rst.Fields(0).Value = yg_getNextId("yg_device_id")
rst.Fields(1).Value = execl_rst.Fields(0).Value & ""
rst.Fields(2).Value = execl_rst.Fields(1).Value & ""
rst.Fields(3).Value = execl_rst.Fields(2).Value & ""
rst.Fields(4).Value = execl_rst.Fields(3).Value & ""
rst.Fields(5).Value = execl_rst.Fields(4).Value & ""
rst.Fields(6).Value = execl_rst.Fields(5).Value & ""
rst.Fields(7).Value = execl_rst.Fields(6).Value & ""
rst.Fields(8).Value = execl_rst.Fields(7).Value & ""
rst.Update
execl_rst.MoveNext
ProgressBar1.Value = ProgressBar1.Value + 1
DoEvents
Loop
yg_free_recordset rst
Else
MsgBox "结构不一致"
Exit Sub
End If
End If
If (Option2.Value) Then '如果是外借仪器
If (execl_rst.Fields.Count = 12) Then
Set rst = yg_gain_table_recordset("yg_out_device")
execl_rst.MoveFirst
Do While (Not execl_rst.EOF)
rst.AddNew
rst.Fields(0).Value = yg_getNextId("yg_out_device_id")
rst.Fields(1).Value = execl_rst.Fields(0).Value & ""
rst.Fields(2).Value = execl_rst.Fields(1).Value & ""
rst.Fields(3).Value = execl_rst.Fields(2).Value & ""
rst.Fields(4).Value = execl_rst.Fields(3).Value & ""
rst.Fields(5).Value = execl_rst.Fields(4).Value & ""
rst.Fields(6).Value = execl_rst.Fields(5).Value & ""
rst.Fields(7).Value = execl_rst.Fields(6).Value & ""
rst.Fields(8).Value = execl_rst.Fields(7).Value & ""
rst.Fields(9).Value = execl_rst.Fields(8).Value & ""
rst.Fields(10).Value = execl_rst.Fields(9).Value & ""
rst.Fields(11).Value = execl_rst.Fields(10).Value & ""
rst.Fields(12).Value = execl_rst.Fields(11).Value & ""
rst.Update
execl_rst.MoveNext
ProgressBar1.Value = ProgressBar1.Value + 1
DoEvents
Loop
yg_free_recordset rst
Else
MsgBox "结构不一致"
Exit Sub
End If
End If