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工作表名
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
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 & "$]"), Conn
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 With
可以使用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)
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
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工作表名
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文件
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 & "$]"), Conn
With oConn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Properties("Extended Properties").Value = "Excel 8.0"
.Open "C:\Book1.xls"
'....
.Close
End With
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)
http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/Q278/9/73.asp&NoWebContent=1
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