大家好:如何把外部的excel表中记录复制到vb控件datagrid中,因为我们公司用的是office2007,现在要搞个图纸的追踪小程序,我用vb创建了mdb数据库,可在做菜单时却发现导入时老是丢在导出的excel表中添加的所有记录,所有我换下方法想把excel中的记录复制到datagrid中;谁能帮我解决以上的问题吗,不胜感激!!!Private Sub into_Click() '导入
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.JET.OLEDB.4.0;Data Source=\\10.10.0.250\图形流向\sclsylb.mdb;Persist Security Info=False"
cn.Execute "INSERT INTO " + jitaihao.Combo1.Text + " SELECT * FROM [" + jitaihao.Combo1.Text + "] IN 'e:\backup.xls' 'EXCEL 5.0;'"
'查询以上插入数据
Set rs = cn.Execute("SELECT * FROM " + jitaihao.Combo1.Text + "")
While Not rs.EOF
Debug.Print rs.Fields(0); rs.Fields(1)
rs.MoveNext
Wend
MsgBox "导入成功", vbOKOnly, "提示"
Set rs = Nothing
Set cn = NothingEnd Sub
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.JET.OLEDB.4.0;Data Source=\\10.10.0.250\图形流向\sclsylb.mdb;Persist Security Info=False"
cn.Execute "INSERT INTO " + jitaihao.Combo1.Text + " SELECT * FROM [" + jitaihao.Combo1.Text + "] IN 'e:\backup.xls' 'EXCEL 5.0;'"
'查询以上插入数据
Set rs = cn.Execute("SELECT * FROM " + jitaihao.Combo1.Text + "")
While Not rs.EOF
Debug.Print rs.Fields(0); rs.Fields(1)
rs.MoveNext
Wend
MsgBox "导入成功", vbOKOnly, "提示"
Set rs = Nothing
Set cn = NothingEnd Sub
--------------------
那数据库是excel?
是这么理解吗?
如果如此,何不access
Dim i As Integer, s As Integer
Dim Econ As New ADODB.Connection
Dim ers As New ADODB.Recordset
Dim acon As New ADODB.Connection
Dim ars As New ADODB.Recordsetacon.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\10.10.0.250\图形流向\sclsylb.mdb;Persist Security Info=False"Econ.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\backup.xls;Persist Security Info=False;Extended Properties = 'excel 5.0;HDR = YES;IMEX = 1'"
Econ.CursorLocation = adUseClient
acon.CursorLocation = adUseClientacon.Open
Econ.Openers.Open "select * from " + jitaihao.Combo1.Text + "", Econ, 1, 3
ars.Open "select * from " + jitaihao.Combo1.Text + "", acon, 1, 3
acon.Execute ("delete * from " + jitaihao.Combo1.Text + "")Do While Not ers.EOF
On Error Resume Nextars.AddNew
For s = o To ers.Fields.Count - 1
ars.Fields(s) = ers.Fields(s)
Next s
ars.MoveNext
ers.MoveNext
ars.Update
ars.MoveFirstLoop
MsgBox "导入成功", vbOKOnly, "提示"ars.Close
ers.Close
Set ars = Nothing
Set ers = Nothing
End Sub
Private Sub out_Click() '导出按钮 Dim db As DatabaseSet db = OpenDatabase("\\10.10.0.250\图形流向\sclsylb.mdb")If Dir("E:\backup.xls") <> "" Then
Kill ("E:\backup.xls")
ElseCall db.Execute("select 图号,图名,材料,件数,* into " + jitaihao.Combo1.Text + " In 'E:\backup.xls' 'excel 5.0;' from " + jitaihao.Combo1.Text + "")
MsgBox "导出成功", vbOKOnly, "提示"
End If
db.Close
Set db = NothingEnd Sub
Private Sub out_Click() '导出按钮Dim db As DatabaseSet db = OpenDatabase("\\10.10.0.250\图形流向\sclsylb.mdb")If Dir("E:\backup.xls") <> "" Then
Kill ("E:\backup.xls")
Elsedb.Execute ("select 图号,图名,材料,件数,* into " + jitaihao.Combo1.Text + " In 'E:\backup.xls' 'excel 5.0;' from " + jitaihao.Combo1.Text + "")
MsgBox "导出成功", vbOKOnly, "提示"
End If
End Sub
Private Sub into_Click() '导入
Dim db As Database
Dim rs As Recordset
Dim i As Integer
Set db = OpenDatabase("E:\backup.xls", True, False, "Excel 5.0")i = MsgBox("是否导入", vbYesNo, "提示")
If i = 6 Then
db.Execute (" insert into " + jitaihao.Combo1.Text + " in '\\10.10.0.250\图形流向\sclsylb.mdb' Select * from " + jitaihao.Combo1.Text + "")
MsgBox "导入成功", vbOKOnly, "提示"
Else
MsgBox "取消导入", vbOKOnly, "提示"
End If
End Sub
或者有什么办法把excel中的记录复制到当前的datagrid中呢(给完整的vb代码哦谢谢)