วิธีการถ่ายโอนข้อมูลจากชุดระเบียน ADO ไปที่ Excel กับการดำเนินการอัตโนมัติ

การแปลบทความ การแปลบทความ
หมายเลขบทความ (Article ID): 246335 - ผลิตภัณฑ์ที่เกี่ยวข้องในบทความนี้
ขยายทั้งหมด | ยุบทั้งหมด

เนื้อหาบนหน้านี้

สรุป

คุณสามารถโอนย้ายเนื้อหาของชุดระเบียน ADO ไปยังแผ่นงาน Microsoft Excel โดยอัตโนมัติ Excel วิธีการที่คุณสามารถใช้ขึ้นอยู่กับรุ่นของ Excel ที่คุณอัตโนมัติ Excel 97, Excel 2000 และ Excel 2002 มี CopyFromRecordset วิธีที่คุณสามารถใช้ชุดระเบียนเป็นการโอนย้ายไปยังช่วง CopyFromRecordset ใน Excel 2000 และ 2002 สามารถใช้เพื่อคัดลอก DAO แบบหรือมีชุดระเบียนของ ADO อย่างไรก็ตาม CopyFromRecordset ใน Excel 97 สนับสนุนชุดระเบียน DAO เท่านั้น การโอนย้ายมีชุดระเบียน ADO ไป Excel 97 คุณสามารถสร้างเป็นแถวได้จากชุดระเบียน และจากนั้น เติมข้อมูลในช่วงที่ มีเนื้อหาของอาร์เรย์ที่

บทความนี้กล่าวถึง approaches ทั้งสอง ตัวอย่างรหัสที่แสดงแสดงว่าคุณสามารถโอนย้ายมีชุดระเบียน ADO กับ Excel 97, Excel 2000, Excel 2002, Excel 2003 หรือ Excel 2007

ข้อมูลเพิ่มเติม

ตัวอย่างรหัสที่จัดให้ด้านล่างนี้แสดงวิธีการคัดลอก ADO มีชุดระเบียนไปยังแผ่นงาน Microsoft Excel โดยใช้การดำเนินการอัตโนมัติจาก Microsoft Visual Basic รหัสการตรวจสอบรุ่นของ Excel ก่อน ถ้า Excel 2000 หรือ 2002 จะตรวจพบ ไม่ใช้วิธีการ CopyFromRecordset เนื่องจากมีประสิทธิภาพ และต้องการรหัสน้อยลง อย่างไรก็ตาม ถ้ามีการตรวจพบ Excel 97 หรือรุ่นก่อนหน้า ชุดระเบียนถูกคัดก่อนลอกไปยังอาร์เรย์โดยใช้วิธีการ GetRows ของวัตถุชุดระเบียนของ ADO อาร์เรย์คือ transposed แล้วเพื่อให้ระเบียนที่อยู่ในขนาดแรก (ในแถว), และเขตข้อมูลในสองมิติ (ในคอลัมน์) จากนั้น อาร์เรย์ถูกคัดลอกไปยังแผ่นงาน Excel โดยการกำหนดให้กับอาร์เรย์ไปยังช่วงของเซลล์ (อาร์เรย์คือคัดลอกในขั้นตอนเดียว แทน looping ถึงแต่ละเซลล์ในแผ่นงาน)

ตัวอย่างรหัสใช้ฐานข้อมูลตัวอย่างของ Northwind ที่มาพร้อมกับ Microsoft Office ถ้าคุณเลือกโฟลเดอร์เริ่มต้นเมื่อคุณทำการติดตั้ง Microsoft Office ฐานข้อมูลจะอยู่ใน:

\Program Files\Microsoft Office\Office\Samples\Northwind.mdb

ถ้า Northwind ในฐานข้อมูลอยู่ในโฟลเดอร์อื่นบนคอมพิวเตอร์ของคุณ คุณจำเป็นต้องแก้ไขเส้นทางของฐานข้อมูลในรหัสที่จัดให้ด้านล่างนี้

ถ้าคุณไม่มีฐานข้อมูล Northwind ที่ติดตั้งอยู่บนระบบของคุณ คุณสามารถใช้ตัวเลือกเพิ่ม/เอาออกสำหรับการตั้งค่า Microsoft Office เพื่อติดตั้งฐานข้อมูลตัวอย่าง

หมายเหตุ:ฐานข้อมูล Northwind ไม่ได้ติดตั้งเมื่อคุณติดตั้ง Office 2007 ที่ Microsoft การขอรับ Northwind 2007 แวะไปที่เว็บไซต์ต่อไปนี้ของ Microsoft:
http://office.microsoft.com/en-us/templates/TC012289971033.aspx

ขั้นตอนในการสร้างตัวอย่าง

  1. เริ่ม Visual Basic และสร้างโครงการ EXE แบบมาตรฐานขึ้นใหม่ Form1 จะถูกสร้างขึ้นตามค่าเริ่มต้น
  2. เพิ่มคำCommandButtonเมื่อต้องการ Form1
  3. คลิกอ้างอิง:จากนั้นProjectเมนู เพิ่มการอ้างอิงไปยังไลบรารีของวัตถุ 2.1 Microsoft ActiveX ข้อมูล.
  4. วางรหัสต่อไปนี้ลงในส่วนของรหัสของ Form1:
    Private Sub Command1_Click()
        Dim cnt As New ADODB.Connection
        Dim rst As New ADODB.Recordset
        
        Dim xlApp As Object
        Dim xlWb As Object
        Dim xlWs As Object
    
        
        Dim recArray As Variant
        
        Dim strDB As String
        Dim fldCount As Integer
        Dim recCount As Long
        Dim iCol As Integer
        Dim iRow As Integer
        
        ' Set the string to the path of your Northwind database
        strDB ="c:\program files\Microsoft office\office11\samples\Northwind.mdb"
      
        ' Open connection to the database
        cnt.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
            "Data Source=" & strDB & ";"
        
        ''When using the Access 2007 Northwind database
        ''comment the previous code and uncomment the following code.
        'cnt.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
        '    "Data Source=" & strDB & ";"
            
        ' Open recordset based on Orders table
        rst.Open "Select * From Orders", cnt
        
        ' Create an instance of Excel and add a workbook
        Set xlApp = CreateObject("Excel.Application")
        Set xlWb = xlApp.Workbooks.Add
        Set xlWs = xlWb.Worksheets("Sheet1")
      
        ' Display Excel and give user control of Excel's lifetime
        xlApp.Visible = True
        xlApp.UserControl = True
        
        ' Copy field names to the first row of the worksheet
        fldCount = rst.Fields.Count
        For iCol = 1 To fldCount
            xlWs.Cells(1, iCol).Value = rst.Fields(iCol - 1).Name
        Next
            
        ' Check version of Excel
        If Val(Mid(xlApp.Version, 1, InStr(1, xlApp.Version, ".") - 1)) > 8 Then
            'EXCEL 2000,2002,2003, or 2007: Use CopyFromRecordset
             
            ' Copy the recordset to the worksheet, starting in cell A2
            xlWs.Cells(2, 1).CopyFromRecordset rst
            'Note: CopyFromRecordset will fail if the recordset
            'contains an OLE object field or array data such
            'as hierarchical recordsets
            
        Else
            'EXCEL 97 or earlier: Use GetRows then copy array to Excel
        
            ' Copy recordset to an array
            recArray = rst.GetRows
            'Note: GetRows returns a 0-based array where the first
            'dimension contains fields and the second dimension
            'contains records. We will transpose this array so that
            'the first dimension contains records, allowing the
            'data to appears properly when copied to Excel
            
            ' Determine number of records
    
            recCount = UBound(recArray, 2) + 1 '+ 1 since 0-based array
            
    
            ' Check the array for contents that are not valid when
            ' copying the array to an Excel worksheet
            For iCol = 0 To fldCount - 1
                For iRow = 0 To recCount - 1
                    ' Take care of Date fields
                    If IsDate(recArray(iCol, iRow)) Then
                        recArray(iCol, iRow) = Format(recArray(iCol, iRow))
                    ' Take care of OLE object fields or array fields
                    ElseIf IsArray(recArray(iCol, iRow)) Then
                        recArray(iCol, iRow) = "Array Field"
                    End If
                Next iRow 'next record
            Next iCol 'next field
                
            ' Transpose and Copy the array to the worksheet,
            ' starting in cell A2
            xlWs.Cells(2, 1).Resize(recCount, fldCount).Value = _
                TransposeDim(recArray)
        End If
    
        ' Auto-fit the column widths and row heights
        xlApp.Selection.CurrentRegion.Columns.AutoFit
        xlApp.Selection.CurrentRegion.Rows.AutoFit
    
        ' Close ADO objects
        rst.Close
        cnt.Close
        Set rst = Nothing
        Set cnt = Nothing
        
        ' Release Excel references
        Set xlWs = Nothing
        Set xlWb = Nothing
    
        Set xlApp = Nothing
    
    End Sub
    
    
    Function TransposeDim(v As Variant) As Variant
    ' Custom Function to Transpose a 0-based array (v)
        
        Dim X As Long, Y As Long, Xupper As Long, Yupper As Long
        Dim tempArray As Variant
        
        Xupper = UBound(v, 2)
        Yupper = UBound(v, 1)
        
        ReDim tempArray(Xupper, Yupper)
        For X = 0 To Xupper
            For Y = 0 To Yupper
                tempArray(X, Y) = v(Y, X)
            Next Y
        Next X
        
        TransposeDim = tempArray
    
    
    End Function
    
  5. กดแป้น F5 เพื่อเรียกใช้โครงการ form1 ปรากฏขึ้น
  6. คลิกการCommandButtonForm1 และหมายเหตุว่า เนื้อหาของตารางใบสั่งจะแสดงอยู่ในสมุดงานใหม่ใน Excel
การใช้ CopyFromRecordset

ประสิทธิภาพและประสิทธิภาพการทำงาน CopyFromRecordset เป็นวิธีการที่ต้องการ เนื่องจาก Excel 97 สนับสนุนชุดเฉพาะ DAO ระเบียน ด้วย CopyFromRecordset ถ้าคุณพยายามที่จะผ่านชุดระเบียน ADO เพื่อ CopyFromRecordset กับ Excel 97 คุณได้รับข้อความแสดงข้อผิดพลาดต่อไปนี้:
ข้อผิดพลาดการทำ 430:
คลาสที่ไม่สนับสนุนการทำงานอัตโนมัติ หรือไม่สนับสนุนอินเทอร์เฟซที่คาดไว้
ในตัวอย่างรหัส คุณสามารถหลีกเลี่ยงข้อผิดพลาดนี้ โดยการตรวจสอบของ Excel รุ่นดังนั้นคุณไม่ได้ใช้ CopyFromRecordset รุ่น 97 ได้

หมายเหตุ:เมื่อต้องการใช้ CopyFromRecordset คุณควรทราบว่า ชุด ADO หรือ DAO ระเบียนคุณใช้ไม่ประกอบด้วยเขตข้อมูลวัตถุ OLE หรืออาร์เรย์ข้อมูลเช่นชุดระเบียนตามลำดับชั้น ถ้าคุณรวมเขตข้อมูลชนิดอย่างใดอย่างหนึ่งในชุดระเบียนเป็น วิธี CopyFromRecordset ล้มเหลว ด้วยข้อผิดพลาดต่อไปนี้:
ข้อผิดพลาดการทำ-2147467259:
วิธี CopyFromRecordset ของช่วงวัตถุล้มเหลว
การใช้ GetRows

ถ้ามีการตรวจพบ Excel 97 ใช้เมธอด GetRows ของชุดระเบียน ADO เพื่อคัดลอกชุดระเบียนเป็นอาร์เรย์ ถ้าคุณกำหนดอาร์เรย์ที่ส่งกลับ โดย GetRows ไปยังช่วงของเซลล์ในแผ่นงาน ข้อมูลใส่ข้ามคอลัมน์แทนที่จะลงแถว ตัวอย่างเช่น ถ้าชุดระเบียนมีเขตข้อมูลที่สองและแถวที่ 10 อาร์เรย์ปรากฏเป็นสองแถวและคอลัมน์ที่ 10 ดังนั้น คุณจำเป็นต้อง transpose อาร์เรย์โดยใช้ฟังก์ชัน TransposeDim() ของคุณก่อนที่จะกำหนดอาร์เรย์ให้ช่วงของเซลล์ เมื่อกำหนดอาร์เรย์ให้ช่วงของเซลล์ มีข้อจำกัดบางอย่างที่ต้องระวัง:

ข้อจำกัดต่อไปนี้นำไปใช้เมื่อกำหนดอาร์เรย์ไปยังวัตถุช่วง Excel:
  • อาร์เรย์ต้องไม่ประกอบด้วยเขตข้อมูลวัตถุ OLE หรืออาร์เรย์ข้อมูล เช่นชุดระเบียนตามลำดับชั้น Notice that the code sample checks for this condition and displays "Array Field" so that the user is made aware that the field cannot be displayed in Excel.

  • The array cannot contain Date fields that have a date prior to the year 1900. (See the "References" section for a Microsoft Knowledge Base article link.) Note that the code sample formats Date fields as variant strings to avoid this potential problem.
Note the use of the TransposeDim() function to transpose the array before the array is copied to the Excel worksheet. Instead of creating your own function to transpose the array, you can use Excel's Transpose function by modifying the sample code to assign the array to the cells as shown below:
   xlWs.Cells(2, 1).Resize(recCount, fldCount).Value = _
      xlApp.WorksheetFunction.Transpose(recArray)
				
If you decide to use Excel's Transpose method instead of the TransposeDim() function to transpose the array, you should be aware of the following limitations with the Transpose method:
  • The array cannot contain an element that is greater than 255 characters.
  • The array cannot contain Null values.
  • The number of elements in the array cannot exceed 5461.
If the above limitations are not taken into consideration when you copy an array to an Excel worksheet, one of the following run-time errors may occur:
Run-time Error 13: Type Mismatch
Run-time Error 5: Invalid procedure call or argument
Run-time Error 1004: Application defined or object defined error

ข้อมูลอ้างอิง

For additional information about limitations on passing arrays to various versions of Excel, click the following article number to view the article in the Microsoft Knowledge Base:
177991XL: Limitations of Passing Arrays to Excel Using Automation
For additional information, click the article numbers below to view the articles in the Microsoft Knowledge Base:
146406XL: How to Retrieve a Table from Access into Excel Using DAO
215965XL2000: 12:00:00 AM Displayed for Dates Earlier Than 1900
243394How To Use MFC to Copy a DAO Recordset to Excel with Automation
247412ข้อมูล: วิธีการถ่ายโอนข้อมูลไปยัง Excel จาก Visual Basic

คุณสมบัติ

หมายเลขบทความ (Article ID): 246335 - รีวิวครั้งสุดท้าย: 8 มกราคม 2554 - Revision: 4.0
ใช้กับ
  • Microsoft Office Excel 2007
  • Microsoft Excel 2002 Standard Edition
  • Microsoft Excel 2000 Standard Edition
  • Microsoft Excel 97 Standard Edition
  • Microsoft Visual Basic 5.0 Professional Edition
  • Microsoft Visual Basic 6.0 Professional Edition
  • Microsoft Visual Basic 5.0 Enterprise Edition
Keywords: 
kbexpertiseinter kbautomation kbhowto kbmt KB246335 KbMtth
แปลโดยคอมพิวเตอร์
ข้อมูลสำคัญ: บทความนี้แปลโดยซอฟต์แวร์การแปลด้วยคอมพิวเตอร์ของ Microsoft แทนที่จะเป็นนักแปลที่เป็นบุคคล Microsoft มีบทความที่แปลโดยนักแปลและบทความที่แปลด้วยคอมพิวเตอร์ เพื่อให้คุณสามารถเข้าถึงบทความทั้งหมดในฐานความรู้ของเรา ในภาษาของคุณเอง อย่างไรก็ตาม บทความที่แปลด้วยคอมพิวเตอร์นั้นอาจมีข้อบกพร่อง โดยอาจมีข้อผิดพลาดในคำศัพท์ รูปแบบการใช้ภาษาและไวยากรณ์ เช่นเดียวกับกรณีที่ชาวต่างชาติพูดผิดเมื่อพูดภาษาของคุณ Microsoft ไม่มีส่วนรับผิดชอบต่อความคลาดเคลื่อน ความผิดพลาดหรือความเสียหายที่เกิดจากการแปลเนื้อหาผิดพลาด หรือการใช้บทแปลของลูกค้า และ Microsoft มีการปรับปรุงซอฟต์แวร์การแปลด้วยคอมพิวเตอร์อยู่เป็นประจำ
ต่อไปนี้เป็นฉบับภาษาอังกฤษของบทความนี้:246335

ให้ข้อเสนอแนะ

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com