有谁知道如何在vb中访问Excel表中的数据
如题，小弟比较急，或者各位为小弟提供这样的几篇相关的文章也好。 问题点数：0、回复次数：9Top
1 楼of123（）回复于 2003-07-02 13:58:57 得分 0
DAO 的例子：
Dim db As Database
Dim rs As Recordset
Set db = OpenDatabase(sExcelPath, True, False, "Excel 5.0")
Set rs = db.OpenRecordset("Select * FROM [" & sSheetName & "$]")
sExcelPath： Excel文件名
sSheetName： Excel工作表名
Top
2 楼xzy88888（改变）回复于 2003-07-02 13:59:50 得分 0
Private Sub populatelistview()
Dim ofield As Field
Dim nfieldcount As Integer
Dim nfieldalign As Integer
Dim nfieldwidth As Single
Dim orecitem As ListItem
Dim svalformat As String
Screen.MousePointer = vbHourglass
Set db = OpenDatabase(m_sfilepath, False, False, "excel 8.0;hdr=yes;")
Set rs = db.OpenRecordset(m_ssheetname)
With lstvwidgetorders
.ListItems.Clear
.ColumnHeaders.Clear
For Each ofield In db.TableDefs(m_ssheetname).Fields
nfieldalign = IIf((ofield.Type = dbCurrency), vbRightJustify, vbLeftJustify)
nfieldwidth = TextWidth(ofield.Name) + IIf(ofield.Type = dbText, 500, 0)
.ColumnHeaders.Add , , ofield.Name, nfieldwidth, nfieldaligh
Next ofield
End With
With rs
.MoveFirst
While (Not .EOF)
If IsNull(.Fields(0)) = True Then
' If IsNull(.Fields(6)) = False Then
Set orecitem = lstvwidgetorders.ListItems.Add(, , CStr(.Fields(6)))
orecitem.SubItems(6) = .Fields(6)
orecitem.SubItems(7) = .Fields(7)
' End If
Else
Set orecitem = lstvwidgetorders.ListItems.Add(, , CStr(.Fields(0)))
For nfieldcount = 1 To .Fields.Count - 1
svalformat = IIf(.Fields(nfieldcount).Type = dbcurrecny, "$#,##0.00", "")
orecitem.SubItems(nfieldcount) = Format$("" & .Fields(nfieldcount), svalformat)
Next nfieldcount
End If
.MoveNext
Wend
End With
Set m_oselitem = orecitem
Set orecitem = Nothing
Set orecitem = Nothing
Screen.MousePointer = vbDefault
End Sub
该过程应该可以打开EXCEL文件Top
3 楼lxqlogo0（群子）回复于 2003-07-02 14:02:47 得分 0
http://expert.csdn.net/Expert/topic/1962/1962921.xml?temp=.4907648Top
4 楼of123（）回复于 2003-07-02 14:04:45 得分 0
ODBC Driver for Excel 的例子（ADO)
dim Conn as New Adodb.Connection
dim rs as New Adodb.Recordset
Conn.CursorLocation = adUseClient
Conn.Open "Driver={Microsoft Excel Driver (*.xls)};" & _
"DriverId=790;" & _
"Dbq=c:\somepath\mySpreadsheet.xls;" & _
"DefaultDir=c:\somepath;"
rs.Open ("Select * FROM [" & sSheetName & "$]"), ConnTop
5 楼of123（）回复于 2003-07-02 14:07:40 得分 0
ADO (Jet Engine):
Dim oConn As New ADODB.Connection
With oConn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Properties("Extended Properties").Value = "Excel 8.0"
.Open "C:\Book1.xls"
'....
.Close
End WithTop
6 楼lihonggen0（李洪根，MS MVP，标准答案来了）回复于 2003-07-02 17:24:28 得分 0
可以使用ado直接调用EXCEL的数据
Dim oConn As New ADODB.Connection
Dim oRS As New ADODB.Recordset
oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Book1.xls;" & _
"Extended Properties=""Excel 8.0;"""
oRS.Open "Select * from [Sheet1$]", oConn, adOpenStatic '打开Sheet1，也可以是定义的名称，或一个Range。
..
..
..
oRS.Close
oConn.Close
更具体的方法可以参考微软的Knowledge Base的文章：“Q278973 xcelADO Demonstrates How To Read/Write Data in Excel Workbooks”。
用这个方法调用EXCEL数据是非常快的。
能否用VB的ADO的方法访问Excel文件,如果能该怎样实现？
Dim adoConnection As New ADODB.Connection
Dim adoRecordset As New ADODB.Recordset
'OLE DB + ODBC Driver 方式:
'adoConnection.Open "Data Provider=MSDASQL.1;driver=Microsoft Excel Driver (*.xls);DBQ=e:\temp\book2.xls"
'Microsoft.Jet.OLEDB.4.0 方式,(建议)
adoConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=e:\temp\book2.xls;Extended Properties='Excel 8.0;HDR=Yes'"
adoRecordset.Open "select * from [sheet1$]", adoConnection, adOpenKeyset, adLockOptimistic
Debug.Print adoRecordset.RecordCount
Dim i As Integer
Do Until adoRecordset.EOF
For i = 0 To adoRecordset.Fields.Count - 1
Debug.Print adoRecordset.Fields.Item(0).Name
Debug.Print adoRecordset.Fields.Item(0).Value
Next i
adoRecordset.MoveNext
Loop
'注: OLE DB + ODBC Driver 方式不支持以下语句,但 Microsoft.Jet.OLEDB.4.0 方式支持!
adoConnection.Execute "insert into [sheet1$](F1) values(3)"
adoRecordset.AddNew Array("f1", "f2", "f3", "f4"), Array(1, 2, 3, 4)Top
7 楼lihonggen0（李洪根，MS MVP，标准答案来了）回复于 2003-07-02 17:24:53 得分 0
微软的答案：
http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/Q278/9/73.asp&NoWebContent=1Top
8 楼raincold（冷夜雨）回复于 2003-07-03 11:39:43 得分 0
Dim dbname as string
Dim oConn As New ADODB.Connection
Dim oRS As New ADODB.Recordset
dbname=app.path+"\book1.xls"
oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source= " & "dbname"
"Extended Properties=""Excel 8.0;"""
oRS.Open "Select * from [Sheet1$]", oConn, adOpenStatic
Top
9 楼raincold（冷夜雨）回复于 2003-07-03 11:40:45 得分 0
或者在VB里面创建一个EXCEL对象来打开并读取数据Top
