자동화를 사용하여 ADO 레코드 집합에서 Excel로 데이터를 전송하는 방법

기술 자료 번역 기술 자료 번역
기술 자료: 246335 - 이 문서가 적용되는 제품 보기.
모두 확대 | 모두 축소

이 페이지에서

요약

Excel을 자동화하여 ADO 레코드 집합의 내용을 Microsoft Excel 워크시트로 전송할 수 있습니다. 자동화할 Excel 버전에 따라 사용할 수 있는 방법이 각각 다릅니다. Excel 97, Excel 2000 및 Excel 2002에는 레코드 집합을 범위로 전송하는 데 사용할 수 있는 CopyFromRecordset 메서드가 있습니다. Excel 2000 및 2002의 CopyFromRecordset는 DAO 또는 ADO 레코드 집합을 복사하는 데 사용할 수 있습니다. 그러나 Excel 97의 CopyFromRecordset는 DAO 레코드 집합만 지원합니다. ADO 레코드 집합을 Excel 97로 전송하려면 레코드 집합의 배열을 만든 다음 범위를 해당 배열의 내용으로 채울 수 있습니다.

이 문서에서는 두 방법을 모두 설명합니다. 제공된 예제 코드에서는 ADO 레코드 집합을 Excel 97, Excel 2000 또는 Excel 2002로 전송하는 방법을 보여줍니다.

추가 정보

아래에 제공된 코드 예제에서는 Microsoft Visual Basic의 자동화 기능을 사용하여 ADO 레코드 집합을 Microsoft Excel 워크시트에 복사하는 방법을 보여줍니다. 코드는 먼저 Excel 버전을 검사합니다. Excel 2000 또는 2002가 검색되면 효율적이고 보다 적은 코드를 필요로 한다는 이유로 CopyFromRecordset 메서드가 사용됩니다. 그러나 Excel 97 이전 버전이 검색되면 ADO 레코드 집합 개체의 GetRows 메서드를 사용하여 레코드 집합이 먼저 배열로 복사됩니다. 이후 레코드가 첫 번째 차원(행)에 있고 필드가 두 번째 차원(열)에 있도록 배열이 바뀝니다. 그런 다음 배열을 셀 범위에 할당하는 방법을 통해 배열이 Excel 워크시트로 복사됩니다. 배열은 워크시트의 각 셀을 모두 순환하는 대신 한 단계로 복사됩니다.

코드 예제에서는 Microsoft Office에 포함된 Northwind 예제 데이터베이스를 사용합니다. Microsoft Office를 설치할 때 기본 폴더를 선택한 경우 데이터베이스는 다음 위치에 있습니다.

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

Northwind 데이터베이스가 컴퓨터의 다른 폴더에 있는 경우 아래에 제공된 코드에서 데이터베이스의 경로를 편집해야 합니다.

시스템에 Northwind 데이터베이스가 설치되어 있지 않은 경우 Microsoft Office 설치 프로그램의 추가/제거 옵션을 사용하여 예제 데이터베이스를 설치할 수 있습니다.

예제를 만드는 단계

  1. Visual Basic을 시작하고 표준 EXE 프로젝트를 새로 만듭니다. 기본적으로 Form1이 생성됩니다.
  2. Form1에 CommandButton을 추가합니다.
  3. 프로젝트 메뉴에서 참조를 누릅니다. Microsoft ActiveX Data Objects 2.1 Library에 대한 참조를 추가합니다.
  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 & ";"
            
        ' 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 or 2002: 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. Form1에서 CommandButton을 누르고 Orders 테이블의 내용이 Excel의 새 통합 문서에 표시되는지 확인합니다.
CopyFromRecordset 사용

효율성과 성능을 위해 기본적으로 사용하는 메서드는 CopyFromRecordset입니다. Excel 97은 CopyFromRecordset가 있는 DAO 레코드 집합만 지원하기 때문에 Excel 97에서 ADO 레코드 집합을 CopyFromRecordset로 전달하려고 하면 다음 오류가 발생합니다.
런타임 오류 430:
클래스가 자동화를 지원하지 않거나 예상되는 인터페이스를 지원하지 않습니다.
코드 예제에서 97 버전에 대해 CopyFromRecordset를 사용하지 않도록 Excel의 버전을 검사하여 이 오류를 피할 수 있습니다.

참고 CopyFromRecordset를 사용할 때는 사용하는 ADO 또는 DAO 레코드 집합이 계층적 레코드 집합 같은 OLE 개체 필드나 배열 데이터를 포함할 수 없다는 것을 알고 있어야 합니다. 레코드 집합에 어느 한 유형의 필드를 포함하는 경우 CopyFromRecordset 메서드는 다음 오류와 함께 실패합니다.
런타임 오류 -2147467259:
Range 개체의 CopyFromRecordset 메서드에서 오류가 발생하였습니다.
GetRows 사용

Excel 97이 검색되면 ADO 레코드 집합의 GetRows 메서드를 사용하여 레코드 집합을 배열로 복사합니다. GetRows에서 반환하는 배열을 워크시트의 셀 범위에 할당하는 경우 데이터는 행 아래쪽이 아닌 열 가로 방향으로 배열됩니다. 예를 들어, 레코드 집합에 두 필드가 있고 10개의 행이 있는 경우 배열은 두 행과 10개의 열로 나타납니다. 따라서 배열을 셀 범위에 할당하기 전에 TransposeDim() 함수를 사용하여 배열을 바꾸어야 합니다. 배열을 셀 범위에 할당할 때 몇 가지 제한 사항이 있다는 것을 알아 두어야 합니다.

다음 제한 사항은 배열을 Excel Range 개체에 할당할 때 적용됩니다.
  • 배열에는 계층적 레코드 집합 같은 OLE 개체 필드나 배열 데이터가 포함될 수 없습니다. 코드 예제에서는 이 조건을 검사하고 "Array Field"를 표시하여 필드를 Excel에 표시할 수 없음을 사용자에게 알립니다.

  • 배열에는 날짜가 1900년 이전인 날짜 필드가 포함될 수 없습니다. 해당 Microsoft 기술 자료 문서 링크는 "참조" 절을 참조하십시오. 코드 예제에서는 이 문제가 발생하지 않도록 날짜 필드를 변형 문자열로 서식 지정합니다.
배열을 Excel 워크시트로 복사하기 전에 TransposeDim() 함수를 사용하여 배열을 바꿉니다. 자체 함수를 만들어 배열을 바꾸는 대신 아래 표시된 셀에 배열을 할당하는 예제 코드를 수정하여 Excel의 Transpose 함수를 사용할 수 있습니다.
   xlWs.Cells(2, 1).Resize(recCount, fldCount).Value = _
      xlApp.WorksheetFunction.Transpose(recArray)
				
TransposeDim() 함수를 사용하는 대신 Excel의 Transpose 메서드를 사용하여 배열을 바꾸려는 경우 Transpose 메서드에 다음과 같은 제한 사항이 있음을 유념해야 합니다.
  • 배열에는 255자 이상의 요소가 포함될 수 없습니다.
  • 배열에는 Null 값이 포함될 수 없습니다.
  • 배열에 있는 요소 수는 5461을 초과할 수 없습니다.
배열을 Excel 워크시트로 복사할 때 위의 제한 사항을 고려하지 않을 경우 다음과 같은 런타임 오류 중 하나가 발생할 수 있습니다.
런타임 오류 13: 형식이 일치하지 않습니다.
런타임 오류 5: 잘못된 프로시저 호출 또는 인수입니다.
런타임 오류 1004: 응용 프로그램 정의 또는 개체 정의 오류입니다.

참조

다양한 Excel 버전에 배열을 전달할 때의 제한 사항에 대한 자세한 내용은 Microsoft 기술 자료의 다음 문서를 참조하십시오.
177991 XL: 자동화를 사용하여 Excel에 배열을 전달할 때의 제한 사항
자세한 내용은 Microsoft 기술 자료의 다음 문서를 참조하십시오.
146406 DAO를 사용하여 Access에서 Excel로 테이블을 검색하는 방법
215965 XL2000: 12:00:00 AM이 1900년 이전 날짜로 표시된다
243394 HOWTO: MFC를 사용하여 자동화로 DAO 레코드 집합을 Excel로 복사
247412 INFO: Visual Basic에서 Excel로 데이터를 전송하는 방법




Microsoft 제품 관련 기술 전문가들과 온라인으로 정보를 교환하시려면 Microsoft 뉴스 그룹에 참여하시기 바랍니다.

속성

기술 자료: 246335 - 마지막 검토: 2005년 11월 3일 목요일 - 수정: 4.3
본 문서의 정보는 다음의 제품에 적용됩니다.
  • 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 6.0 Enterprise Edition
  • Microsoft ActiveX Data Objects 2.0
  • Microsoft ActiveX Data Objects 2.1
  • Microsoft ActiveX Data Objects 2.5
키워드:?
kbhowto kbautomation KB246335

피드백 보내기

 

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