엑셀 SQL 연동 및 출력 :: SQL_PRINT 명령문 사용법

엑셀에서 SQL SELECT 쿼리를 실행한 후 데이터를 시트에 출력하는 SQL_PRINT 명령문 사용법을 알아봅니다.

홈페이지 » 엑셀 SQL 연동 및 출력 :: SQL_PRINT 명령문 사용법

작성자 :
오빠두엑셀
최종 수정일 : 2022. 05. 26. 17:06
URL 복사
메모 남기기 : (0)

엑셀 SQL 연동 및 출력 :: SQL_PRINT 명령문 사용법 총정리

엑셀 SQL_PRINT 명령문 목차 바로가기
요약

엑셀에서 SQL SELECT 쿼리를 실행한 후, 반환된 데이터를 시트에 출력합니다.

명령문 구문
SQL_PRINT 연결문자열, 테이블이름, 출력셀, [필드목록], [WHERE절],
[JOIN절], [머릿글포함], [열자동맞춤]
사용된 인수 및 변수 알아보기
인수 설명
연결문자열
[String]
OLEDB(ADO.NET)로 연결할 연결문자열입니다.
테이블이름
[String]
SELECT 쿼리로 출력할 테이블 이름입니다.
예) "users"
출력셀
[Range]
쿼리로 반환된 데이터를 출력할 범위의 시작셀입니다.
필드목록
[String, 선택인수]
쿼리로 출력할 필드 목록입니다. 필드가 여러개일 경우 쉼표(,)로 구분하여 작성합니다. 기본값은 모든 필드를 선택합니다.
예) "Field1,Field2,Field3,..."
WHERE절
[String, 선택인수]
쿼리의 WHERE 절을 작성합니다.
예) "id=1"
JOIN절
[String, 선택인수]
쿼리의 JOIN 절을 작성합니다.
예) "INNER JOIN TableB ON TableA.id = TableB.id
머릿글포함
[Boolean, 선택인수]
데이터 출력시 머릿글 포함 여부입니다. 기본값은 True 입니다.
열자동맞춤
[Boolean, 선택인수]
데이터 출력 후, 열 자동맞춤 여부입니다. 기본값은 False 입니다.

예제파일 다운로드

오빠두엑셀의 강의 예제파일은 여러분을 위해 자유롭게 제공하고 있습니다.


상세 설명

SQL_PRINT 함수는 엑셀에서 SQL SELECT 쿼리를 실행한 후, 데이터를 시트에 출력하는 함수입니다.

기본 WHERE, JOIN 절을 지원하며, 필요에 따라 Nested JOIN 형태로 쿼리를 작성할 수도 있습니다. ORDER BY 와 GROUP BY 절은 지원하지 않으며, 필요시 SQL_SELECT_STRING 코드를 적절히 수정하여 ORDER BY, GROUP BY 절을 추가할 수 있습니다.

본 함수의 인수로 테이블이름, 필드목록, JOIN절, WHERE절을 차례대로 작성하면, SQL SELECT 쿼리문이 아래와 같이 작성됩니다. SQL SELECT 쿼리에 대한 자세한 설명은 관련 링크를 참고하세요.

SELECT 필드목록 FROM 테이블이름 JOIN JOIN절 WHERE WHERE절
실전 사용 예제
  1. users 테이블에서 login_id에 abc가 포함된 레코드를 A1셀에 출력하기
    Dim ConnString As String
    ConnString = "Server=database.windows.net,1433;User ID=oppadu;Password=123;"
     
    SQL_PRINT ConnString, "users", Range("A1"), , "login_id LIKE '%abc%'"

엑셀 SQL 연동 및 출력, SQL_PRINT 명령문 동작원리

SQL_PRINT 명령문 전체 코드
Sub SQL_PRINT(Connection As String, Table As String, Rng As Range, Optional Fields As String = "*", _
                        Optional Where As String = "", Optional JoinCondition As String = "", _
                        Optional incl_header As Boolean = True, Optional autofit As Boolean = False)
'###############################################################
'오빠두엑셀 VBA 사용자지정함수 (https://www.oppadu.com)
'▶ SQL_PRINT
'▶ SQL 서버에 연결 후 특정 테이블의 DB를 시트 위로 출력합니다.
'▶ 인수 설명
'_____________Connection              : SQL OLE DB로 연결할 연결문자열(Connection String)을 입력합니다.
'_____________Table                        : 데이터를 불러올 테이블 이름입니다.
'_____________Rng                          : 데이터를 출력할 시작 셀입니다.
'_____________Fields                        : [선택인수] 출력할 필드명입니다. 기본값은 모든 필드를 출력합니다.
'_____________Where                      : [선택인수] 출력할 조건절을 입력합니다.
'_____________JoinCondition           : [선택인수] 여러 테이블을 연결할 경우, JOIN 절을 작성합니다. e.g) "INNER JOIN 테이블B ON 테이블A.필드 = 테이블B.필드"
'_____________incl_header               : [선택인수] 머릿글 출력 여부입니다. 기본값은 TRUE 입니다.
'_____________autofit                       : [선택인수] 열 너비 자동맞춤 여부입니다. 기본값은 FALSE 입니다.
'▶ 사용 예제
'SQL_PRINT "Server=database.windows.net,1433;User ID=oppadu;Password=123;", "테이블명", ActiveSheet.Range("A1")
'■ 사용된 보조명령문
'Get_RS
'SQL_CONN
'SQL_OPEN
'SQL_SELECT_STRING
'###############################################################
Dim RS As Object
Dim vArr As Variant
Dim x As Long: Dim i As Long
 
Set RS = Get_RS(Connection, Table, Fields, Where, JoinCondition)
Rng.CurrentRegion.ClearContents
 
If incl_header = True Then
    For i = 0 To RS.Fields.Count - 1
        Rng.Offset(0, i).Value = RS.Fields(i).Name
    Next
    vArr = Application.Transpose(RS.GetRows)
    On Error GoTo SingleRow:
    If IsNumeric(UBound(vArr, 2)) Then x = UBound(vArr): GoTo NextStep:
SingleRow:
    x = 1
NextStep:
    On Error GoTo 0
    Rng.Offset(1, 0).Resize(x, RS.Fields.Count) = vArr
    If autofit = True Then Rng.CurrentRegion.EntireColumn.autofit
Else
    Rng.CopyFromRecordset RS
End If
 
End Sub
 
Function Get_RS(Connection As String, Table As String, Optional Fields As String = "*", _
                        Optional Where As String = "", Optional JoinCondition As String = "") As Object
'###############################################################
'오빠두엑셀 VBA 사용자지정함수 (https://www.oppadu.com)
'▶ Get_RS
'▶ SQL 서버에 연결 후 특정 테이블의 DB를 RecordSet Object 로 반환합니다.
'▶ 인수 설명
'_____________Connection              : SQL OLE DB로 연결할 연결문자열(Connection String)을 입력합니다.
'_____________Table                        : 데이터를 불러올 테이블 이름입니다.
'_____________Fields                        : [선택인수] 출력할 필드명입니다. 기본값은 모든 필드를 출력합니다.
'_____________Where                      : [선택인수] 출력할 조건절을 입력합니다.
'_____________JoinCondition           : [선택인수] 여러 테이블을 연결할 경우, JOIN 절을 작성합니다. e.g) "INNER JOIN 테이블B ON 테이블A.필드 = 테이블B.필드"
'▶ 사용 예제
'Dim RS As Object
'Set RS = Get_RS("Server=database.windows.net,1433;User ID=oppadu;Password=123;", "테이블명")
'■ 사용된 보조명령문
'SQL_CONN
'SQL_OPEN
'SQL_SELECT_STRING
'###############################################################
Dim DB As Object
Dim RS As Object
On Error GoTo EH_CONN:
Set DB = SQL_CONN(Connection)
Set RS = SQL_OPEN(DB, SQL_SELECT_STRING(Table, Fields, Where, JoinCondition))
 
'Debug.Print SQL_SELECT_STRING(Table, Fields, Where, JoinCondition)
If RS.State = 1 Then
    Set Get_RS = RS
Else
    GoTo EH_CONN:
End If
 
Set RS = Nothing
 
Exit Function
 
EH_CONN:
    MsgBox "쿼리를 실행하는 도중 오류가 발생했습니다." & vbNewLine & _
                    "쿼리 : " & SQL_SELECT_STRING(Table, Fields, Where, JoinCondition) & vbNewLine & _
                    "오류 번호 : " & Err.Number & vbNewLine & "오류 내용 : " & Err.Description, vbInformation
    End
End Function
 
Function SQL_SELECT_STRING(Table As String, Fields As String, Where As String, JoinCondition As String) As String
SQL_SELECT_STRING = "SELECT " & Fields & " FROM " & Table
If JoinCondition <> "" Then SQL_SELECT_STRING = SQL_SELECT_STRING & " " & Trim(JoinCondition)
If Where <> "" Then SQL_SELECT_STRING = SQL_SELECT_STRING & " WHERE " & Where
SQL_SELECT_STRING = SQL_SELECT_STRING & ";"
End Function
 
 
Function SQL_CONN(CONN_STRING As String) As Object
'###############################################################
'오빠두엑셀 VBA 사용자지정함수 (https://www.oppadu.com)
'▶ SQL_CONN
'▶ SQL 연결 문자열로 OLE DB 연결 후 ADODB 커넥션 개체를 반환합니다.
'▶ 인수 설명
'_____________Connection              : SQL OLE DB로 연결할 연결문자열(Connection String)을 입력합니다.
'▶ 사용 예제
'Dim DB As Object
'Set DB = SQL_CONN("Server=database.windows.net,1433;User ID=oppadu;Password=123;")
'###############################################################
 
Dim DB As Object
Set DB = CreateObject("ADODB.Connection")
 
If InStr(1, CONN_STRING, "Provider=", vbTextCompare) = 0 Then CONN_STRING = "Provider=SQLOLEDB;" & CONN_STRING
 
On Error GoTo EH_CONN:
DB.ConnectionTimeout = 3
DB.Open CONN_STRING
 
If DB.State = 1 Then
    Set SQL_CONN = DB
Else
    GoTo EH_CONN
End If
 
Set DB = Nothing
 
Exit Function
 
EH_CONN:
    MsgBox "서버에 연결할 수 없습니다." & vbNewLine & "인터넷 연결 또는 서버 상태를 확인하세요.", vbInformation
    Set DB = Nothing
    End
End Function
 
Function SQL_OPEN(DB As Object, SQL_STRING As String, _
                                Optional CursorType As Integer = 2, Optional LockType As Integer = 3) As Object
'###############################################################
'오빠두엑셀 VBA 사용자지정함수 (https://www.oppadu.com)
'▶ SQL_OPEN
'▶ SQL로 연결된 ADODB 커넥션에서, SQL 쿼리를 실행하여 ADO 레코드셋 개체로 반환합니다.
'▶ 인수 설명
'_____________DB                             : SQL OLE DB로 연결된 ADODB Connection 개체입니다.
'_____________SQL_STRING              : SQL 쿼리문입니다.
'_____________CursorType                : 실행 시 어느 작업을 허용할 지 결정합니다. (기본값: OpenDynamic)
'_____________LockType                   : 실행 시 잠금 유형을 지정합니다. (기본값: LockOptimistic)
'▶ 사용 예제
'Dim DB As Object
'Dim RS As Object
'Set DB = SQL_CONN(ConnectionString)
'Set RS = SQL_OPEN(DB, SQLString)
'###############################################################
' CursorType
' adOpenForwardOnly = 0 <- 읽기만 가능 (기본값)
' adOpenKeyset = 1 <- 업데이트 가능, 편집하는 모든 과정 잠김
' adOpenDynamic = 2 <- 업데이트 가능, 실행하는 순간에만 잠김 (보편적 사용)
' adOpenStatic = 3 <- 단독 실행시에는 잠기지 않고, 여러 레코드 업데이트시에만 잠김
 
' LockType
' adLockReadOnly = 1 <- 읽기만 가능 (기본값)
' adLockPessimistic = 2 <- 강한 잠금. 편집이 끝난 후 바로 잠김
' adLockOptimistic = 3 <- 약한 잠금. 편집하는 과정에 잠김 해제 (보편적 사용)
' adLockBatchOptimistic = 4 <- 가장 약한 잠금. 여러 데이터 동시 업데이트 시 사용
 
Dim RS As Object
Set RS = CreateObject("ADODB.RecordSet")
 
On Error GoTo EH_CONN:
RS.Open SQL_STRING, DB, CursorType, LockType
 
If RS.State = 1 Then
    Set SQL_OPEN = RS
Else
    Set SQL_OPEN = Nothing
End If
 
Set RS = Nothing
 
Exit Function
 
EH_CONN:
    MsgBox "데이터를 호출할 수 없습니다." & vbNewLine & "작성한 쿼리가 올바른지 확인하세요." & _
    vbNewLine & vbNewLine & SQL_STRING, vbInformation
    Set RS = Nothing
    End
End Function
명령문 동작원리 단계별 알아보기
  1. 연결문자열과 테이블이름, WHERE/JOIN절을 참조하여 SQL 서버로부터 데이터를 받아온 후, ADO 개체를 생성합니다.
    Dim RS As Object
    Dim vArr As Variant
    Dim x As Long
     
    Set RS = Get_RS(Connection, Table, Fields, Where, JoinCondition)
  2. 출력할 셀 기준, 연속된 범위를 초기화합니다.
    Rng.CurrentRegion.ClearContents
  3. 머릿글포함이 True일 경우, 첫행에 머릿글 추가 후, 2번째 행부터 데이터를 출력합니다.
    If incl_header = True Then
        For i = 0 To RS.Fields.Count - 1
            Rng.Offset(0, i).Value = RS.Fields(i).Name
        Next
        vArr = Application.Transpose(RS.GetRows)
        On Error GoTo SingleRow:
        If IsNumeric(UBound(vArr, 2)) Then x = UBound(vArr): GoTo NextStep:
    SingleRow:
        x = 1
    NextStep:
        On Error GoTo 0
        Rng.Offset(1, 0).Resize(x, RS.Fields.Count) = vArr
        If autofit = True Then Rng.CurrentRegion.EntireColumn.autofit
  4. 머릿글포함이 False일 경우 데이터를 출력한 후 명령문을 종료합니다.
    Else
        Rng.CopyFromRecordset RS
    End If
0 0 투표
게시글평점
0 댓글
Inline Feedbacks
모든 댓글 보기
0
여러분의 생각을 댓글로 남겨주세요.x