7,765
社区成员
发帖
与我相关
我的任务
分享
'Excel¹¤×÷²¾µÄADOÁ´½Ó·½Ê½
Set oConn = New ADODB.Connection
oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & App.Path & "\Results\Orders1.xls;" & _
"Extended Properties=""Excel 8.0;HDR=NO;"""
'´ò¿ªÊ¾ÀýÊý¾Ý¿â
Dim oNWindConn As New ADODB.Connection, oOrdersRS As New ADODB.Recordset
oNWindConn.Open "provider=microsoft.jet.oledb.4.0; data source=" & sNwind
oOrdersRS.Open "SELECT [Order Details].OrderID, Products.ProductName, " & _
"[Order Details].UnitPrice , [Order Details].Quantity, " & _
"[Order Details].Discount FROM Products INNER JOIN " & _
"[Order Details] ON Products.ProductID = " & _
"[Order Details].ProductID ORDER BY [Order Details].OrderID", _
oNWindConn, adOpenStatic
'**Note: The first "row" in the Orders_Table is hidden -- it contains dummy data that
' the OLE DB Provider uses to determine the data types for the table.
'´ÓʾÀýÊý¾Ý¿âÏòExcel¹¤×÷±íÌí¼ÓÊý¾Ý
Dim oRS As New ADODB.Recordset 'Excel¹¤×÷±íµÄ¼Ç¼¼¯¶ÔÏó
'¼Ç¼¼¯´ò¿ªµÄ·½Ê½£¬Orders_TableΪ¶¨ÒåÃû³Æ£¬Ê¹Óüü¼¯Óα꼰ÀÖ¹ÛËø¶¨·½Ê½
oRS.Open "Select * from Orders_Table", oConn, adOpenKeyset, adLockOptimistic
'ÏòExcel¼Ç¼¼¯Ìí¼ÓÊý¾ÝµÄ¹ý³Ì£¬ÔÚÕâÀïExcelÍêÈ«±»×÷ΪÊý¾Ý¿âÀ´Ê¹ÓÃ
'ÕâÀïÖ÷ÒªÑÝʾExcel¹¤×÷±íÊý¾ÝÌí¼ÓµÄ·½·¨
Do While Not (oOrdersRS.EOF)
oRS.AddNew
For i = 0 To 4
oRS.Fields(i).Value = oOrdersRS.Fields(i).Value
Next
oRS.Update
oOrdersRS.MoveNext
Loop
'¹Ø±ÕʾÀýÊý¾Ý¿âÁ´½Ó
oOrdersRS.Close
Set oOrdersRS = Nothing
oNWindConn.Close
Set oNWindConn = Nothing
'¹Ø±ÕExcel¹¤×÷²¾Á´½Ó
oConn.Close
Set oConn = Nothing