Sub 左反結合()
    Dim mCode As Variant
    Dim formulaText As String
    Dim csvPath1 As String
    Dim csvPath2 As String
    Dim ws As Worksheet
    Dim lo As ListObject

    csvPath1 = ThisWorkbook.Path & "\7-11-左反結合_明細データ.csv"
    csvPath2 = ThisWorkbook.Path & "\7-11-左反結合_商品マスター.csv"

    mCode = Array( _
        "let", _
        "    明細データ = Csv.Document(File.Contents(""" & Replace(csvPath1, "\", "\\") & """), [Delimiter="","", Encoding=65001, QuoteStyle=QuoteStyle.Csv]),", _
        "    明細ヘッダー = Table.PromoteHeaders(明細データ, [PromoteAllScalars=true]),", _
        "    明細型変更 = Table.TransformColumnTypes(明細ヘッダー,{{""商品コード"", type text}, {""数量"", Int64.Type}}),", _
        "    商品マスター = Csv.Document(File.Contents(""" & Replace(csvPath2, "\", "\\") & """), [Delimiter="","", Encoding=65001, QuoteStyle=QuoteStyle.Csv]),", _
        "    商品ヘッダー = Table.PromoteHeaders(商品マスター, [PromoteAllScalars=true]),", _
        "    商品型変更 = Table.TransformColumnTypes(商品ヘッダー,{{""商品コード"", type text}, {""商品名"", type text}, {""単価"", Int64.Type}}),", _
        "    左反結合 = Table.NestedJoin(明細型変更, {""商品コード""}, 商品型変更, {""商品コード""}, ""商品マスター"", JoinKind.LeftAnti)", _
        "in", _
        "    左反結合" _
    )

    formulaText = Join(mCode, vbCrLf)

    ThisWorkbook.Queries.Add _
        Name:="左反結合", _
        Formula:=formulaText

    Set ws = ThisWorkbook.Worksheets("Sheet1")

    Set lo = ws.ListObjects.Add( _
        SourceType:=0, _
        Source:=Array( _
            "OLEDB;Provider=Microsoft.Mashup.OleDb.1;" & _
            "Data Source=$Workbook$;" & _
            "Location=左反結合;" & _
            "Extended Properties=""""" _
        ), _
        Destination:=ws.Range("A1") _
    )

    lo.Name = "T_左反結合"

    With lo.QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [左反結合]")
        .Refresh BackgroundQuery:=False
    End With

    ThisWorkbook.Queries("左反結合").Delete
End Sub
