VBA - 엑셀 시트를 만들면 데이터를 가져오는 소스

VBA작성(Alt+F11)

Private Sub Workbook_NewSheet(ByVal Sh As Object)
    Dim cnnConnect As ADODB.Connection
    Dim rstRecordset As ADODB.Recordset
    
    Set cnnConnect = New ADODB.Connection
    cnnConnect.Open Module1.cnn_str("MsSqlODBC")
    
    Set rstRecordset = New ADODB.Recordset
    rstRecordset.Open _
        Source:="Select * From items", _
        ActiveConnection:=cnnConnect, _
        CursorType:=adOpenDynamic, _
        LockType:=adLockReadOnly, _
        Options:=adCmdText
    
    With ActiveSheet.QueryTables.Add( _
            Connection:=rstRecordset, _
            Destination:=Range("A1"))
        .Name = "Contact List"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = True
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .Refresh BackgroundQuery:=False
    End With
End Sub

MsSqlODBC 연결문자열은 해당 DB(Oracle, MSSQL, MySql, Access, Excel, Text)에 맞게 ODBC설정한다.

시트를 추가(NewSheet event)하면 아래와 같이 원하는 데이터가 항상 생성 된다.
Result...

iid	iprod	itype	iopb	ircp	iiss	iadj	idesc
1	item1	F	20	1020	20	0	item number1
1	item1	F	20	1020	20	0	item number1
	item2	I

---

---

'