[Excel] 엑셀 데이터베이스 쿼리검색

 

엑셀에 입력된 데이터를 엑셀에서 쿼리를 이용해서 데이터를 추출할 수 있습니다. 

 

데이터베이스 와 엑셀은 유사한 부분이 많이 있습니다.(Excel Query)

 

엑셀의 Column 은 데이터베이스의  Field에 해당합니다. 

엑셀의 Row 는  데이터베이스의  Recordset에 해당합니다. 

엑셀의 Sheet 는 데이터베이스의 Table에 해당합니다.

엑셀의 File은 데이터베이스에 해당합니다. 

 

엑셀파일은 곧 데이터베이스라고 볼 수 있습니다. 

 

엑셀파일을 데이터베이스처럼 쿼리를 이용하여 데이터를 검색할 수 있습니다.

데이터만 사용하려면 *.xlsx로 사용이 가능하지만  엑셀파일내에서 검색을 하려면 매크로파일로 설정을 해야 합니다.

매크로파일로 설정을 하려면 다른이름으로 저장에서 파일형식을 Excel 매크로 사용 통합문서로 저장을 해야 합니다. 

 

 

매크로 수정을 위해서는 [ALT] + [F11] 로 Visual Basice Application(VBA)를 실행시킬 수 있습니다.

 

 

쿼리를 사용하려면  VBA 에서 ADO 기능을 추가해야 합니다. 

 

반응형

 

도구-> 참조->   ActiveX Data Objects 6.1 Library 추가합니다.

 

 

매크로 코드로 아래 같이 작성합니다. 

 

판매리스트에 값을 입력하고 

 

검색에서 Query 를 입력하고 검색하면  결과값이 표시됩니다. 

테이블은 [sheetname$] 으로 입력하시면 됩니다.

엑셀로 간단하게 쿼리테스트를 해볼 수 있습니다.

 

 

 

Sub 검색()

    Dim query As String
    Dim objRecordset As ADODB.Recordset
    Dim objConnection As ADODB.Connection
    
    Set objRecordset = CreateObject("ADODB.Recordset")
    Set objConnection = CreateObject("ADODB.Connection")
    
    objConnection.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
            "Data Source=" & ThisWorkbook.Path & "\" & ActiveWorkbook.Name & ";" & _
            "Extended Properties=""Excel 12.0 Macro;HDR=Yes;"";"
       
       
    query = Sheets("검색").Cells(2, 2)
    
    objRecordset.Open query, objConnection, adOpenStatic, adLockOptimistic, adCmdText
    
    Dim i, j, row As Integer
    
    row = 10
    If objRecordset.State = 1 Then
    
        For i = 0 To objRecordset.Fields.Count - 1
            Sheets("검색").Cells(row, i + 1).Value = objRecordset.Fields.Item(i).Name
        Next i
    
        row = row + 1
        Do Until objRecordset.EOF
            For j = 0 To objRecordset.Fields.Count - 1
                Sheets("검색").Cells(row, j + 1).Value = objRecordset.Fields.Item(j)
            Next j
            row = row + 1
            objRecordset.MoveNext
        Loop
    Else
    End If
    
End Sub

 

샘플프로그램

 

Query Test.xlsm

0.02MB