[EXCEL]Excel에서 DB data 가져오기 소스

Excel에서 DB data 가져오기 소스

Option Explicit

Sub getData()

    Dim SQL As String

    

    SQL = ""

    SQL = SQL & ""

    

    Open ThisWorkbook.Path & "\QUERY.dqy" For Output As #1

        Print #1, "XLODBC"

        Print #1, "1"

        Print #1, "DRIVER=SQL Server;SERVER= IP ;UID=id;PWD=pwd;APP=Microsoft® Query;WSID=MINSU;DATABASE=database"

        '쿼리문

        Print #1, SQL

    Close #1

    Sheets("Rawdata").Select

    Cells.ClearContents

    With ActiveSheet.QueryTables.Add(Connection:="FINDER;" & ThisWorkbook.Path & "\QUERY.dqy", Destination:=Range("A1"))

        .Name = "QUERY1"

        .FieldNames = True

        .RowNumbers = False

        .FillAdjacentFormulas = False

        .PreserveFormatting = True

        .RefreshOnFileOpen = False

        .BackgroundQuery = True

        .RefreshStyle = xlInsertDeleteCells 'xlOverwriteCells

        .SavePassword = True

        .SaveData = True

        .AdjustColumnWidth = True

        .RefreshPeriod = 0

        .PreserveColumnInfo = True

        .Refresh BackgroundQuery:=False

    End With

End Sub

==============================================================

case 2

Option Explicit

Sub EataExtract()

    Dim cnPubs As ADODB.Connection

    Dim rsPubs As ADODB.Recordset

    Dim NewBook As Workbook

    Dim tbar As String, msg As String

    Dim i As Integer

    Dim strConn As String

    

On Error GoTo ErrorHandler

    ' Create a connection object.

    Set cnPubs = New ADODB.Connection

    

    ' Provide the connection string.

    'Use the SQL Server OLE DB Provider.

    'strConn = "PROVIDER=SQLOLEDB;"

    

    'Connect to the Pubs database on the local server.

    'strConn = strConn & "DATA SOURCE=(local);INITIAL CATALOG=pubs;"

    strConn = strConn & "DATA SOURCE=KIS;INITIAL CATALOG=pubs;"

    

    'Use an integrated login.

    'strConn = strConn & " INTEGRATED SECURITY=sspi;"

    strConn = strConn & "uid=sa; pwd=sa100;"

    

    

    With cnPubs

        ' Set the OleDB provider for the connection.

        .Provider = "SQLOLEDB"

        'Now open the connection.

        .Open strConn

    End With

    

    ' Create a recordset object.

    Set rsPubs = New ADODB.Recordset

    

    With rsPubs

        ' Assign the Connection object.

        .ActiveConnection = cnPubs

        ' Extract the required records.

        .Open "SELECT * FROM Authors", cnPubs, adOpenDynamic, adLockBatchOptimistic

        

    End With

    

    ' Add a new worksheet to this workbook

    Set NewBook = Workbooks.Add

    

    For i = 0 To rsPubs.Fields.Count - 1

        NewBook.Sheets(1).Range("a1").Offset(0, i).Value = rsPubs.Fields(i).Name

    Next i

    

    ' Copy the records into cell A2 on Sheet1.

    NewBook.Sheets(1).Range("A2").CopyFromRecordset rsPubs

        

    ' Tidy up

    rsPubs.Close

    cnPubs.Close

    Set rsPubs = Nothing

    Set cnPubs = Nothing

Exit Sub

ErrorHandler:

    ' Refer to the Errors collection of the Connection

    ' object.

    With cnPubs.Errors(0)

        ' The title bar will contain the source of the

        ' error.

        tbar = .Source

        ' The message will contain the error number and

        ' the text of the error message.

        msg = "Error Number: " & .Number & _

              WorksheetFunction.Rept(Chr(13), 2) & _

              .Description

        ' Display the error information.

        MsgBox prompt:=msg, Title:=tbar

    End With

End Sub