テスト116

'Excel
Sub Excel_SQL_JOIN2()

Dim CN As ADODB.Connection
Dim RS As ADODB.Recordset
Dim MyPath As String
Dim str_SQL1 As String
Dim str_SQL2 As String

Set CN = New ADODB.Connection
Set RS = New ADODB.Recordset

MyPath = ThisWorkbook.FullName

CN.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
               "Data Source=" & MyPath & ";" & _
               "Extended Properties='Excel 12.0;HDR=YES' "

'完全外部結合(サブクエリ分)
str_SQL1 = "SELECT A.*, B.* " & _
                    "FROM [テストデータ1$] AS A " & _
                    "LEFT OUTER JOIN [テストデータ2$] AS B " & _
                    "ON A.商品コード = B.商品コード " & _
                    "UNION " & _
                    "SELECT A.*, B.* " & _
                    "FROM [テストデータ1$] AS A " & _
                    "LEFT OUTER JOIN [テストデータ2$] AS B " & _
                    "ON A.商品コード = B.商品コード "

'片側テーブル分だけで処理しようとすれば重複は防げる
str_SQL2 = "SELECT DISTINCT A.商品コード " & _
                   "FROM (" & str_SQL1 & ") AS C"

Set RS = CN.Execute(str_SQL2)

With ThisWorkbook.Sheets("テスト出力")
    
    .Cells.Clear
    
    .Range("A1:D1").Value = Array("商品コード_A", "金額", "商品コード_B", "金額")

    .Range("A2").CopyFromRecordset RS
    
    .Columns("A:D").AutoFit

End With

RS.Close
CN.Close

Set RS = Nothing
Set CN = Nothing

MsgBox "処理が終了しました。", vbInformation

End Sub