Sub 分割と結合()
    Dim folderPath As String
    Dim mCode As Variant
    Dim formulaText As String
    Dim ws As Worksheet
    Dim lo As ListObject

    folderPath = ThisWorkbook.Path & "\"

    mCode = Array( _
        "let", _
        "    ソース = Csv.Document(File.Contents(""" & folderPath & "7-7-会員一覧表.csv""), [Delimiter="","", Encoding=932, QuoteStyle=QuoteStyle.None]),", _
        "    ヘッダー昇格 = Table.PromoteHeaders(ソース, [PromoteAllScalars=true]),", _
        "    会員コード追加 = Table.AddColumn(ヘッダー昇格, ""会員コード"", each [年度] & ""-"" & [連番], type text),", _
        "    氏名分割 = Table.SplitColumn(会員コード追加, ""氏名"", Splitter.SplitTextByDelimiter("" "", QuoteStyle.Csv), {""姓"", ""名""}),", _
        "    列削除 = Table.RemoveColumns(氏名分割, {""年度"", ""連番""}),", _
        "    列並べ替え = Table.ReorderColumns(列削除, {""会員コード"", ""姓"", ""名"", ""住所"", ""電話番号"", ""入会日""})", _
        "in", _
        "    列並べ替え" _
    )

    formulaText = Join(mCode, vbCrLf)

    ThisWorkbook.Queries.Add _
        Name:="分割と結合", _
        Formula:=formulaText

    Set ws = ThisWorkbook.Worksheets("Sheet1")

    Set lo = ws.ListObjects.Add( _
        SourceType:=0, _
        Source:="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
