엑셀과 DB를 연결하는 VBA Source

티스토리 뷰

엑셀과 DB를 연결하는 VBA Source

데타신 2012. 3. 22. 15:34

Sub DB_Call()

Dim IP_set As Variant
Dim User_set As Variant
Dim Pass_set As Variant
Dim DB_set As Variant
Dim DB_type As Variant

Dim Con      As New ADODB.Connection
Dim RS       As New ADODB.Recordset
Dim strCnn   As String
Dim SQL      As String
Dim intRC    As Integer
Dim SHname   As Worksheet
Dim rngSheet As Worksheet

Dim i           As Double
Dim SR          As Double
Dim SHN         As Double

Application.ScreenUpdating = False '화면 갱신을 숨기구

intNum = Worksheets("SQL").UsedRange.Rows.Count '세로행의 갯수

For SR = 1 To (intNum - 1)

     SHN = SR + 1


    Sheet_Name = Worksheets("SQL").Cells(SHN, 1)    ' Sheet Name 위치
    Sheet_SQL = Worksheets("SQL").Cells(SHN, 2)     ' SQL 위치
    Sheet_Use = Worksheets("SQL").Cells(SHN, 3)     ' 사용여부

엑셀2010 매크로 & VBA이종석

상세보기

    If Sheet_Use = "Sheet Create" Then

        For Each rngSheet In Worksheets             ' Sheet 명을 찾아서 지운다

            If rngSheet.Name = Sheet_Name Then

                Application.DisplayAlerts = False   ' Sheet 삭제시 팝업창 제거
                 rngSheet.Delete
                Application.DisplayAlerts = True

            End If


        Next rngSheet

         Worksheets.Add.Name = Sheet_Name           ' Sheet 명을 생성한다




    ElseIf Sheet_Use = "Data Call" Then

       Set SHname = Worksheets("Set_Up")   'Set_Up Sheet 에서 값을 가져온다

       Set IP_set = SHname.Range("A2")     'IP       설정 값을 가져온다
       Set PORT_set = SHname.Range("B2")   'PORT     설정 값을 가져온다
       Set User_set = SHname.Range("C2")   'User     설정 값을 가져온다
       Set Pass_set = SHname.Range("D2")   'PASS     설정 값을 가져온다
       Set DB_set = SHname.Range("E2")     'DB       설정 값을 가져온다
       Set DB_type = SHname.Range("F2")    'DBType   설정 값을 가져온다

        '** DB type 설정
        '** DB type 설정

        If DB_type = "MySQL" Then      'MySQL일 경우

                strCnn = "DRIVER={MySQL ODBC 5.1 Driver};SERVER=" & IP_set & ";PORT=" & PORT_set & ";DATABASE=" & DB_set & ";USER=" & User_set & ";PASSWORD=" & Pass_set & ";OPTION=3;"

        ElseIf DB_type = "MSSQL" Then 'MSSQL일 경우

                strCnn = "Provider=sqloledb;Data Source=" & IP_set & ";database=" & DB_set & ";User Id=" & User_set & ";Password=" & Pass_set & ";"

        ElseIf DB_type = "Access" Then 'ACCESS일 경우

                '2003 일때

                'strCnn = "Provider=Microsoft.Jet.OLEDB.4.0;Jet OLEDB:Database Password=" & Pass_set & ";Data Source=" & DB_set & ";"


                '2007 일때 (Password 제외하고)

                strCnn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & DB_set & ";"

        End If

        'MsgBox strCnn

        '** DB type 설정
        '** DB type 설정

        For Each rngSheet In Worksheets


            If rngSheet.Name = Sheet_Name Then               ' Sheet 명을 미리 생성하여 뿌릴곳을 정한다


                rngSheet.Range("A1:AZ1000000").ClearContents ' 기존 내용이 있으면 일단 다 지워라


                SQL = Sheet_SQL                              ' 쿼리를 불러오고


                Set Con = New ADODB.Connection
                Set RS = New ADODB.Recordset
                Con.Open strCnn


                RS.Open SQL, Con, adOpenDynamic



                 With ActiveSheet


                  For i = 1 To RS.Fields.Count

                    rngSheet.Cells(1, i).Value = RS.Fields(i - 1).Name '칼럼명 불러오고

                  Next i


                 rngSheet.Range("A2").CopyFromRecordset RS   'A2 열부터 뿌려준다
                 .UsedRange.Columns.AutoFit                  '열 자동정렬



                  Sheets(Sheet_Name).Select
                  Range("1:1").Select
                  With Selection.Interior
                     .Pattern = xlSolid
                     .PatternColorIndex = xlAutomatic
                     .Color = 65535
                     .TintAndShade = 0
                     .PatternTintAndShade = 0
                  End With
                  Selection.AutoFilter
                  Range("A2").Select
                  ActiveWindow.FreezePanes = True


                 End With



                RS.Close
                Con.Close

            End If