Visual Basic 또는 VBA에서 Excel 데이터에 ADOX를 사용하는 방법

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

이 페이지에서

요약

ADO(ActiveX Data Objects) 개발자는 ADOX(ActiveX Data Objects Extensions for Data Definition Language and Security)를 통해 DAO(Data Access Objects)에 있는 기능을 사용하여 데이터베이스, 테이블, 필드 및 다른 데이터베이스 개체를 프로그래밍 방식으로 변경할 수 있습니다. Jet 엔진에서 Microsoft Access 이외의 데스크톱 데이터베이스와 함께 사용하는 ISAM 드라이버를 지원하는 Microsoft OLE DB Provider for Jet 버전 4.0이 출시된 이후 ADO 개발자는 Microsoft Excel 통합 문서와 워크시트에서 ADO와 ADOX를 모두 사용할 수 있습니다.

그러나 Excel 워크시트가 일반 데이터베이스가 아니기 때문에 ADO가 Excel에서 사용되는 경우 제한이 있습니다. 특히 많은 ADOX 메서드가 Excel에서 사용될 때 작동하지 않거나 예기치 않은 결과를 생성합니다. 이 문서에서는 ADOX와 Excel의 다양한 제한에 대해 설명합니다. Excel에서 ADO를 사용하는 방법에 대한 자세한 내용은 Microsoft 기술 자료의 다음 문서를 참조하십시오.
257819 Visual Basic 또는 VBA에서 Excel 데이터에 ADO를 사용하는 방법
이 문서는 다음과 같은 내용으로 구성되어 있습니다.

참고: 이 문서에 대한 테스트는 Microsoft Data Access Components(MDAC) 2.6 SP1, Microsoft Visual Basic 6 SP5 및 Microsoft Excel 2002(XP)가 설치된 Microsoft Windows 2000 Server 서비스 팩 2(SP2)에서 수행되었으므로 Windows, MDAC, Visual Basic 또는 Excel의 버전 차이로 인해 발생할 수 있는 동작의 차이를 인정하거나 논의하지 않을 수 있습니다.

추가 정보

ADOX 카탈로그

ADOX 개체 모델에서 Catalog 개체는 데이터베이스(Excel의 경우 통합 문서)를 나타냅니다.
  • ADO Connection 개체나 연결 문자열을 사용하여 Catalog 개체를 만들거나 열 수 있습니다. 다음 코드 예제에서는 Book1.xls를 ADOX Catalog로 여는 방법을 보여 줍니다.
    Dim cat As ADOX.Catalog
    Set cat = New ADOX.Catalog
    cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & App.Path & "\book1.xls;Extended Properties=Excel 8.0"
    					
  • Catalog 개체에는 Create 메서드가 있지만 Delete 메서드는 없습니다.

카탈로그 만들기

ADOX Catalog 개체의 Create 메서드를 사용하여 새로운 Excel 통합 문서를 만들 수 없습니다. 이렇게 하려고 하면 다음과 같은 오류 메시지가 나타납니다.
이 개체 형식에서는 지원되지 않는 작업입니다.
그러나 아래에서 설명하는 방법을 통해 카탈로그에 새로운 통합 문서 파일 이름을 지정하고 하나 이상의 테이블을 새 카탈로그에 추가하여 새로운 Excel 통합 문서를 만들 수 있습니다. 이 방법은 ADOX 테이블로 만든 워크시트로만 새 통합 문서를 만듭니다. 즉, Excel 옵션 대화 상자를 통해 새로운 Excel 통합 문서에 지정된 빈 시트의 기본 수가 추가되지 않습니다.

ADOX 테이블

ADOX Table 개체는 데이터의 컨테이너 역할을 할 수 있는 Excel 개체인 워크시트나 범위에 해당합니다.

테이블 만들기

ADOX를 사용하여 Excel에서 새 테이블을 만들 수 있습니다. 열도 만들어야 테이블을 만들 수 있으므로 코드 예제는 "ADOX 열" 절에 있습니다.
  • 새로운 ADOX 테이블을 만들면 새 워크시트와 지정된 이름의 새 워크시트에 새로운 명명된 범위가 만들어집니다. 새로운 명명된 범위는 열 머리글만 포함합니다. 예를 들어, 열이 두 개인 MyTable이라는 테이블(워크시트)을 만드는 경우 MyTable!$A$1:$B$1로 정의된 명명된 범위 MyTable도 만들어집니다.
  • 공백을 포함하는 테이블 이름을 지정할 수 있지만 ADOX는 사용자가 만드는 워크시트 및 범위 이름에서 공백을 밑줄로 대체합니다.
  • Excel 옵션에서 새 통합 문서에 지정된 빈 시트의 기본 수 뒤에 새 워크시트가 통합 문서의 마지막 시트로 만들어집니다.
  • 통합 문서가 Excel에서 열릴 때 새 테이블을 만들려고 하면 다음과 같은 오류 메시지가 나타납니다.
    지정되지 않은 오류입니다.
  • 열이 정의되지 않은 테이블을 만들려고 하면 성공한 것처럼 보이지만 사실 아무 것도 만들어지지 않습니다.
  • 기존 테이블의 이름을 변경하려고 하면 다음과 같은 오류 메시지가 나타납니다.
    이 개체 형식에서는 지원되지 않는 작업입니다.

테이블 삭제

ADOX를 사용하여 Excel에서 테이블(워크시트)이나 명명된 범위를 삭제할 수 없습니다. 이는 ADOX를 사용하여 테이블(워크시트)이나 명명된 범위를 만든 경우에도 마찬가지입니다.
  • 워크시트(MyTable$)를 삭제하려고 하면 오류 없이 성공한 것처럼 보이지만 워크시트가 통합 문서에 남아 있습니다. Delete 메서드를 오류 없이 반복적으로 호출할 수 있지만 아무 효과가 없습니다.
  • 범위(MyTable)를 삭제하려고 하면 오류 없이 성공한 것처럼 보이지만 범위가 통합 문서에 남아 있습니다. 그러나 범위를 두 번째로 삭제하려고 하면 다음과 같은 오류 3265가 나타납니다.
    요청한 이름 또는 서수에 해당하는 컬렉션에서 항목을 찾을 수 없습니다.
  • 명명된 범위를 삭제하려고 하면 명명된 범위 정의가 그대로 유지되지만 범위의 데이터는 모두 삭제됩니다.

ADOX 열

ADOX 열은 데이터베이스의 열이나 필드에 해당하며 Excel 워크시트의 열에 해당합니다.

Excel에서 사용되는 ADO 데이터 형식

ADO는 Excel 데이터 원본에서 열을 만드는 데 사용할 수 있는 6가지 데이터 형식을 인식합니다.

  • adDouble, 형식 5
  • adDate, 형식 7
  • adCurrency, 형식 6
  • adBoolean, 형식 11
  • adVarWChar, 형식 202
  • adLongVarWChar("메모"), 형식 203

열 개요

  • 이전에 만든 테이블에 데이터의 행이 포함되지 않은 경우 추가 열을 해당 테이블에 추가할 수 있습니다.
  • 공백이 포함된 열 이름을 지정할 수 있습니다.
  • ADOX는 항상 작은 따옴표(')가 앞에 오는 열 머리글을 만듭니다(예: 'F1, 'Col1, 'Col2). 그러나 이름이 작은 따옴표 없이 검색되기 때문에 나중에 문제를 발생시키지는 않습니다.

테이블을 만들 때 열 만들기

ADOX를 사용하여 새 테이블을 만들 때 열을 함께 만드는 것이 가장 좋습니다.

다음 코드에서는 두 열(숫자 열 및 텍스트 열)이 포함된 새 테이블(워크시트)을 만듭니다.
    Dim cat As ADOX.Catalog
    Dim tbl As ADOX.Table
    Dim col As ADOX.Column
    Set cat = New ADOX.Catalog
    cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
	"Data Source=" & App.Path & "\book1.xls;Extended Properties=Excel 8.0"
    Set tbl = New ADOX.Table
    tbl.Name = "TestTable"
    Set col = New ADOX.Column
    With col
        .Name = "Col1"
        .Type = adDouble
    End With
    tbl.Columns.Append col
    Set col = Nothing
    Set col = New ADOX.Column
    With col
        .Name = "Col2"
        .Type = adVarWChar
    End With
    tbl.Columns.Append col
    cat.Tables.Append tbl
				

기존 워크시트에 열 만들기

기존의 빈 워크시트에 열을 추가하면 비정상적인 결과가 생성됩니다. 첫 번째 ADOX 열이 테이블의 Columns 컬렉션에 추가되면 ADOX는 먼저 워크시트의 A 열에 F1이라는 열 머리글을 만든 다음 워크시트의 B 열에 새로운 ADOX 열 머리글을 만듭니다. 따라서 새로운 열을 2개 추가하는 ADOX 코드는 열 머리글을 3개 생성합니다. 첫 번째 ADOX 열을 추가하기 전에 이 원하지 않는 첫 번째 열을 Columns 컬렉션에서 삭제하려고 하면 성공한 것처럼 보이지만 실제로는 원하지 않는 열이 만들어집니다.

열 삭제

Excel에서 ADOX 테이블의 열을 삭제할 수 없습니다.
  • 열에 데이터가 없으면 오류 없이 열을 삭제한 것처럼 보이지만 열과 열 머리글이 남아 있습니다.
  • 열에 데이터가 있으면 삭제 작업이 실패하고 다음과 같은 오류 메시지가 나타납니다.
    잘못된 연산입니다.

기존 열의 데이터 형식 변경

열이 테이블에 추가된 후 열의 .Type 속성을 다른 데이터 형식으로 설정하려고 하면 다음과 같은 오류 메시지가 나타납니다.
작업을 이 컨텍스트에서 허용하지 않습니다.

Excel 테이블 및 열에 데이터 추가

Visual Basic 디자인 환경에서 Excel을 사용하여 ADO 코드를 테스트하는 경우 레코드 집합 작업을 처음 실행할 때 다음과 같은 오류 메시지가 나타날 수 있습니다.
선택된 정렬 순서는 이 운영 체제에서 지원되지 않습니다.
이 오류 메시지는 무시해도 됩니다. 응용 프로그램이 컴파일된 후에는 이 오류가 발생하지 않습니다. 자세한 내용은 Microsoft 기술 자료의 다음 문서를 참조하십시오.
246167 PRB: Excel XLS에 대해 ADODB 레코드 집합을 처음으로 열 때 정렬 순서 오류가 발생한다

ADOX로 만든 Excel 열에 데이터 추가

Excel에서 ADOX를 사용하여 만든 테이블과 열에서 ADO를 사용하여 데이터를 삽입하거나 수정하는 경우 ADO는 이 정보가 저장되는 위치가 분명하지 않은 경우에도 해당 열에 지정한 데이터 형식을 반영합니다.
  • 모든 텍스트 문자열 값은 작은 따옴표 표시가 앞에 추가되어 저장됩니다. 그러나 이러한 값이 작은 따옴표 없이 검색되기 때문에 나중에 문제를 발생시키지는 않습니다.
  • 숫자로 정의된 열에 텍스트 문자열을 삽입하려고 하거나 숫자를 텍스트 열에 삽입하려고 하면 다음과 같은 오류 80040e21이 나타납니다.
    여러 단계 작업을 하는 동안 오류가 발생했습니다. 각 상태 값을 확인하십시오.

일반 Excel 열에 데이터 추가

ADOX를 사용하여 만들거나 수정하지 않은 Excel 워크시트나 범위에서 ADO를 사용하여 데이터를 삽입하거나 수정하는 경우 삽입할 수 있는 데이터 형식에 대한 제한이 적용되지 않습니다.
  • 텍스트 문자열은 작은 따옴표 표시가 앞에 추가되지 않고 저장됩니다.
  • 숫자는 왼쪽 맞춤되어 표시됩니다. Excel 2002에서 숫자에는 "숫자를 텍스트로 저장"을 경고하는 스마트 태그로 플래그가 지정됩니다. 이는 특히 나중에 데이터가 수치 분석을 위해 Excel에 저장된 경우 데이터로 작업할 때 문제를 발생시킬 수 있습니다.

SQL CREATE, ALTER 및 DROP 문 사용

ADO Connection을 통해 Excel 데이터베이스 개체에 대해 CREATE, ALTER 및 DROP과 같은 Microsoft SQL DDL(데이터 정의 언어) 문을 실행하여 위에서 설명한 해당 ADOX 메서드와 동일한 결과를 생성할 수 있습니다.

결론

  • ADOX는 새 테이블과 열을 모두 동시에 만드는 경우 Excel에서 가장 효과적으로 작동합니다.
  • ADO는 ADOX를 사용하여 만든 워크시트에 데이터를 저장하는 경우 Excel 데이터와 가장 효과적으로 작동합니다.

속성

기술 자료: 303814 - 마지막 검토: 2008년 4월 11일 금요일 - 수정: 2.1
본 문서의 정보는 다음의 제품에 적용됩니다.
  • Microsoft Data Access Components 2.0
  • Microsoft Data Access Components 2.1
  • Microsoft Data Access Components 2.5
  • Microsoft Data Access Components 2.6
  • Microsoft Data Access Components 2.7
키워드:?
kbdatabase kbhowto kbiisam KB303814

피드백 보내기

 

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