Sub クレンジング()
    Dim folderPath As String
    Dim filePath As String
    Dim mCode As Variant
    Dim formulaText As String
    Dim ws As Worksheet
    Dim lo As ListObject
    Dim i As Long

    folderPath = ThisWorkbook.Path & "\"
    filePath = folderPath & "7-12-売上明細_会社名クレンジング用.csv"

    If Dir(filePath) = "" Then
        MsgBox "7-12-売上明細_会社名クレンジング用.csv が見つかりません。", vbExclamation
        Exit Sub
    End If

    Application.DisplayAlerts = False
    On Error Resume Next

    For Each ws In ThisWorkbook.Worksheets
        For i = ws.ListObjects.Count To 1 Step -1
            If Not ws.ListObjects(i).QueryTable Is Nothing Then
                ws.ListObjects(i).Delete
            End If
        Next i
    Next ws

    For i = ThisWorkbook.Connections.Count To 1 Step -1
        ThisWorkbook.Connections(i).Delete
    Next i

    For i = ThisWorkbook.Queries.Count To 1 Step -1
        ThisWorkbook.Queries(i).Delete
    Next i

    On Error GoTo 0
    Application.DisplayAlerts = True

    mCode = Array( _
        "let", _
        "    ソース = Csv.Document(File.Contents(""" & filePath & """), [Delimiter="","", Encoding=65001, QuoteStyle=QuoteStyle.None]),", _
        "    ヘッダー昇格 = Table.PromoteHeaders(ソース, [PromoteAllScalars=true]),", _
        "    株式会社に置換1 = Table.ReplaceValue(ヘッダー昇格, ""㈱"", ""株式会社"", Replacer.ReplaceText, {""会社名""}),", _
        "    株式会社に置換2 = Table.ReplaceValue(株式会社に置換1, ""(株)"", ""株式会社"", Replacer.ReplaceText, {""会社名""}),", _
        "    株式会社削除 = Table.ReplaceValue(株式会社に置換2, ""株式会社"", """", Replacer.ReplaceText, {""会社名""}),", _
        "    空白削除 = Table.TransformColumns(株式会社削除, {{""会社名"", each Text.Remove(_, {"" "", ""　""}), type text}})", _
        "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
        .Delete
    End With

    ThisWorkbook.Queries("クレンジング").Delete
End Sub
