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

http://support.microsoft.com/kb/257819

기술 자료: 257819 - 마지막 검토: 2007년 12월 26일 수요일 - 수정: 4.4

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

이 문서가 적용되는 제품 보기.

이 페이지에서

모두 확대 | 모두 축소

요약

이 문서에서는 Microsoft Excel 스프레드시트에서 데이터 원본으로 ADO(ActiveX Data Objects)를 사용하는 방법을 설명하고 Excel 관련 구문 문제와 제한 사항에 대해서도 중점적으로 설명합니다. 이 문서에서는 OLAP 또는 PivotTable 기술이나 그 밖의 특별한 Excel 데이터 사용에 대한 내용은 다루지 않습니다.

자세한 내용은 Microsoft 기술 자료의 다음 문서를 참조하십시오.

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

위로 가기

추가 정보

소개

Microsoft Excel 스프레드시트의 행 및 열은 데이터베이스 테이블의 행 및 열과 매우 유사합니다. Microsoft Excel이 관계형 데이터베이스 관리 시스템이 아니고 이러한 사실에 따른 제한 사항을 알고 있으면 대개 Excel과 해당 도구를 사용하여 데이터를 저장하고 분석하는 것이 좋습니다.

Microsoft ActiveX Data Objects를 사용하면 Excel 통합 문서를 데이터베이스처럼 처리할 수 있습니다. 이 작업을 수행하는 방법은 다음과 같은 절에 설명되어 있습니다.

• ADO를 사용하여 Excel에 연결
• ADO를 사용하여 Excel 데이터 검색 및 편집
• Excel에서 데이터 원본 구조(메타데이터) 검색

참고: 이 문서는 Visual Basic 6.0 서비스 팩 3 및 Excel 2000이 설치된 Microsoft Windows 2000에서 Microsoft Data Access Components(MDAC) 2.5를 사용하여 테스트되었으므로 MDAC, Microsoft Windows, Visual Basic 또는 Excel의 버전 차이로 인해 발생할 수 있는 동작 차이를 인정하거나 다룰 수 없습니다.

위로 가기

ADO를 사용하여 Excel에 연결

ADO는 MDAC에 포함된 다음 두 OLE DB 공급자 중 하나를 사용하여 Excel 데이터 파일에 연결할 수 있습니다.

• Microsoft Jet OLE DB Provider
  
  
• Microsoft OLE DB Provider for ODBC Drivers

Microsoft Jet OLE DB Provider를 사용하는 방법

Jet 공급자를 사용하여 Excel 데이터 원본에 연결하려면 경로(파일 이름 포함)와 Excel 파일 버전에 대한 정보가 필요합니다.

연결 문자열을 사용하는 Jet 공급자

Dim cn as ADODB.Connection
Set cn = New ADODB.Connection
With cn
	.Provider = "Microsoft.Jet.OLEDB.4.0"
	.ConnectionString = "Data Source=C:\MyFolder\MyWorkbook.xls;" & _
"Extended Properties=Excel 8.0;"
	.Open
End With
				

공급자 버전: Jet 3.51 공급자는 Jet ISAM 드라이버를 지원하지 않기 때문에 Jet 4.0 공급자를 사용해야 합니다. Jet 3.51 공급자를 지정하면 런타임에 다음과 같은 오류 메시지가 나타납니다.

설치 가능한 ISAM을 찾을 수 없습니다.

Excel 버전: Excel 95 통합 문서(Excel 7.0 버전)에 대해서는 Excel 5.0을 지정하고 Excel 97, Excel 2000 또는 Excel 2002(XP) 통합 문서(Excel 8.0, 9.0 및 10.0 버전)에 대해서는 Excel 8.0을 지정합니다.

데이터 연결 속성 대화 상자를 사용하는 Jet 공급자

응용 프로그램에서 ADO Data 컨트롤이나 Data Environment를 사용하는 경우 필요한 연결 문자열을 수집하는 데이터 연결 속성 대화 상자가 나타납니다.

1. 공급자 탭에서 Jet 4.0 공급자를 선택합니다. Jet 3.51 공급자는 Jet ISAM 드라이버를 지원하지 않습니다. Jet 3.51 공급자를 지정하면 런타임에 다음과 같은 오류 메시지가 나타납니다.
   설치 가능한 ISAM을 찾을 수 없습니다.
2. 연결 탭에서 통합 문서 파일을 찾습니다. "사용자 ID" 및 "암호" 항목은 Excel 연결에 적용되지 않으므로 무시합니다. 암호로 보호된 Excel 파일은 데이터 원본으로 열 수 없습니다. 이에 대한 자세한 내용은 이 문서의 뒷부분에 나와 있습니다.
3. 모두 탭의 목록에서 확장 속성을 선택한 다음 값 편집을 누릅니다. 세미콜론(;)으로 기존의 다른 항목과 구별되도록 Excel 8.0;을 입력합니다. 이 단계를 생략하면 연결을 테스트할 때 오류 메시지가 나타납니다. 이는 별도로 지정하지 않는 한 Jet 공급자가 대상을 Microsoft Access 데이터베이스로 간주하기 때문입니다.
4. 연결 탭으로 돌아가서 연결 테스트를 누릅니다. 프로세스가 성공했다는 내용의 메시지 상자가 나타납니다.

기타 Jet 공급자 연결 설정

열 머리글: 기본적으로 Excel 데이터 원본의 첫 번째 행에는 필드 이름으로 사용할 수 있는 열 머리글이 포함되어야 합니다. 그렇지 않을 경우 이 설정을 해제해야 합니다. 그렇지 않으면 필드 이름으로 사용하기 위해 첫 번째 데이터 행이 "사라집니다". 이 설정을 해제하려면 선택적 HDR= 설정을 연결 문자열의 확장 속성으로 설정하면 됩니다. 지정하지 않아도 되는 기본값은 HDR=Yes입니다. 열 머리글이 없으면 HDR=No를 지정해야 합니다. 그러면 필드 이름이 F1, F2 등으로 지정됩니다.확장 속성 문자열에 여러 개의 값이 포함되어 있을 경우 다음 예제와 같이 큰따옴표로 묶고 다시 한 쌍의 따옴표로 묶어 Visual Basic이 첫 번째 따옴표 쌍을 리터럴 값으로 처리하도록 해야 합니다. 여기에는 읽기 편리하도록 별도의 공백이 추가되었습니다.

.ConnectionString = "Data Source=C:\MyFolder\MyWorkbook.xls;" & _
"Extended Properties=" " Excel 8.0; HDR=No;" " "
				

Microsoft OLE DB Provider for ODBC Drivers 사용

ODBC 드라이버용 공급자(이 문서에서는 줄여서 "ODBC 공급자"라고 함)를 사용하여 Excel 데이터 원본에 연결하려는 경우에도 드라이버 이름과 통합 문서 경로(파일 이름 포함)에 대한 정보가 필요합니다.

중요: Excel에 대한 ODBC 연결은 기본적으로 읽기 전용입니다. 따라서 이 연결 수준 설정은 ADO 레코드 집합 LockType 속성 설정에 의해 무시되지 않습니다. 데이터를 편집하려면 연결 문자열이나 DSN 구성에서 ReadOnly를 False로 설정해야 합니다. 그렇지 않으면 다음과 같은 오류 메시지가 나타납니다.

이 작업에서는 업데이트할 수 있는 쿼리를 사용해야 합니다.

DSN이 없는 연결 문자열을 사용하는 ODBC 공급자

Dim cn as ADODB.Connection
Set cn = New ADODB.Connection
With cn
	.Provider = "MSDASQL"
	.ConnectionString = "Driver={Microsoft Excel Driver (*.xls)};" & _
"DBQ=C:\MyFolder\MyWorkbook.xls; ReadOnly=False;"
	.Open
End With
				

DSN이 포함된 연결 문자열을 사용하는 ODBC 공급자

Dim cn as ADODB.Connection
Set cn = New ADODB.Connection
With cn
	.Provider = "MSDASQL"
	.ConnectionString = "DSN=MyExcelDSN;"
	.Open
End With
				

데이터 연결 속성 대화 상자를 사용하는 ODBC 공급자

응용 프로그램에서 ADO Data 컨트롤이나 Data Environment를 사용하는 경우 필요한 연결 문자열을 수집하는 데이터 연결 속성 대화 상자가 나타납니다.

1. 공급자 탭에서 Microsoft OLE DB Provider for ODBC Drivers를 선택합니다.
2. 연결 탭에서 사용할 기존 DSN을 선택하거나 연결 문자열 사용을 선택합니다. 그러면 필요한 연결 문자열을 수집하는 표준 DSN 구성 대화 상자가 나타납니다. 필요한 경우 앞에서 설명한 것처럼 기본 읽기 전용 설정의 선택을 취소해야 합니다.
3. 연결 탭으로 돌아가서 연결 테스트를 누릅니다. 프로세스가 성공했다는 내용의 메시지 상자가 나타납니다.

기타 ODBC 공급자 연결 설정

열 머리글: 기본적으로 Excel 데이터 원본의 첫 번째 행에는 필드 이름으로 사용할 수 있는 열 머리글이 포함되어야 합니다. 그렇지 않을 경우 이 설정을 해제해야 합니다. 그렇지 않으면 필드 이름으로 사용하기 위해 첫 번째 데이터 행이 "사라집니다". 이 설정을 해제하려면 선택적FirstRowHasNames= 설정을 연결 문자열로 설정하면 됩니다. 지정하지 않아도 되는 기본값은FirstRowHasNames=1입니다. 여기서 1은 True를 나타냅니다. 열 머리글이 없으면FirstRowHasNames=0을 지정해야 합니다(여기서 0은 False를 나타냄). 그러면 필드 이름이 F1, F2 등으로 지정됩니다. DSN 구성 대화 상자에서는 이 옵션을 사용할 수 없습니다.

그러나 ODBC 드라이버의 버그로 인해 현재 FirstRowHasNames 설정을 지정해도 아무런 효과가 없습니다. 즉, Excel ODBC 드라이버(MDAC 2.1 이상)가 지정된 데이터 원본의 첫 번째 행을 항상 필드 이름으로 처리합니다. 열 머리글 버그에 대한 자세한 내용은 Microsoft 기술 자료의 다음 문서를 참조하십시오.

288343  BUG: Excel ODBC 드라이버가 FirstRowHasNames 또는 헤더 설정을 무시한다

검색할 행: Excel은 관계형 데이터베이스와 마찬가지로 ADO에 포함된 데이터에 대한 자세한 스키마 정보를 해당 ADO에 제공하지 않습니다. 따라서 드라이버는 각 열의 데이터 형식을 추측하기 위해 적어도 몇 개의 기존 데이터 행을 검색합니다. "검색할 행"의 기본값은 8입니다. 1에서 16 사이의 정수 값을 지정하거나 0을 지정하여 기존 행을 모두 검색할 수 있습니다. 이 작업은 연결 문자열에 선택적 MaxScanRows= 설정을 추가하거나 DSN 구성 대화 상자에서 검색할 행 설정을 변경하여 수행합니다. 

그러나 ODBC 드라이버의 버그로 인해 현재 검색할 행(MaxScanRows) 설정을 지정해도 아무런 효과가 없습니다. 즉, Excel ODBC 드라이버(MDAC 2.1 이상)가 각 열의 데이터 형식을 확인하기 위해 지정된 데이터 원본의 첫 8행을 검색합니다. 

검색할 행 버그와 이 버그를 해결하는 간단한 방법에 대한 자세한 내용은 Microsoft 기술 자료의 다음 문서를 참조하십시오.

189897  Excel ODBC 드라이버를 사용하면 데이터가 255자로 잘린다

기타 설정: 데이터 연결 속성 대화 상자를 사용하여 연결 문자열을 구성하는 경우 다음과 같이 꼭 필요하지 않은 일부 확장 속성 설정이 연결 문자열에 추가될 수 있습니다.

... DefaultDir=C:\WorkbookPath;DriverId=790;FIL=excel 8.0;MaxBufferSize=2048;PageTimeout=5;
				

Visual Basic Editor의 "정렬 순서" 오류 메시지

특정 버전의 MDAC를 사용하는 Visual Basic 디자인 환경에서 디자인 타임에 프로그램이 Excel 데이터 원본에 처음으로 연결할 때 다음과 같은 오류 메시지가 나타날 수 있습니다.

선택된 정렬 순서는 이 운영 체제에서 지원되지 않습니다.

이 메시지는 IDE에서만 나타나고 컴파일된 버전의 프로그램에서는 나타나지 않습니다. 자세한 내용은 Microsoft 기술 자료의 다음 문서를 참조하십시오.

246167  PRB: Excel XLS에 대해 ADODB 레코드 집합을 처음으로 열 때 정렬 순서 오류가 발생한다

두 OLE DB 공급자에 대한 고려 사항

혼합된 데이터 형식에 대한 주의 사항

앞에서 설명한 것처럼 ADO는 Excel 워크시트 또는 범위에 있는 각 열의 데이터 형식을 추측해야 합니다. 이 작업은 Excel 셀 서식 설정의 영향을 받지 않습니다. 한 열에 숫자 값과 텍스트 값이 함께 있으면 심각한 문제가 발생할 수 있습니다. Jet 공급자와 ODBC 공급자 모두 대다수를 차지하는 데이터 형식을 반환하지만 소수의 데이터 형식에 대해서는 NULL(빈) 값을 반환합니다. 한 열에 이 두 형식이 함께 있으면 공급자는 텍스트 값보다 숫자 값을 우선적으로 선택합니다.

예를 들면 다음과 같습니다.

• 8개의 검색된 행의 열에 5개의 숫자 값과 3개의 텍스트 값이 있으면 공급자는 5개의 숫자 값과 3개의 null 값을 반환합니다.
• 8개의 검색된 행의 열에 3개의 숫자 값과 5개의 텍스트 값이 있으면 공급자는 3개의 null 값과 5개의 텍스트 값을 반환합니다.
• 8개의 검색된 행의 열에 4개의 숫자 값과 4개의 텍스트 값이 있으면 공급자는 4개의 숫자 값과 4개의 null 값을 반환합니다.

따라서 열에 혼합된 값이 포함되어 있으면 유일한 해결 방법은 숫자 값을 열에 텍스트로 저장하고 필요한 경우 Visual Basic VAL 함수나 이와 동등한 함수를 사용하여 클라이언트 응용 프로그램에서 숫자로 되돌리는 것입니다.

읽기 전용 데이터에 대해 이 문제를 해결하려면 연결 문자열의 확장 속성 구역에서 "IMEX"를 "1"로 설정하여 가져오기 모드를 사용할 수 있도록 설정합니다. 이렇게 하면ImportMixedTypes=Text 레지스트리 설정이 적용됩니다. 그러나 이 모드에서는 업데이트할 때 예기치 않은 결과가 발생할 수 있습니다. 이 설정에 대한 자세한 내용은 Microsoft 기술 자료의 다음 문서를 참조하십시오.

194124  PRB: DAO OpenRecordset을 사용할 때 Excel 값이 NULL로 반환된다

암호로 보호된 통합 문서를 열 수 없다

암호로 보호된 Excel 통합 문서는 Microsoft Excel 응용 프로그램에 이미 열려 있지 않는 한 연결 설정에 올바른 암호를 제공하더라도 데이터 액세스를 위해 열 수 없습니다. 이렇게 하려고 하면 다음과 같은 오류 메시지가 나타납니다.

파일 암호를 해독할 수 없습니다.

자세한 내용은 Microsoft 기술 자료의 다음 문서를 참조하십시오.

211378  XL2000: 암호로 보호된 파일을 열려고 하면 "파일 암호를 해독할 수 없습니다." 오류 메시지가 나타난다

위로 가기

ADO를 사용하여 Excel 데이터 검색 및 편집

이 절에서는 Excel 데이터 작업과 관련된 다음과 같은 두 가지 측면에 대해 설명합니다.

• 데이터를 선택하는 방법
  
  
• 데이터를 변경하는 방법

데이터를 선택하는 방법

다음과 같은 여러 가지 방법으로 Excel 데이터를 선택할 수 있습니다.

• 코드 사용
• ADO Data 컨트롤 사용
• Data Environment 명령 사용

코드를 사용하여 Excel 데이터 선택

Excel 데이터는 다음 중 하나의 통합 문서에 포함될 수 있습니다.

• 전체 워크시트
• 워크시트의 명명된 셀 범위
• 워크시트의 명명되지 않은 셀 범위

워크시트 지정

워크시트를 레코드 원본으로 지정하려면 달러 기호가 뒤에 오고 대괄호로 묶인 워크시트 이름을 사용하십시오. 예를 들면 다음과 같습니다.

	strQuery = "SELECT * FROM [Sheet1$]"
				

또한 키보드에서 물결표(~) 아래에 있는 기울어진 작은따옴표 문자(`)로 워크시트 이름을 구분할 수도 있습니다. 예를 들면 다음과 같습니다.

	strQuery = "SELECT * FROM `Sheet1$`"
				

Microsoft는 문제가 있는 데이터베이스 개체 이름을 나타내는 데 기존 방식인 대괄호를 사용할 것을 권장합니다.

달러 기호와 괄호를 둘 다 생략하거나 달러 기호만 생략하면 다음과 같은 오류 메시지가 나타납니다.

... Jet 데이터베이스 엔진에서 지정한 개체를 찾을 수 없습니다.

달러 기호만 사용하고 괄호를 생략하면 다음과 같은 오류 메시지가 나타납니다.

FROM 절에 구문 오류가 있습니다.

일반적인 작은따옴표를 사용하려고 하면 다음과 같은 오류 메시지가 나타납니다.

쿼리 구문 오류입니다. 쿼리 절이 완전하지 않습니다.

명명된 범위 지정

명명된 셀 범위를 레코드 원본으로 지정하려면 단순히 정의된 이름을 사용하십시오. 예를 들면 다음과 같습니다.

	strQuery = "SELECT * FROM MyRange"
				

명명되지 않은 범위 지정

명명되지 않은 셀 범위를 레코드 원본으로 지정하려면 시트 이름 끝에 표준 Excel 행/열 표시를 대괄호로 묶어 추가하십시오. 예를 들면 다음과 같습니다.

	strQuery = "SELECT * FROM [Sheet1$A1:B10]"
				

워크시트를 지정할 때의 주의 사항: 공급자는 데이터 테이블이 지정된 워크시트의 왼쪽 상단에 있는 비어 있지 않은 셀부터 시작한다고 가정합니다. 즉, 3행의 C열에서는 아무 문제 없이 데이터 테이블을 시작할 수 있지만 셀 A1에 있는 데이터의 위와 왼쪽에는 워크시트 제목을 입력할 수 없습니다.

범위를 지정할 때의 주의 사항: 워크시트를 데이터 원본으로 지정하면 공급자는 공간이 허용되는 한 워크시트에 있는 기존 레코드 아래에 새 레코드를 추가합니다. 명명된 범위나 명명되지 않은 범위를 지정하면 Jet 또한 공간이 허용되는 한 범위에 있는 기존 레코드 아래에 새 레코드를 추가합니다. 그러나 원래 범위를 다시 쿼리하면 결과 레코드 집합에 범위 밖에 새로 추가된 레코드가 포함되지 않습니다.

MDAC 2.5 이전 버전을 사용하는 경우 명명된 범위를 지정하면 정의된 범위 제한을 넘어 새 레코드를 추가할 수 없거나 다음과 같은 오류 메시지가 나타납니다.

명명된 범위를 확장할 수 없습니다.

ADO Data 컨트롤을 사용하여 Excel 데이터 선택

ADODC 속성 대화 상자의 일반 탭에서 Excel 데이터 원본에 대한 연결 설정을 지정한 후에는 레코드 원본 탭을 누릅니다. adCmdText의 CommandType을 선택한 경우에는 명령 텍스트 대화 상자에 앞에서 설명한 구문을 사용하여 SELECT 쿼리를 입력할 수 있습니다. adCmdTable의 CommandType을 선택하고 Jet 공급자를 사용하는 경우 드롭다운 목록에 선택한 통합 문서에서 사용할 수 있는 명명된 범위와 워크시트 이름이 둘 다 표시됩니다. 이 경우 명명된 범위가 먼저 표시됩니다. 

이 대화 상자에서는 워크시트 이름 뒤에 달러 기호가 올바로 추가되지만 필요한 대괄호는 추가되지 않습니다. 따라서 단순히 워크시트 이름을 선택하고 확인을 누르면 나중에 다음과 같은 오류 메시지가 나타납니다.

FROM 절에 구문 오류가 있습니다.

대괄호를 사용하여 워크시트 이름을 수동으로 묶어야 합니다. 이 콤보 상자는 편집할 수 없습니다. ODBC 공급자를 사용하는 경우에는 이 드롭다운 목록에 명명된 범위만 표시됩니다. 그러나 적절한 구분 기호를 사용하여 워크시트 이름을 수동으로 입력할 수 있습니다.

Data Environment 명령을 사용하여 Excel 데이터 선택

Excel 데이터 원본에 대한 Data Environment 연결을 설정한 후 새 명령 개체를 만듭니다. SQL 문의 데이터 원본을 선택한 경우에는 앞에서 설명한 구문을 사용하여 텍스트 상자에 쿼리를 입력할 수 있습니다. 데이터베이스 개체의 데이터 원본을 선택하고 첫 번째 드롭다운 목록에서 테이블을 선택하고 Jet 공급자를 사용하는 경우에는 드롭다운 목록에 선택한 통합 문서에서 사용할 수 있는 명명된 범위와 워크시트 이름이 둘 다 표시됩니다. 이 경우 명명된 범위가 먼저 표시됩니다. 이 위치에서 워크시트 이름을 선택하면 ADO Data 컨트롤을 사용할 때처럼 대괄호를 사용하여 워크시트 이름을 수동으로 묶지 않아도 됩니다. ODBC 공급자를 사용하는 경우에는 이 드롭다운 목록에 명명된 범위만 표시됩니다. 그러나 워크시트 이름을 수동으로 입력할 수 있습니다.

Excel 데이터를 변경하는 방법: 편집, 추가 및 삭제

편집

일반적인 ADO 메서드를 사용하여 Excel 데이터를 편집할 수 있습니다. "=" 기호로 시작하는 Excel 수식이 포함된 Excel 워크시트의 셀에 해당하는 레코드 집합 필드는 읽기 전용이며 편집할 수 없습니다. 연결 문자열에 별도로 지정하지 않는 한 Excel에 대한 ODBC 연결은 기본적으로 읽기 전용입니다. 자세한 내용은 앞에 나와 있는 "ODBC 드라이버용 Microsoft OLE DB 공급자 사용" 절을 참조하십시오.

추가

공간이 허용되는 한에서 Excel 레코드 원본에 레코드를 추가할 수 있습니다. 그러나 처음 지정한 범위 밖에 새 레코드를 추가하면 원래 범위 지정을 다시 쿼리하지 않을 경우 이러한 레코드가 보이지 않습니다. 자세한 내용은 앞에 나와 있는 "범위를 지정할 때의 주의 사항" 절을 참조하십시오.

경우에 따라 ADO 레코드 집합 개체의 AddNew 및 Update 메서드를 사용하여 Excel 테이블에 새 레코드를 추가하면 ADO가 잘못된 Excel 열에 데이터 값을 삽입할 수 있습니다. 자세한 내용은 Microsoft 기술 자료의 다음 문서를 참조하십시오.

314763  FIX: ADO가 잘못된 Excel 열에 데이터를 삽입한다

삭제

관계형 데이터 원본에서 데이터를 삭제할 때보다 Excel 데이터를 삭제할 때 더 많은 제한 사항이 있습니다. 관계형 데이터베이스에서는 "행"이 아무런 의미도 갖지 않으며 "레코드"와 떨어져서 존재하지 않지만 Excel 워크시트에서는 그렇지 않습니다. 필드(셀) 값을 삭제할 수 있지만 다음과 같은 제한 사항이 있습니다.

1. 전체 레코드를 한 번에 삭제할 수 없거나 다음과 같은 오류 메시지가 나타납니다.
   이 ISAM에서는 연결된 테이블의 데이터를 삭제할 수 없습니다.
   개별 필드의 내용을 비우는 방식으로만 레코드를 삭제할 수 있습니다.
2. Excel 수식이 포함된 셀에서 데이터를 삭제할 수 없고 다음과 같은 오류 메시지가 나타납니다.
   작업을 이 컨텍스트에서 허용하지 않습니다.
3. 삭제된 데이터가 있는 빈 스프레드시트 행을 삭제할 수 없고 레코드 집합에 이러한 빈 행에 해당하는 빈 레코드가 계속 표시됩니다.

ADO를 사용하여 Excel 데이터를 편집할 때의 주의 사항: ADO를 사용하여 Excel에 텍스트 데이터를 삽입하면 텍스트 값 앞에 작은따옴표가 표시됩니다. 이로 인해 나중에 새 데이터를 사용하여 작업할 때 문제가 발생할 수 있습니다.

위로 가기

Excel에서 데이터 원본 구조(메타데이터) 검색

ADO를 사용하여 Excel 데이터 원본(테이블 및 필드)의 구조에 대한 데이터를 검색할 수 있습니다. 두 OLE DB 공급자가 적지만 동일한 수의 유용한 정보 필드를 반환하는 경우에도 두 OLE DB 공급자가 반환하는 결과는 약간 다릅니다. 이 메타데이터는 ADO 레코드 집합 개체를 반환하는 ADO 연결개체의 OpenSchema 메서드를 사용하여 검색할 수 있습니다. 또한 이러한 목적을 위해 보다 강력한 Microsoft ActiveX Data Objects Extensions for Data Definition Language and Security(ADOX) 라이브러리를 사용할 수도 있습니다. 그러나 "테이블"이 워크시트 또는 명명된 범위이고 "필드"가 제한된 수의 일반 데이터 형식 중 하나인 Excel 데이터 원본의 경우 이 추가 기능이 유용하지 않습니다.

쿼리 테이블 정보

관계형 데이터베이스에서 사용할 수 있는 다양한 개체(테이블, 뷰, 저장 프로시저 등) 중에서 Excel 데이터 원본만 지정된 통합 문서에 정의되어 있는 워크시트와 명명된 범위로 구성된 테이블을 노출합니다. 명령된 범위는 "테이블"로 처리되고 워크시트는 "시스템 테이블"로 처리되며 이 "table_type" 속성 외에는 검색할 수 있는 유용한 테이블 정보가 많지 않습니다. 다음은 통합 문서에서 사용할 수 있는 테이블 목록을 요청하는 코드입니다.

Set rs = cn.OpenSchema(adSchemaTables)
				

Jet 공급자는 9개의 필드 중 다음 4개의 필드만 채워져 있는 레코드 집합을 반환합니다.

• table_name
• table_type("테이블" 또는 "시스템 테이블")
• date_created
• date_modified

지정된 테이블에 대한 두 데이터 필드에는 항상 동일한 값인 "마지막으로 수정한 날짜"가 표시됩니다. 즉, "date_created"를 신뢰할 수 없습니다. 

ODBC 공급자도 9개의 필드 중 다음 3개의 필드만 채워져 있는 레코드 집합을 반환합니다.

• table_catalog(통합 문서가 있는 폴더)
• table_name
• table_type("테이블" 또는 "시스템 테이블")

ADO 설명서에는 워크시트 목록만 검색할 수 있다고 되어 있습니다. 예를 들어, OpenSchema 메서드에 다음과 같은 추가 기준을 지정할 수 있습니다.

Set rs = cn.OpenSchema(adSchemaTables, Array(Empty, Empty, Empty, "System Table"))
				

그러나 두 공급자 중 하나를 사용하는 MDAC 2.0 이상 버전이 설치된 경우에는 Excel 데이터 원본에서 워크시트 목록만 검색할 수 없습니다.

쿼리 필드 정보

Excel 데이터 원본에 있는 모든 필드(열)은 다음 데이터 형식 중 하나입니다.

• 숫자(ADO 데이터 형식 5, adDouble)
• 통합(ADO 데이터 형식 6, adCurrency)
• 논리 또는 부울(ADO 데이터 형식 11, adBoolean)
• 날짜(ADO 데이터 형식 7, adDate - Jet을 사용하는 경우, ADO 데이터 형식 135, adDBTimestamp - ODBC를 사용하는 경우)
• 텍스트(202, adVarChar, 200, adVarWChar 등과 같은 ADO ad...Char 형식)

숫자 열의 numeric_precision은 항상 Excel의 최대 전체 자릿수인 15로 반환되고 텍스트 열의 character_maximum_length는 항상 Excel 열에서 텍스트를 표시할 수 있는 최대 너비인 255로 반환됩니다. 이 때 255는 최대 길이가 아닙니다. data_type 속성 외에는 구할 수 있는 유용한 필드 정보가 많지 않습니다. 다음은 테이블에서 사용할 수 있는 필드 목록을 요청하는 코드입니다.

Set rs = cn.OpenSchema(adSchemaTables, Array(Empty, Empty, "TableName", Empty))
				

Jet 공급자는 28개의 필드가 포함된 레코드 집합을 반환합니다. 이 중 8개의 필드는 숫자로 채워져 있고 9개의 필드는 텍스트로 채워져 있으며 다음과 같은 유용한 필드가 있습니다.

• table_name
• column_name
• ordinal_position
• data_type

ODBC 공급자는 29개의 필드가 포함된 레코드 집합을 반환합니다. 이 중 10개의 필드는 숫자로 채워져 있고 11개의 필드는 텍스트로 채워져 있습니다. 유용한 필드는 Jet 공급자와 같습니다.

테이블 및 필드와 함께 해당 속성 열거

다음 예제와 같은 Visual Basic 코드를 사용하여 Excel 데이터 원본에 테이블 및 열과 함께 이러한 각 개체에 대한 정보를 저장할 수 있는 필드를 열거할 수 있습니다. 이 예제는 동일한 폼에 있는 Listbox와 List1에 결과를 출력합니다.

Dim cn As ADODB.Connection
Dim rsT As ADODB.Recordset
Dim intTblCnt As Integer, intTblFlds As Integer
Dim strTbl As String
Dim rsC As ADODB.Recordset
Dim intColCnt As Integer, intColFlds As Integer
Dim strCol As String
Dim t As Integer, c As Integer, f As Integer
Set cn = New ADODB.Connection
With cn
	.Provider = "Microsoft.Jet.OLEDB.4.0"
	.ConnectionString = "Data Source=" & App.Path & _
"\ExcelSrc.xls;Extended Properties=Excel 8.0;"
	'.Provider = "MSDASQL"
	'.ConnectionString = "Driver={Microsoft Excel Driver (*.xls)};" & _
"DBQ=" & App.Path & "\ExcelSrc.xls; "
	.CursorLocation = adUseClient
	.Open
End With
Set rsT = cn.OpenSchema(adSchemaTables)
intTblCnt = rsT.RecordCount
intTblFlds = rsT.Fields.Count
List1.AddItem "Tables:	" & intTblCnt
List1.AddItem "--------------------"
For t = 1 To intTblCnt
	strTbl = rsT.Fields("TABLE_NAME").Value
	List1.AddItem vbTab & "Table #" & t & ":	" & strTbl
	List1.AddItem vbTab & "--------------------"
	For f = 0 To intTblFlds - 1
		List1.AddItem vbTab & rsT.Fields(f).Name & _
vbTab & rsT.Fields(f).Value
	Next
	List1.AddItem "--------------------"
	Set rsC = cn.OpenSchema(adSchemaColumns, Array(Empty, Empty, strTbl, Empty))
	intColCnt = rsC.RecordCount
	intColFlds = rsC.Fields.Count
	For c = 1 To intColCnt
		strCol = rsC.Fields("COLUMN_NAME").Value
		List1.AddItem vbTab & vbTab & "Column #" & c & ": " & strCol
		List1.AddItem vbTab & vbTab & "--------------------"
		For f = 0 To intColFlds - 1
			List1.AddItem vbTab & vbTab & rsC.Fields(f).Name & _
vbTab & rsC.Fields(f).Value
		Next
		List1.AddItem vbTab & vbTab & "--------------------"
		rsC.MoveNext
		Next
		rsC.Close
		List1.AddItem "--------------------"
		rsT.MoveNext
Next
rsT.Close
cn.Close
				

Data View 창 사용

Visual Basic Data View 창에서 Excel 데이터 원본에 대한 데이터 링크를 만들면 앞에서 설명했듯이 Data View 창에 프로그래밍 방식으로 검색할 수 있는 동일한 정보가 표시됩니다. 특히, Jet 공급자는 "테이블" 아래에 워크시트와 명명된 범위를 둘 다 표시하는 반면 ODBC 공급자는 명명된 범위만 표시합니다. ODBC 공급자를 사용하고 있고 명명된 범위를 정의하지 않은 경우에는 "테이블" 목록에 아무 것도 표시되지 않습니다.

Excel 제한 사항

Excel을 데이터 원본으로 사용할 경우 Excel 통합 문서 및 워크시트에 대한 내부 제한 사항의 영향을 받습니다. 이러한 제한 사항으로는 다음과 같은 것들이 있지만 이에 국한되지는 않습니다.

• 워크시트 크기: 65,536행 x 256열
• 셀 내용(텍스트): 32,767자
• 통합 문서에 있는 시트: 사용 가능한 메모리에 의해 제한됨
• 통합 문서에 있는 이름: 사용 가능한 메모리에 의해 제한됨