[엑셀]매크로 시트의 내용을 쿼리로 처리하기

'가족사항이란 시트의 내용 중 사붠번호가 일치하는 것만 선택하여 Sheet1에 표시하기

 

Sub FP_Query()
    Dim sStr As String
    Dim DbCon As ADODB.Connection
    Dim Rs As ADODB.Recordset
    Dim sFile As String
    Dim sSheet As String
    Dim lRow As Long
    Dim lCol As Long
    Dim sCell As String
   
    '파일명
    sFile = ActiveWorkbook.FullName
    '시트명
    sSheet = ActiveSheet.Name
   
    sStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sFile & "; Extended Properties=Excel 8.0;"
    Set DbCon = New ADODB.Connection
    DbCon.Open sStr

    sSheet = "가족사항"
    SSQL = ""
    SSQL = SSQL & vbCrLf & "SELECT * FROM [" & sSheet & "$] A"
    SSQL = SSQL & vbCrLf & "WHERE A.사원번호 = 'A123456"

    Set Rs = New ADODB.Recordset
    lRow = 1
    Sheets("Sheet1").Select
   
    Rs.Open SSQL, DbCon
    Do While True
        If Rs.EOF Then
            Exit Do
        End If
       
        lRow = lRow + 1
        sCell = "A" & lRow
        Range(sCell).Select
        For lCol = 1 To Rs.Fields.Count
            ActiveCell.Offset(0, lCol - 1).Value = Rs.Fields(lCol - 1).Value
        Next
        Rs.MOVENEXT
    Loop
    Rs.Close
   
    DbCon.Close
    Set DbCon = Nothing
End Sub