ExcelADO 連接下載﹐真的好好用

chajiangliang

之前總是用dim abc as excel.application 的方法 去編輯Excel 資料﹐今天找到Microsoft的好例子﹐真的如獲至寶. 沒有下載的同志們沖啊!!! (VB6.0程序﹐可簡單轉到Access)

http://support.microsoft.com/default.aspx?scid=kb;zh-tw;278973

沒有vb6.0的可依此轉換, 應該不難吧!

窗體下的代碼是

Private Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" _

                   (ByVal hwnd As Long, ByVal lpszOp As String, _

                    ByVal lpszFile As String, ByVal lpszParams As String, _

                    ByVal LpszDir As String, ByVal FsShowCmd As Long) _

                    As Long

Private Const SW_NORMAL = 1

Private sNwind As String            'Path to sample Access database

Private sOrdersTemplate As String   'Path to Orders Workbook "Template"

Private sEmpDataTemplate As String  'Path to Employee Data Workbook "Template"

Private sProductsTemplate As String 'Path to Products Workbook "Template"

Private sChartTemplate As String    'Path to Workbook "Template" containing a chart

Private sSourceData As String

Private Sub Form_Load()

    'Initialize the paths to the various workbooks that the

    'samples will use.

    sNwind = App.Path & "\data.mdb"

    sOrdersTemplate = App.Path & "\OrdersTemplate.xls"

    sEmpDataTemplate = App.Path & "\EmpDataTemplate.xls"

    sProductsTemplate = App.Path & "\ProductsTemplate.xls"

    sChartTemplate = App.Path & "\ChartingTemplate.xls"

    sSourceData = App.Path & "\SourceData.xls"

End Sub

Private Sub cmdSample1_Click()

   

    'Make a copy of the workbook template

    FileCopy sOrdersTemplate, App.Path & "\Results\Orders1.xls"

   

    'Open the ADO connection to the Excel workbook

    Set oConn = New ADODB.Connection

    oConn.Open "rovider=Microsoft.Jet.OLEDB.4.0;" & _

               "Data Source=" & App.Path & "\Results\Orders1.xls;" & _

               "Extended Properties=""Excel 8.0;HDR=NO;"""

    'Open a connection to the Northwind database and retrieve the information

    'in OrderDetails table

    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.

   

    'Add the data from the Order Details table in Northwind to the workbook

    Dim oRS As New ADODB.Recordset

    oRS.Open "Select * from Orders_Table", oConn, adOpenKeyset, adLockOptimistic

   

        For i = 0 To 4

            oRS.Fields(i).Value = oOrdersRS.Fields(i).Value

        Next

        oRS.Update

        oOrdersRS.MoveNext

    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

   

    'Close the recordset and connection to Northwind

    oOrdersRS.Close

    Set oOrdersRS = Nothing

    oNWindConn.Close

    Set oNWindConn = Nothing

   

    'Close the connection to the workbook

    oConn.Close

    Set oConn = Nothing

   

    'Open the workbook to examine the results

    DoEvents

    ShellExecute Me.hwnd, "Open", App.Path & "\Results\Orders1.xls", "", "C:\", SW_SHOWNORMAL

      

End Sub

Private Sub cmdSample2_Click()

tmtony

不错的文章,谢谢!

cdwlove

有没有实例看看呀!!!

kmq029

灌水就灌自来水