Com2meNet

개발 도중에는 쿼리를 검증하거나 중간결과를 확인하기가 좀 불편한 경우가 있습니다.

간단한 데이터들을 간편하게 가공할때는 엑셀이 참 편리하죠.

엑셀로 쿼리를 날려 결과물을 시트에 채워넣는 방법으로 DB를 조회할 수 있다면 참 편하곘죠.

그렇다고 거창하게 VSTO나 VBA로 코딩을 하면 오히려 일이 커집니다.

간편하게 사용자 함수를 정의해서 쿼리문을 인수로 넣고 결과물을 배열로 반환 받는다면 어떨까요?

자 티스토리 오픈기념으로 저만의 노하우를 하나 공개합니다.

분량에 맞게 미리 영역을 지정해 놓고 입력은 배열수식형태로 해야합니다.
(즉 함수식을 입력하고 ctrl + shift + Enter 를 누르셔야 합니다.)

예를 들면 =req_sql("SELECT * FROM table") 이런 식으로 활용하시면 됩니다.

제가 만든 req_sql() 사용자정의 함수의 코드는 아래와 같습니다.

컨넥션 스트링만 DB종류 및 계정에 맞게 설정해서 사용하시면 됩니다.
 (저는 ODBC를 통해 Mysql서버로 연결해봤습니다.)

Function req_sql(sql As String)

    '================= DB연결 및 Recordset정의 =======================
    Dim db_con As ADODB.Connection
    Set db_con = New ADODB.Connection
    Dim str_con As String
    str_con = "DSN=mydb;UID=root;PWD=admin;"
    db_con.Open str_con
    
    Dim adoRS As ADODB.Recordset
    Set adoRS = New ADODB.Recordset
    Set adoRS = db_con.Execute(sql)
    
    '================= Index변수 및 배열정의 =======================
    Dim r, c As Integer
    Dim r_cnt, c_cnt As Integer
    c_cnt = adoRS.Fields.Count
    adoRS.MoveFirst
    Do While adoRS.EOF = False
        r_cnt = r_cnt + 1
        adoRS.MoveNext
    Loop
    
    Dim var() As Variant
    ReDim var(0 To r_cnt, 0 To c_cnt - 1)
    
    '================= 배열에 필드명 및 데이터 채우기 =======================
    For c = 0 To c_cnt - 1
        var(0, c) = adoRS.Fields.Item(c).Name
    Next c
    
    adoRS.MoveFirst
    For r = 1 To r_cnt '행단위탐색
        For c = 0 To c_cnt - 1 '열단위 탐색
            If (IsError(adoRS(c).Value) = False) Then
                var(r, c) = adoRS(c).Value '배열 요소에 데이터 입력
            End If
        Next c
        adoRS.MoveNext
    Next r
    
    '================= 결과물 반환 =======================
    req_sql = var
    
    '================= DB연결 및 Recordset 반환 =======================
    adoRS.Close
    db_con.Close
    Set adoRS = Nothing
    Set db_con = Nothing
    
End Function