答案:加代码如下:
Private Sub Command1_Click()
Dim cn As Connection
Dim rs As Recordset
Dim sql As String
Dim n As Long, m As Long
Set cn = New Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & App.Path & "\test.mdb;"
sql = "SELECT * From [Sheet1$] IN " & Chr(34) & App.Path & "\test.xls" & Chr(34) & Chr(32) & Chr(34) & "EXCEL 8.0;" & Chr(34)
Debug.Print sql
Set rs = cn.Execute(sql)
While Not rs.EOF
sql = "insert into tb(id,num,dt) values ('" & rs.Fields(0) & "'," & Val(rs.Fields(1)) & ",#" & rs.Fields(2) & "#)"
cn.Execute sql, n
m = m + n
rs.MoveNext
Wend
Set rs = Nothing
Set cn = Nothing
MsgBox "成功写入数据:" & m
End Sub
要求:
1、当前文件夹下有数据库test.mdb,里面有一个表的名字叫tb,它的字段id,num,dt,第一个是字符型的,第二个是数值型的,第三个是日期型的。
2、EXCEL表格里面的表头及数据类型最好要与tb里面的字段对应。
上一个:excel vba如何实现根据数据库sql查询返回的数据集绘图表呢?
下一个:电脑上有个备份的*.dmp文件,是从Oracle数据库导出的,本机上没有Oracle,现在要导入到excel,有办法吗?