Excel Expression Error 値が１文字の文字列ではありません の解決方法

発生方法

1. Excelにテキストでタブ区切り形式のデータを読み込ませるマクロを記録する。
2. その接続と、シートを削除する。（接続を削除しないと、重複した名前になり別のエラーが起きる）
3. もう一度マクロを起動すると次のエラーが発生する
4. Expression Error 値が1文字の文字列ではありません

原因

まずこれをマクロの記録で作る.
すさまじいひどい形式だ。

ActiveWorkbook.Queries.Add Name:="FilebaseName", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    ソース = Csv.Document(File.Contents(""C:\hoge\File.txt""),[Delimiter==""  "", Columns=10, Encoding=65001, QuoteStyle=QuoteStyle.None])," & Chr(13) & "" & Chr(10) & "    昇格されたヘッダー数 = Table.PromoteHeaders(ソース, [PromoteAllScalars=true])," & Chr(13) & "" & Chr(10) & "    変更された型 = Table.TransformColumnTypes(昇格されたヘッダー数,{{""列名1"", Int64.Type}, {""列名2"", type text}, {""列名3"", type lo" & _
        "gical}, {""列名4"", type time}, {""列名5"", type text}, {""列名6"", type text}, {""列名7"", type text}, {""列名8"", Int64.Type}, {""列名9"", type text}, {""列名10"", type text}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    変更された型" & _

すさまじくひどい点

• そもそも最後が切れている
• Logicalが途中でLo 改行 gicalになっている
• さらに長整数型はなぜかInt64.Tyoeとピリオドが入る
• 昇格されたヘッダー数のところなどなぜかスペースが入る

ちなみにこのめちゃくちゃなのはパワークエリの詳細の部分を表している。
昇格されたヘッダーとは、テキストファイルの１行目の列名を採用されたことを言っているらしい。

VBAの記録の流れ

ファイル名のベースネームが、クエリ名、シート名となる
タブ区切り、UTF-8
BOM付き

1. `ActiveWorkbook.Queries.Add
2. Worksheet追加
3. With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=SheetName; Extended Properties=""""" _
, Destination:=Range("$A$1")).QueryTable　が来る。
4. いわゆる滝が来る
   .CommandType = xlCmdSql
.CommandText = Array("SELECT * FROM [クエリ名]")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = "クエリ名"
.Refresh BackgroundQuery:=False
End With
Application.CommandBars("Queries and Connections").Visible = False

Qiitaのマークダウン（インラインコード表示が効かないレベル。

おわかりいただけるだろうか、クエリっぽい名前をワークブックレベル、ワークシートレベル、さらにCommandTextでSQLが必要になっている。
一体このどこにエラーの要素があるのか。

解決方法

またしても

この解説は日本語にはないらしい。今回の記事が日本語ベース最初となるようだ

ヒント

Stack Overflowと公式の解説がヒントとなる。
excel - How to fix "The value isn't a single character string" in power query - Stack Overflow
Queries.Add メソッド (Excel) | Microsoft Learn
英語ではa singleってなんじゃこりゃ？
これは値が１文字の文字列ではありませんとか言うのはあほやろ。
まずこのエクスプレッションエラーというのは式、つまりformula部分に発生している。
そして、英語はこんな感じであるが、余計わからない。
まあ、とにかく公式のサンプルを見てくれ。といってもまたいつ削除になるかわからないので引用する。

Dim myConnection As WorkbookConnection
Dim mFormula As String
mFormula = _
"let Source = Csv.Document(File.Contents(""C:\data.txt""),null,""#(tab)"",null,1252) in Source"
query1 = ActiveWorkbook.Queries.Add("query1", mFormula)

これは文字コードが1252タブ区切りだが、名前付き引数が書いていない。
""#(tab)""はマクロの記録では[Delimiter==""#(tab)""
となるはずである、

エラー発生の原因

マクロの記録で記録されたコードではタブ区切りの場合、空白２文字として出力されてしまう。
このため、本来１文字であるべき区切り文字が２文字になる。
しかし、タブはどーやっていれるかというのが難しい。Chr(9)とかもうまくいかず#(tab)というわけのわからない文字になる。こんなタブの表記ほかでみた記憶がない。
M 言語の字句の構造 - PowerQuery M | Microsoft Learn
これはエスケープしているそうだ。M言語・・・この言語を使うものは被虐性癖があるとでもいうのだろか。
だいたいこういうへんてこな仕様は数年先に廃止されることが多いので不安である。
実際この中身はSQLをさらに詳しく書いてあるだけなので、言語というのは恥ずかしすぎる。中二病か？力抜けよ、みたいなレベル。だいたい仕様を定めてマクロで出力できないってバグでしょ。

解決方法

できたコードのDelimiter=="" ""をDelimiter==""#(tab)""に書き換えるとOK。
それかコンマ区切りにする。（しないけど）

再現度100％

StackOverFlowでも答えた人に起きている。

The same error can come out when trying to record a MACRO to import data to power query.
In my situation, I figured out that the Delimiter="" "" changed to Delimiter=""#(tab)"" solved my problem.

つまりタブ区切りでマクロの記録を使うと必ず起きるらしい。MACROにMicrosoftへの恨みが詰まっているよコレ。。。

ただしAccessより性能はよい

癖はあるが、Tabが複数入っていても割と無視してインポートするなど、親切なところはある。Accessではこうはいかない。またBOMにも影響されない。

おまけエクスポートするときはADODB.Streamがよいようだ

単純にはコピペで貼り付けでもよいが。もうインポート、エクスポートを繰り返すなら、正しいCSVを出力させたほうが良い。特に値がないところは""を入れる。最後はタブを入れない
時間だけの場合はFormatを入れる。そうしないと小数点になり、再度インポートするとそれを日付に変えようとするので、おかしなことになる。
通貨型とかは試していないが、ほかの型でも何か起きそうではある。

For iRow = 2 To LastRow
For iCol = 1 To 9
If iCol = 4 Then
buf = buf & Format(ws.Cells(iRow, iCol), "hh:mm:ss") & vbTab
Else
If IsEmpty(ws.Cells(iRow, iCol)) = True Or ws.Cells(iRow, iCol) = "" Then buf = buf & """""" & vbTab Else buf = buf & ws.Cells(iRow, iCol) & vbTab
End If
Next
buf = buf & """""" ' 今回のデータは最後の列が必ず空白なので、10列目はこのようにする。タブを追加させない。

仕様も翻訳もおかしい

　まず、これって仕様で発生しているんだからエラーではない。タブ区切りはわかっているのだから、マクロ作成時に「すみません、タブ区切りは出力できないので、お客様でエスケープ文字に訂正してください：くらいのメッセージを出すべきだろう。
　次に英語でも日本語でも訳が分からない。a singleってなんぞ。これって区切り文字などで１文字であるべきところが２文字以上になっています。くらいだよね。直訳でも原文でもわかる人はいるわけがない。

M言語とかMシステムの存続可能性が感じられない

こ れ を み て み ろ よ（ 出典：M 言語の型 - PowerQuery）

わかるわけないだろ。抽象型とかポリコレで訳がわからない単語になっている。
そもそもクエリとSchema.iniをごった煮にしただけじゃないか。なにがM言語だ。ただのSQLじゃないか。
しかもマクロの記録で記録できないのは終わっている。
こんなにイキリ散らかす暇があったらAccessを改良すべきでしょう。