????? ??? ???????? ?? ?????? ????? ADO ??? ?????? Excel ???????? ??????? ????????

?????? ????????? ?????? ?????????
???? ???????: 246335 - ??? ???????? ???? ????? ????? ??? ???????.
????? ???? | ?? ????

?? ??? ??????

??????

????? ??? ??????? ?????? ????? ADO ??? ???? ??? Microsoft Excel ?? ???? ????? Excel. ????? ??????? ???? ???? ????????? ??? ????? Excel ??? ?????. Excel 2002 ? Excel 2000 ?? Excel 97 ????? ????? CopyFromRecordset ???? ????? ????????? ???? ?????? ????? ??? ????. ???? ??????? CopyFromRecordset ?? Excel 2000 ? 2002 ???? DAO ?? ?????? ????? ADO. ??? ????? ?? ???? ???? CopyFromRecordset ?? Excel 97 ??????? ????? DAO ???. ???? ?????? ????? ADO ??? Excel 97 ???? ????? ???? ?? ?????? ??????? ??? ?? ??? ?? ???? ??????? ??? ??????.

?????? ??? ??????? ????? ???. ???? ????? ???????? ???????? ????? ??? ????? ??? ?????? ????? 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. ??? ????? ?????? ?? ???? ???? ??????? ?? ????? ????? (?? ????) ????? ?????? ?? ????? ?????? (?? ???????). ?? ??? ??? ?????? ??? ???? ??? Excel ?? ???? ????? ?????? ??? ???? ?? ???????. (?????? ???????? ?? ???? ????? ????? ?? looping ???? ?? ???? ?? ???? ?????.)

????? ????????? ???????? ?????? ????? ?????? ????? Northwind ????? ?? Microsoft Office. ??? ??? ?????? ?????? ????????? ??? ????? Microsoft Office ? ???? ????? ???????? ??:

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

??? ??? Northwind ????? ???????? ??????? ?? ???? ??? ??? ?????????, ????? ??? ????? ???? ????? ???????? ?? ????????? ???????? ???????? ?????.

??? ?? ??? ???? ????? ?????? Northwind ??????? ??? ??????? ????? ??????? ???? "?????/?????" ?????? ????? Microsoft Office ?????? ????? ????? ????????.

?????? ?? ??? ????? ????? ???????? Northwind ??? ????? 2007 Microsoft Office. ?????? ??? 2007 Northwind ?? ?????? ???? Microsoft ?????? ??? ?????:
http://office.microsoft.com/en-us/templates/TC012289971033.aspx

??????? ??????? ?????? ?????

  1. ?? ?????? Visual Basic ?? ????? ????? EXE ????? ????. ??? ????? Form1 ???? ???????.
  2. ????? CommandButton Form1.
  3. ???? "?????" ?? ??????? ???????. ????? ???? ??? ????? 2.1 ?????? ?????? ActiveX Microsoft.
  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. ???? ??? CommandButton ??? Form1 ????? ??? ??????? ?????? Orders (????????) ?? ???? ???? ?? 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. ????? ??? ????? ???? ?????? ???????? ?????? TransposeDim() ??? ????? ?????? ??? ?????? ?? ???????. ??? ????? ???? ??? ???? ?? ??????? ? ???? ??? ?????? ?? ???? ??? ???:

????? ?????? ??????? ??? ????? ???? ???? ???? Excel:
  • ?? ???? ?? ????? ?????? ??? ???? ???? OLE ?? ???? ??????? ??? ??????? ??????? ??? ????? ????. ???? ??? ????? ????????? ???????? ????? ??? ????? ???? "???? ???" ???? ??? ????? ???????? ??? ?? ???? ??? ????? ?? Excel.

  • ?? ???? ?? ????? ?????? ??? ???? "???????" ???? ??? ????? ??? ??? ??? 1900. (???? ??? "???????" ?????? ??? ?????? ????? ????? ????? Microsoft.) ???? ????? ????????? ???????? ?????? ???? ??????? ?????? ????? ????? ??? ??????? ????????.
?????? ??? ??????? ?????? TransposeDim() ????? ???? ?????? ??? ?????? ??? ??? ???? ??? Excel. ????? ??????? Excel ????? ?? ?????? ?????? ?? ????? ???? ?????? ????? ?????? ?????? ????? ????? ???????? ???????? ?????? ?????? ??? ??????? ??? ?? ???? ?????:
   xlWs.Cells(2, 1).Resize(recCount, fldCount).Value = _
      xlApp.WorksheetFunction.Transpose(recArray)
				
??? ???? ??????? ??????? ????? ?? Excel ????? ?? ?????? TransposeDim() ????? ???? ?????? ???? ??? ?? ?????? ??? ??? ?????? ??????? ?? ????? ?????:
  • ?? ???? ?? ????? ?????? ??? ???? ???? ?? 255 ?????.
  • ?? ???? ?? ????? ?????? ??? ????? ???????.
  • ?? ???? ?? ?????? ??? ??????? ?? ?????? 5461.
??? ?? ??? ??? ???? ????? ???? ???????? ??? ??? ??? ??????? ??? ???? ??? Excel ? ?? ???? ??? ????? ??? ??????? ???????:
??? ??? ??????? 13: ??? ????? ???
5 ??? ??? ???????: ??????? ????? ??? ???? ?? ?????
????? ??????? 1004 ??? ??? ???????: ?? ????? ?????? ???

?????

?????? ??? ??????? ?????? ??? ???? ??? ????? ??????? ??? ????????? ???????? ?? Excel? ???? ??? ??? ??????? ?????? ?????? ?? "????? ??????? ?? Microsoft:
177991XL: ?????? ????? ?? ????? ?????? ??? Excel ???????? ???????
?????? ??? ??????? ??????? ???? ??? ???? ????????? ????????? ??????? ?? "????? ??????? ?? Microsoft:
146406XL: ????? ??????? ???? ?? Access ??? Excel ???????? DAO
215965XL2000: 12: 00: 00 ? ???????? ??? ?????? ????? 1900
243394????? ??????? MFC ??? ?????? ????? DAO ??? Excel ???????? ???????
247412INFO: ??? ??? ???????? ??? Excel ?? Visual Basic

???????

???? ???????: 246335 - ????? ??? ??????: 09/???? ?????/1428 - ??????: 5.1
????? ???
  • Microsoft Office Excel 2007
  • Microsoft Office Excel 2003
  • 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
  • Microsoft Visual Basic Enterprise Edition for Windows 6.0
  • Microsoft ActiveX Data Objects 2.0
  • Microsoft ActiveX Data Objects 2.1
  • Microsoft ActiveX Data Objects 2.5
????? ??????: 
kbmt kbexpertiseinter kbautomation kbhowto KB246335 KbMtar
????? ????
???: ??? ????? ??? ?????? ???????? ?????? ????? ???? ????? ?????????? ????? ?? ????????? ?????? ????. ???? ???? ?????????? ???? ?? ???????? ???????? ?????? ????????? ????? ????????? ???????? ????? ???????? ?????? ?? ?????? ??? ?? ???????? ???????? ?? ????? ??????? ?????? ??? ??????? ?????? ??. ?????? ?? ???? ??? ??????? ???????? ????? ?? ???? ????? ?????? ??? ????? ??? ????? ??????? ?? ????? ?? ?????? ??? ??? ??????? ??????? ?? ????? ????? ????? ????? ?????. ?? ????? ???? ?????????? ??????? ??? ????? ?? ??????? ?? ????? ?????? ?? ??? ????? ?? ????? ??????? ?? ???????? ?? ??? ???????. ???? ???? ?????????? ???????? ??? ????? ?????? ??????? ??????
???? ??? ????? ??????? ?????? ??????????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