Sub ShowDB1() strQuery = "SELECT * FROM DBinput1" Call changQuery End Sub
Sub ShowDB2() strQuery = "SELECT * FROM DBinput2" Call changQuery End Sub
Sub ShowDBAll() strQuery = "(SELECT * FROM DBinput1) UNION ALL (SELECT * FROM DBinput2)" Call changQuery End Sub
Sub ShowDoubleCode() strQuery = "SELECT s1.품목, s1.규격, s1.단가 FROM DBinput1 s1, DBinput2 s2 WHERE s1.품목 = s2.품목" Call changQuery End Sub
Private Function changQuery() If strFilePath = "" Then strFilePath = ThisWorkbook.FullName If strFolder = "" Then strFolder = ThisWorkbook.Path With ActiveSheet.QueryTables(1) .Connection = Array(Array( _ "ODBC;DSN=Excel Files;DBQ=" & strFilePath & " ;DefaultDir=" & strFolder & ";DriverId=790;MaxBufferSize=2048;PageTimeout=5" _ ), Array(";")) .CommandText = Array(strQuery) .Refresh BackgroundQuery:=False End With End Function
![[레벨:1]](http://abyul.com/zbxe/modules/point/icons/default/1.gif "포인트:187point (35%), 레벨:1/30"){kind=icon}
프로도
![[레벨:30]](http://abyul.com/zbxe/modules/point/icons/default/30.gif "포인트:141971point, 레벨:30/30"){kind=icon}
아별
와우.. 새벽 4시..
제가 미쳤나 봅니다. 내일 출근해야하는데.. ㅋㅋ
첨부파일에 자세히 적었으니.. 참고하세요..
abyul_QnA_QueryJoin_20100514-2.xls
일단.. 님이 요구하시는 것은..
DataBase를 결합하는 문제와 비슷해서 SQL을 사용했습니다.
엑셀의 기본 기능인 데이터 통합이나.. 고급필터 따위로는 불가능하죠..
첨부파일에는 외부데이터 연결을 통해서 SQL을 사용하는 방법을 나름~ 상세히 적어놨구요..
실제 기능 구현은 아래의 VBA코드로 했습니다.
아래 코드들이 사용됐습니다.
사실 구현은 좀 더 일찍 끝났지만.. 자꾸 에러가 나서 에러 찾는데 시간을 좀 보냈습니다.
에러의 원인은.. 회원님이 주신 샘플 데이터에서 "입력2" 테이블의 "품목" 필드명이 "픔목"이기 때문이었습니다. 두둥!
프..음..목.. =_=;;;
Option Explicit
Dim strFilePath As String
Dim strFolder As String
Dim strQuery As String
Sub makeQueryTable()
strFilePath = ThisWorkbook.FullName
strFolder = ThisWorkbook.Path
strQuery = "SELECT s1.품목, s1.규격, s1.단가 FROM DBinput1 s1, DBinput2 s2 WHERE s1.품목 = s2.품목"
Dim rngTarget As Range
Set rngTarget = ActiveSheet.Range("B2")
Dim qt As queryTable
If ActiveSheet.QueryTables.Count >= 1 Then
For Each qt In ActiveSheet.QueryTables
qt.Delete
Next qt
End If
With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DSN=Excel Files;DBQ=" & strFilePath & " ;DefaultDir=" & strFolder & ";DriverId=790;MaxBufferSize=2048;PageTimeout=5" _
), Array(";")), Destination:=rngTarget)
.CommandText = Array(strQuery)
.Name = "abyulQuery001"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
End Sub
Sub ShowDB1()
strQuery = "SELECT * FROM DBinput1"
Call changQuery
End Sub
Sub ShowDB2()
strQuery = "SELECT * FROM DBinput2"
Call changQuery
End Sub
Sub ShowDBAll()
strQuery = "(SELECT * FROM DBinput1) UNION ALL (SELECT * FROM DBinput2)"
Call changQuery
End Sub
Sub ShowDoubleCode()
strQuery = "SELECT s1.품목, s1.규격, s1.단가 FROM DBinput1 s1, DBinput2 s2 WHERE s1.품목 = s2.품목"
Call changQuery
End Sub
Private Function changQuery()
If strFilePath = "" Then strFilePath = ThisWorkbook.FullName
If strFolder = "" Then strFolder = ThisWorkbook.Path
With ActiveSheet.QueryTables(1)
.Connection = Array(Array( _
"ODBC;DSN=Excel Files;DBQ=" & strFilePath & " ;DefaultDir=" & strFolder & ";DriverId=790;MaxBufferSize=2048;PageTimeout=5" _
), Array(";"))
.CommandText = Array(strQuery)
.Refresh BackgroundQuery:=False
End With
End Function