[Excel] 엑셀시트 ODBC로 연결하여 SQL 처리

엑셀도 하나의 좋은 DataBase 이다 .

엑셀파일을 DataBase 에 스키마와 동급이다

엑셀시트는 각각에 해당 테이블과 동급이다

그렇다면 엑셀시트를 query를 사용하여 쓸수 있을까? 쓸수 있다

사이트에서 일하다보면 엑셀로 무수히 많은 엑셀자료를 만들기는 하는데 이걸 참조해서 개발하는 개발자는 너무많은 자료에 치여사는경우가 많다 그래서 생각한것이 하나의 만들어놓은 엑셀에서 관련되어 있는 엑셀을 query를 실행하여 조회하는 메크로를 작성해보았다

전에 Database를 사용하기 위해서는 ODBC드라이버가 있어야된다고 했는데 ODBC드라이버를 확인해보자

 

1) ODBC설치 확인(기본으로 OFFICE를 설치하면 설치된는것을 알수 있다)

2) 참조에 Microsoft ActiveX DataObject 2.8 Library를 추가 : ADODB 객체를 사용하려구 

3) 일단 엑셀을 sql로 조회하기 위한 엑셀을 하나 준비한다

 

 

4) 엑셀을 DataBase처럼 Connection을 실행해서 잘 연결되는지 확인해보자

    특이하게 Provider를 사용하여 쓰고 뒤에 Properties가 더 붙는다

Public dbcon As ADODB.Connection

Public dbcon As ADODB.Connection 

Public Sub ExcelCon()    '=>실행시 문제가 없는지 확인

    Dim strCon  As String '연결String
    Dim driver, dsrc
   
    dsrc = "D:\엑셀DB테스트.xlsx"    '파일명
    
    '엑셀파일을 DataBase처럼 연결
    strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & dsrc & ";Extended Properties=Excel 12.0;"
    Set dbcon = New ADODB.Connection
   
    dbcon.Open strCon      '연결

End Sub

 

5) 연결이 잘되었으면 RecordSet으로 받아 엑셀에 출력을 해보자

Public Sub getExcelEtcSel()

    Dim rs, sql, colCnt, wRow, i
   
    ExcelCon     
    sql = "select * from [Sheet2$] "  '->조건없이 전부 조회
    Debug.Print sql
   
    'Set rs = CreateObject("ADODB.RecordSet")
    Set rs = New ADODB.Recordset
   
    rs.Open sql, dbcon
   
    wRow = ActiveSheet.Range("B4")  '작성시작 row
    colCnt = rs.Fields.Count
    '컬럼에 head를 시작에 달아줌
      For i = 0 To colCnt - 1
          ActiveSheet.Cells(wRow, i + 1) = rs.Fields(i).Name
      Next
      setHeaderStyle wRow     'header 스타일 적용
      wRow = wRow + 1
   
    If rs.EOF = False Then
   

        Do While rs.EOF = False
       
            For i = 0 To colCnt - 1
                ActiveSheet.Cells(wRow, i + 1) = rs(rs.Fields(i).Name)    '레코드셋에 이름으로 매핑하여 표시
            Next
           
            wRow = wRow + 1
            rs.MoveNext
        Loop
    End If

End Sub

 

6) 엑셀에 특정영역중에 query를 사용할경우와 컬럼명을 기술할때 일반 query와 상이함

 sql = "select [컬럼1],[컬럼2] from [Sheet2$A2:G5] "

- 시트전체조회시 [시트명$]

- 시트에일부영역시 [Sheet2$A2:G5] -> sheet2에 A2:G5 영역중 조회

- 컬럼은 위처럼 중괄호를 해야되고 중복된 컬럼명이 있으면 임의로 넘버링을 붙임

 

중복컬럼이 있는경우

조회결과