1,首先你得建一个access数据库,名称为:TEST01,建一个数据表:表名为:TEST02,表中有三个TEXT型字段.然后在excel表中A,B,C三列输入相一些数据.
2.在表格中建一个commandbutton把以下CommandButton1代码复制进去.
3,把后面的定义和connect,disconnect放到模块里面.
4,点击按钮,就会把A,B,C三列的的内容上传到数据库中.
Private Sub CommandButton1_Click()
On Error Resume Next
Dim cn As ADODB.Connection
Dim cnStr As String
Dim SqlStr As String
cnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dataPath & ";"
Set cn = New ADODB.Connection
cn.Open cnStr
Call Connect
If Not Flag Then
Call Disconnect
Exit Sub
End If
For i = 1 To Range("a65536").End(xlUp).Row
a1 = Cells(i, 1)
a2 = Cells(i, 2)
a3 = Cells(i, 3)
SqlStr = "insert into TEST02 values('" & a1 & "'," & a2 & "'," & a3 & "')"
cn.Execute SqlStr
DoEvents
Next i
cn.Close
Call Disconnect
End Sub
以下放到模块里面:
Public Const dataPath = "d:\Test\Test01.mdb"
Public con As New ADODB.Connection
Public rs As New ADODB.Recordset
Public Flag As Boolean
Sub Disconnect()
On Error Resume Next
rs.Close
Set rs = Nothing
If con.State = adStateOpen Then
con.Close
Set con = Nothing
End If
End Sub
Sub Connect()
On Error Resume Next
Flag = True
If con.State <> adStateOpen Then
Dim conStr As String
conStr = "Provider=Microsoft.Jet.Oledb.4.0; " & _
" Data Source=" & dataPath
con.Open conStr
If con.State <> adStateOpen Then
MsgBox "未连接到数据库,请检查!"
Flag = False
con.Close
Set con = Nothing
Exit Sub
End If
End If
End Sub
追问读取EXCEL文件,遇到空行的处理呢?
追答在上面的a1,a2,a3下面加一条语句.if a1="" or a2="" or a3="" then exit sub 只要有一个为空,则退出程序.
For i = 1 To Range("a65536").End(xlUp).Row
a1 = Cells(i, 1)
a2 = Cells(i, 2)
a3 = Cells(i, 3)
if a1="" or a2="" or a3="" then exit sub
SqlStr = "insert into TEST02 values('" & a1 & "'," & a2 & "'," & a3 & "')"
cn.Execute SqlStr
DoEvents
Next i