如何从打开excel,并从中读取相应的数据,关键是打开数据文件,不能使用getobject(FileName)这个函数,因为,这个效率特别慢。谢谢,需要源代码!
解决方案 »
- datagrid 怎样铺满整个窗口
- 在Access VBA控制Excel画图,但是怎么也退不出Excel.exe
- vb入门一问:编译错误,子程序或函数未定义
- TrueDBgrid 如何单独设置列中的一个单元格的输入内容的格式。 急 在线等
- 续昨天的(全部家当求一SQL语句(急! 在线等!)):已结贴 不好意思,现在只有20分了昨天答了个问题给了我10分,今天长了10分,以后再补
- 如何通过一个字符串=窗体名找到窗体?
- 这样的程序,谁会搞?
- rs-232实现红外遥控
- 请教如何用VB来编写GPIB控制软件
- 请问这样的sql语句如何写?
- 关闭系统前,如何得到提示以便进行一些操作?
- ActiveReports 2做报表,如何能动态将查询结果(recordset对象)报表?
ado,adox,excel 9.0 library
1.获得表名
'=================
Dim cnRepair As New ADODB.Connection
Dim rsRepair As New ADODB.Recordset
Dim strRepair As String
strRepair = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=" & app.Path & "\test.xls;Extended Properties='Excel 8.0;HDR=Yes'"
cnRepair.Open strRepair
Dim cat As New ADOX.Catalog
Set cat.ActiveConnection = cnRepair
For i = 0 To cat.Tables.Count - 1
'If cat.Tables.Item(i).Type = "table" Then
Debug.Print cat.Tables.Item(i).Name
'End If
Next
'=================
2.这个是你想要的
Dim RDate As String
Dim Id As String
Dim Card As String
Dim RepairCard As String
CommonDialog1.CancelError = True
On Error GoTo err
CommonDialog1.Filter = "Excel Files (*.xls)|*.xls"
CommonDialog1.ShowOpen
Dim xlApp As Excel.Application
Set xlApp = New Excel.Application
Set xlApp = CreateObject("Excel.Application")
Dim xBook As Excel.Workbook
Dim xSheet As Excel.Worksheet
Set xBook = xlApp.Workbooks.Open(CommonDialog1.FileName)
Set xSheet = xBook.Worksheets(1)
With xSheet
nRows = .Cells(2, 1).CurrentRegion.Rows.Count
'nCols = .Cells(2, 1).CurrentRegion.Columns.Count'没有空格时候的行列数
For i = 1 To nRows
If .Cells(i, 2) = "企业金税卡" Then
Card = "QJSK"
ElseIf .Cells(i, 2) = "企业IC卡" Then
Card = "QICK"
End If
Id = Trim(.Cells(i, 3))
RDate = Date
RepairCard = "update qk set status=15 where id='" & Id & "' and type='" & Card & "' and used = 1 and (status = 1 or status=22 or status=41) "
cn.Execute RepairCard
cn.Execute "insert into qk(type,id,status,editdate,used) values('" & Card & "'),'" & Id & "',15,'" & RDate & "',1"
Next
MsgBox "已将CIS系统的维修卡号设置完毕!", , "送修单"
End With
xlApp.Quit
err:
Exit Sub
'===========================