Excel & VBA---Brain Training

닫혀있는 엑셀시트를 DB로 처리하여 값참조하기

닫혀있는 시트의 테이블의 내용을 화일을 열지 않고 가져 오고 싶은 경우가 많다
화일을 열어서 처리하고 화일을 닫아도 좋겠지만
닫혀있는 화일을 DB로 처리하고 싶은 경우도 많을 것이고..
실은 엑셀은 본래 DB에서 필요한 자료만 조건에 맞게 불러다가
분석을 하거나 보고서를 작성하는 것이 잘 사용하는 방법이다
DB로 처리하는 방법으로는 DB엔진을 외부에서 참조하여야한다는 점이 있다
DB엔진으로는 억세스의 본래의 엔진은 DAO라고 하는 것이 있고
억세스뿐만 아니라 모든 DB를 범용으로 처리하는 ADO라고 하는 것이 있고
억세스 accdb확장자로 끝나는 엔진의 경우는 Access DataBase Engine 라이브러리를
활용하는 경우도 있다
가장 확장성이 좋은 엔진은 ADO엔진이니..
이것으로 처리하는 것을 이 코너에서는 하도록 하자

항상 하듯이 DB로 사용할 데이타화일과 폴더를 만들자
아래의 구문을 실행하면

Const FolderName As String = "DATAS"
Const DataFileName As String = "Datas.xls"
Const DataSheetName As String = "Datas"

Sub makeDataFileAndFolder()
Dim sFolder As String
If Dir(ThisWorkbook.Path & "\" & FolderName, vbDirectory) = "" Then
    VBA.FileSystem.MkDir ThisWorkbook.Path & "\" & FolderName
    GoTo MAKE_FILE
Else
    If Dir(ThisWorkbook.Path & "\" & FolderName & "\" & DataFileName) = "" Then
MAKE_FILE:
        Dim oFile As Workbook
        Set oFile = getSampleDataFile(DataSheetName, DataFileName)
        oFile.SaveAs Filename:=ThisWorkbook.Path & "\" & FolderName & "\" & DataFileName, FileFormat:=Excel.XlFileFormat.xlWorkbookNormal
        oFile.Close
    Else
        MsgBox "이미 데이타화일이 있습니다 , 다음 ADO작업을 하세요"
        Exit Sub
    End If
End If
MsgBox "폴더와 화일이 만들어졌습니다, 다음 ADO 작업을 하세요"
End Sub

Function getSampleDataFile(sShtName As String, sFileName As String)
Dim oBook As Workbook
Set oBook = Workbooks.Add
On Error Resume Next
Dim shtX As Worksheet
Application.DisplayAlerts = False
For Each shtX In oBook.Worksheets
    shtX.Delete
Next
Application.DisplayAlerts = True
Set shtX = oBook.Worksheets(1)
Dim iRow As Integer
With shtX
    .Name = sShtName
    .Range("A1").Resize(, 7) = _
        Array("일자", "상품", "담당자", "분류", "단가", "판매수량", "금액")
    For iRow = 2 To 1000
        .Range("A" & iRow).Resize(, 7) = _
                Array(DateSerial(2014, Int(Rnd() * 12) + 1, _
                Int(Rnd() * 30) + 1), _
                String(3, Chr(Int(Rnd() * 10) + 65)), _
                Choose(Int(Rnd() * 9) + 1, "김", "이", "박", "최", "정", "강", "조", "윤", "장"), _
                (Chr(Int(Rnd() * 5) + 65)), _
                Application.Round(Int(Rnd() * 10000) + 10000, -2), _
                Int(Rnd() * 100) + 10, _
                 "=RC[-2]*RC[-1]")
    Next
    .UsedRange.Value = .UsedRange.Value
    .UsedRange.Columns.AutoFit
End With
Set getSampleDataFile = oBook
End Function

실행하는 문서(저장된 상태의 문서여야 한다)가 있는 폴더에
"Datas"라는 폴더가 만들어지고
"Datas"폴더내에 Datas.xls 화일이 만들어지고
Datas.xls화일에는 시트가 한장밖에 없고 이시트에 가상의 정보테이블이
만들어질 것이다
DB데이타로 활용할 화일은 시트가 한장만 있는 것이 좋다
그리고 테이블은 시작행,시작열에 빈열이나 빈행이 없는 것이 좋다
그렇지 않으면 DB테이블로 인식하는데 트러블이 많다
위의 가상의 정보테이블로 다양한 작업을 해 볼수 있을 것이다
위의 가상의 테이블은 질문하신분의 테이블과는 내용이 많이 다르지만
원리는 같은 것이니 충분히 응용하여 하시고자 하는 답을 만들수 있을 것이다
몇개의 화일에 걸쳐서 ADO활용 쌤플의 중요한 것을 만들어 보자

위의 쌤플로 만들어진 데이타는 아래의 그림과 같다
말이 안되는 데이타지만 쌤플이니까..



담당자별로 매출합계를 뽑아 보고 싶다..
만약 열려있는 시트라면 워크시트함수 SUMIF정도 돌리면 된다..
그렇다 닫혀있는 화일을 DB로 읽어 온다는 개념은 화일을 램메모리에
올리지 않고 하드상에 있는 것을 읽어 오는 것
그러니 화일의 부담도 적고, 가볍게 돌아가는 것이 되는 것

DB에 연결하고 하는 작업은 간단하지만
SQL문을 작성하는 것이 일일 것이다
실은 몇개 안되는 명령문으로 구성된 것인데..이것을 두들길줄 알아야
프로그래머가 있어 보인다...
그것을 해보는 것이다



작업하는 일은..
1)ADO라이브러리를 VBA편집기내의 도구/참조메뉴를 통하여 참조시킨다
2)그러면 Connection개체나 Recordset이라는 개체를 사용할수 있게 된다
3)DB와 전화선을 연결하는 역할인 Connect개체와
4)DB에서 얻어온 정보를 임시 담아두는 역할을 하는 메모리상의 테이블 Recordset 개체
   Dim oRST As New Recordset
   Dim oCon As New Connection
5)생성된 두개의 개체중 Connect개체에 필요한 정보를 준다
   sCon = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
   "Data Source=" & ThisWorkbook.Path & "\" & FolderName & "\" & DataFileName & _
   ";Extended Properties=""Excel 8.0;HDR=Yes;"";"
6)연결이 되었으면 어떤 정보를 달라는 요청을 SQL문으로 작성하여 전달하면 된다
   sSQL = "SELECT 담당자,SUM(금액) FROM [Datas$] GROUP BY 담당자;"
   oCon.Open sCon
   oRST.Open sSQL, oCon, adOpenStatic, adLockOptimistic, adCmdText
7)이렇게 Recordset에 담긴 정보를 Range개체의
8)CopyFromRecordset 메소드로 범위에 부어주면 된다

나머지는 정해진,기계적인 것이고..
여러분의 생각이 반영되는 것은 위의 빨강색의 SQL문의 작성인 것이다