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

요약

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, Excel 2003 또는 Excel 2007로 전송하는 방법을 보여 줍니다.

추가 정보

아래에 제공된 코드 샘플에서는 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 설치에 대한 추가/제거 옵션을 사용하여 샘플 데이터베이스를 설치할 수 있습니다.

참고 Northwind 데이터베이스는 2007 Microsoft Office를 설치할 때 설치되지 않습니다. Northwind 2007을 얻으려면 다음 Microsoft 웹 사이트를 방문하세요.

Office 서식 파일 & 테마

샘플을 만드는 단계

1. Visual Basic을 시작하고 새 표준 EXE 프로젝트를 만듭니다. Form1은 기본적으로 만들어집니다.

2. Form1에 CommandButton을 추가합니다.

3. 프로젝트 메뉴에서 참조를 클릭합니다. Microsoft ActiveX Data Objects 2.1 라이브러리에 대한 참조를 추가합니다.

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. Form1에서 CommandButton을 클릭하면 주문 테이블의 내용이 Excel의 새 통합 문서에 표시됩니다.

CopyFromRecordset 사용

효율성과 성능을 위해 CopyFromRecordset이 기본 설정 방법입니다. Excel 97은 CopyFromRecordset에서 DAO 레코드 집합만 지원하므로 Excel 97을 사용하여 ADO 레코드 집합을 CopyFromRecordset에 전달하려고 하면 다음 오류가 발생합니다.

런타임 오류 430: 클래스는 Automation을 지원하지 않거나 예상되는 인터페이스를 지원하지 않습니다. 코드 샘플에서는 97 버전에 CopyFromRecordset를 사용하지 않도록 Excel 버전을 확인하여 이 오류를 방지할 수 있습니다.

참고 CopyFromRecordset을 사용하는 경우 사용하는 ADO 또는 DAO 레코드 집합에 OLE 개체 필드 또는 배열 데이터(예: 계층적 레코드 집합)를 포함할 수 없다는 점에 유의해야 합니다. 레코드 집합에 두 형식의 필드를 포함하면 CopyFromRecordset 메서드가 다음 오류와 함께 실패합니다.

런타임 오류 -2147467259: 개체 범위의 CopyFromRecordset 메서드가 실패했습니다.

GetRows 사용

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

Excel Range 개체에 배열을 할당할 때 적용되는 제한 사항은 다음과 같습니다.

• 배열은 OLE 개체 필드 또는 배열 데이터(예: 계층적 레코드 집합)를 포함할 수 없습니다. 코드 샘플은 이 조건을 확인하고 사용자가 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: Automation을 사용하여 Excel에 배열 전달 제한 사항

247412 INFO: Visual Basic에서 Excel로 데이터를 전송하는 방법