Dim dbs As Database Set dbs = OpenDatabase(App.Path & "\db1.mdb") On Error Resume Next dbs.Execute "DROP TABLE 在校学生;" dbs.Execute "SELECT * INTO 在校学生 FROM [Excel 8.0;DATABASE=" & App.Path & "\MyExcel.xls].[WorkSheet1] " dbs.Close Set dbs = Nothing Shell "C:\Program Files\Microsoft Office\Office\MSACCESS.EXE " & App.Path & "\db1.mdb", vbMaximizedFocus
access中有导入功能,导入时选择EXCEL文件即可。
'*****************从Excel中导入数据到数据库******************** Public Sub FromExcel(OpenUrl As String) '将Excel中的数据导入到数据库中 Dim xlApp As Excel.Application Dim xlBook As Excel.Workbook Dim xlsheet As Excel.Worksheet Dim nRows As Integer Dim nCols As Integer Dim k As Integer, sql As String Dim rs As New ADODB.Recordset On Error Resume Next Set xlApp = CreateObject("Excel.Application") Set xlBook = xlApp.Workbooks.Open(OpenUrl) Set xlsheet = xlBook.Sheets(1) xlApp.Visible = True With xlsheet nRows = .Cells(2, 1).CurrentRegion.Rows.Count nCols = .Cells(2, 1).CurrentRegion.Columns.Count End With 'frmSendData.Show (1) k = 2 With xlsheet For k = 2 To nRows sql = "INSERT INTO product(prd_no,prd_name,prd_price) VALUES('" & Trim(.Cells(k, "A").Value) & "','" & Trim(.Cells(k, "B").Value) & "'," & .Cells(k, "C").Value & ")" Gadocn_app.Execute (sql) DoEvents Next End With End Sub
http://www.csdn.net/expert/topic/352/352686.xml
Set dbs = OpenDatabase(App.Path & "\db1.mdb")
On Error Resume Next
dbs.Execute "DROP TABLE 在校学生;"
dbs.Execute "SELECT * INTO 在校学生 FROM [Excel 8.0;DATABASE=" & App.Path & "\MyExcel.xls].[WorkSheet1] "
dbs.Close
Set dbs = Nothing
Shell "C:\Program Files\Microsoft Office\Office\MSACCESS.EXE " & App.Path & "\db1.mdb", vbMaximizedFocus
Public Sub FromExcel(OpenUrl As String) '将Excel中的数据导入到数据库中
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlsheet As Excel.Worksheet
Dim nRows As Integer
Dim nCols As Integer
Dim k As Integer, sql As String
Dim rs As New ADODB.Recordset
On Error Resume Next
Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Open(OpenUrl)
Set xlsheet = xlBook.Sheets(1)
xlApp.Visible = True
With xlsheet
nRows = .Cells(2, 1).CurrentRegion.Rows.Count
nCols = .Cells(2, 1).CurrentRegion.Columns.Count
End With
'frmSendData.Show (1)
k = 2
With xlsheet
For k = 2 To nRows
sql = "INSERT INTO product(prd_no,prd_name,prd_price) VALUES('" & Trim(.Cells(k, "A").Value) & "','" & Trim(.Cells(k, "B").Value) & "'," & .Cells(k, "C").Value & ")"
Gadocn_app.Execute (sql)
DoEvents
Next
End With
End Sub