Private Sub into_Click() '导入
Dim sql As String
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim mycon As New ADODB.Connection
Dim yourRecord As New ADODB.RecordsetSet xlApp = CreateObject("Excel.Application") '创建EXCEL对象
Set xlBook = xlApp.Workbooks.Open("E:\backup.xls") '打开已经存在的EXCEL工件簿文件
xlApp.Visible = True '设置EXCEL对象可见(或不可见)
sql = jitaihao.Combo1.Text
Set xlSheet = xlBook.Worksheets(sql) '设置活动工作表
If Val(xlApp.Application.Version) >= 8 Then
Set xlSheet = xlApp.ActiveSheet
Else
Set xlSheet = xlApp
End If
mycon.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\10.10.0.250\图形流向\sclsylb.mdb;Persist Security Info=False"
mycon.Open
yourRecord.CursorLocation = adUseClient
yourRecord.Open "select * from " + jitaihao.Combo1.Text + "", mycon, 2, 4 '打开记录集
Dim v '导入记录,用了两层循环
v = 1
Do
If Trim$(xlSheet.Cells(v, 1)) = "" Then Exit Do '外层,如果EXCEL表中读取到空行,结束
yourRecord.AddNew
Dim i As Integer
Dim new_value As String
For i = 1 To yourRecord.RecordCount
' Get the next value.
new_value = Trim$(xlSheet.Cells(v, i)) ' See if it's blank.
If Len(new_value) = 0 Then Exit Do
' Insert the value into the database.
yourRecord.Fields(i) = new_value
yourRecord.MoveNext
Next i
v = v + 1
Loop
yourRecord.Update
xlApp.ActiveWorkbook.close False
' Close Excel.
xlApp.Quit
Set xlSheet = Nothing
Set xlApp = Nothing
yourRecord.closeSet yourRecord = Nothing
MsgBox "导入成功", vbOKOnly, "提示"
End Sub
Dim sql As String
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim mycon As New ADODB.Connection
Dim yourRecord As New ADODB.RecordsetSet xlApp = CreateObject("Excel.Application") '创建EXCEL对象
Set xlBook = xlApp.Workbooks.Open("E:\backup.xls") '打开已经存在的EXCEL工件簿文件
xlApp.Visible = True '设置EXCEL对象可见(或不可见)
sql = jitaihao.Combo1.Text
Set xlSheet = xlBook.Worksheets(sql) '设置活动工作表
If Val(xlApp.Application.Version) >= 8 Then
Set xlSheet = xlApp.ActiveSheet
Else
Set xlSheet = xlApp
End If
mycon.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\10.10.0.250\图形流向\sclsylb.mdb;Persist Security Info=False"
mycon.Open
yourRecord.CursorLocation = adUseClient
yourRecord.Open "select * from " + jitaihao.Combo1.Text + "", mycon, 2, 4 '打开记录集
Dim v '导入记录,用了两层循环
v = 1
Do
If Trim$(xlSheet.Cells(v, 1)) = "" Then Exit Do '外层,如果EXCEL表中读取到空行,结束
yourRecord.AddNew
Dim i As Integer
Dim new_value As String
For i = 1 To yourRecord.RecordCount
' Get the next value.
new_value = Trim$(xlSheet.Cells(v, i)) ' See if it's blank.
If Len(new_value) = 0 Then Exit Do
' Insert the value into the database.
yourRecord.Fields(i) = new_value
yourRecord.MoveNext
Next i
v = v + 1
Loop
yourRecord.Update
xlApp.ActiveWorkbook.close False
' Close Excel.
xlApp.Quit
Set xlSheet = Nothing
Set xlApp = Nothing
yourRecord.closeSet yourRecord = Nothing
MsgBox "导入成功", vbOKOnly, "提示"
End Sub
解决方案 »
- vb使用excel控件,当程序运行时,excel程序打不开
- 发贴不结贴?
- 我在用mshFlexGrid控件显示查询结果时,并不能全部显示只能显示范表2千多条记录,请问显示出全部内容该如何解决?
- 挥泪大放血啊!!!!!!!!!!!!!!!!!
- 这种网页弹出窗口,怎样才能自动填表?
- vb6编的程序,需要修改那些东西才能编成xp风格的?
- ActiveX高手帮忙!
- 按照日期排序问题..
- 一个函数的问题!
- CSDN,想说爱你真的好难!不要说我小气,不是我不愿帮助你。
- 用VB能做一个控制界面来对西门子s7-200PLC进行工作参数的设置吗
- vb+access+word 在vb做的界面上如何实现对Word文档内容保存至access数据库
谁能帮我看看 给我个正确的逐条导入的代码啊 谢谢