How to fix "The value isn't a single character string" in power query

Question

I'm trying to make my power query macro to work with looping data so I need to replace filepaths and references with variables. Cell 37,1 has the location of the excel so the macro can run on different computers with simple path edit. The text file is an export from SAP.

I think the problems are in the last references.

Location=EH4

.CommandText = Array("SELECT * FROM [EH4]")

Sub GetDataEH4_2()

Dim FromFolder As String
FromFolder = Cells(37, 1).Value

Dim WorkCenter As String
WorkCenter = "EH4.txt"

Dim WC As String
WC = "EH4"

Dim FilePath As String
FilePath = FromFolder & WorkCenter

ActiveWorkbook.Queries(WC).Delete

ActiveWorkbook.Queries.Add Name:=WC, Formula:= _
    "let" & Chr(13) & "" & Chr(10) & "    Source = Csv.Document(File.Contents(""" & FilePath & """),

... Power query stuff, moving columns, removing rows, replacing cell values with "", adding custom row and determing column types ...

ActiveWorkbook.Worksheets.Add

With ActiveSheet.ListObjects.Add(SourceType:=0, Source:="OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=EH4;Extended Properties=""""" _
    , Destination:=Range("$A$1")).QueryTable
    .CommandType = xlCmdSql
    .CommandText = Array("SELECT * FROM [EH4]")
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .PreserveColumnInfo = True
    .ListObject.DisplayName = WC
    .Refresh BackgroundQuery:=False
End With

With this code the error is "Run-time error '1004': [Expression.Error] The value isn't a single-character string." I've tried changing .CommandText = Array("SELECT * FROM [EH4]") to "SELECT * FROM" & WC ) but that gives "An operation that uses the database driver could not be completed"

Powerquery stuff here.

"let" & Chr(13) & "" & Chr(10) & "    Source = Csv.Document(File.Contents(""" & FilePath & """),[Delimiter=""  "", Columns=22, Encoding=65001, QuoteStyle=QuoteStyle.None])," & Chr(13) & "" & Chr(10) & "    #""Changed Type"" = Table.TransformColumnTypes(Source,{{""Column1"", type text}, {""Column2"", type text}, {""Column3"", type text}, {""Column4"", type text}, {""Column5"", type text}, {""Col" & _
        "umn6"", type text}, {""Column7"", type text}, {""Column8"", type text}, {""Column9"", type text}, {""Column10"", type text}, {""Column11"", type text}, {""Column12"", type text}, {""Column13"", type text}, {""Column14"", type text}, {""Column15"", type text}, {""Column16"", type text}, {""Column17"", type text}, {""Column18"", type text}, {""Column19"", type text}, " & _
        "{""Column20"", type text}, {""Column21"", type text}, {""Column22"", type text}})," & Chr(13) & "" & Chr(10) & "    #""Removed Top Rows"" = Table.Skip(#""Changed Type"",7)," & Chr(13) & "" & Chr(10) & "    #""Removed Columns"" = Table.RemoveColumns(#""Removed Top Rows"",{""Column1"", ""Column3"", ""Column6"", ""Column7"", ""Column10"", ""Column15"", ""Column17"", ""Column18"", ""Column21"", ""Column4""})," & Chr(13) & "" & Chr(10) & "    #""Reorder" & _
        "ed Columns"" = Table.ReorderColumns(#""Removed Columns"",{""Column20"", ""Column19"", ""Column2"", ""Column5"", ""Column8"", ""Column9"", ""Column11"", ""Column12"", ""Column13"", ""Column14"", ""Column16"", ""Column22""})," & Chr(13) & "" & Chr(10) & "    #""Replaced Value"" = Table.ReplaceValue(#""Reordered Columns"",""Total"","""",Replacer.ReplaceText,{""Column2""})," & Chr(13) & "" & Chr(10) & "    #""Removed Blank R" & _
        "ows"" = Table.SelectRows(#""Replaced Value"", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"""", null})))," & Chr(13) & "" & Chr(10) & "    #""Replaced Value1"" = Table.ReplaceValue(#""Removed Blank Rows"",""User op. status"","""",Replacer.ReplaceText,{""Column20""})," & Chr(13) & "" & Chr(10) & "    #""Replaced Value2"" = Table.ReplaceValue(#""Replaced Value1"",""Stat"","""",Replacer.ReplaceTex" & _
        "t,{""Column19""})," & Chr(13) & "" & Chr(10) & "    #""Replaced Value3"" = Table.ReplaceValue(#""Replaced Value2"",""Week"","""",Replacer.ReplaceText,{""Column2""})," & Chr(13) & "" & Chr(10) & "    #""Replaced Value4"" = Table.ReplaceValue(#""Replaced Value3"",""Work Ctr"","""",Replacer.ReplaceText,{""Column5""})," & Chr(13) & "" & Chr(10) & "    #""Replaced Value5"" = Table.ReplaceValue(#""Replaced Value4"",""Short text for order"","""",Replacer." & _
        "ReplaceText,{""Column5""})," & Chr(13) & "" & Chr(10) & "    #""Replaced Value6"" = Table.ReplaceValue(#""Replaced Value5"",""Sales ord."","""",Replacer.ReplaceText,{""Column8""})," & Chr(13) & "" & Chr(10) & "    #""Replaced Value7"" = Table.ReplaceValue(#""Replaced Value6"",""Op."","""",Replacer.ReplaceText,{""Column9""})," & Chr(13) & "" & Chr(10) & "    #""Replaced Value8"" = Table.ReplaceValue(#""Replaced Value7"",""Operation text"","""",Repla" & _
        "cer.ReplaceText,{""Column11""})," & Chr(13) & "" & Chr(10) & "    #""Replaced Value9"" = Table.ReplaceValue(#""Replaced Value8"",""UserFl"","""",Replacer.ReplaceText,{""Column12""})," & Chr(13) & "" & Chr(10) & "    #""Replaced Value10"" = Table.ReplaceValue(#""Replaced Value9"",""User field (20) 1"","""",Replacer.ReplaceText,{""Column13""})," & Chr(13) & "" & Chr(10) & "    #""Replaced Value11"" = Table.ReplaceValue(#""Replaced Value10"","" Work""" & _
        ","""",Replacer.ReplaceText,{""Column14""})," & Chr(13) & "" & Chr(10) & "    #""Replaced Value12"" = Table.ReplaceValue(#""Replaced Value11"",""LatestFin."","""",Replacer.ReplaceText,{""Column16""})," & Chr(13) & "" & Chr(10) & "    #""Replaced Value13"" = Table.ReplaceValue(#""Replaced Value12"",""Usr date 2"","""",Replacer.ReplaceText,{""Column22""})," & Chr(13) & "" & Chr(10) & "    #""Renamed Columns"" = Table.RenameColumns(#""Replaced Value13" & _
        """,{{""Column20"", ""System status""}, {""Column19"", ""User status""}, {""Column2"", ""Week""}, {""Column5"", ""Description""}, {""Column8"", ""Sales document""}, {""Column9"", ""Activity""}, {""Column11"", ""Description activity""}, {""Column12"", ""Eng category""}, {""Column13"", ""Engineer name""}, {""Column14"", ""Work hours""}, {""Column16"", ""Latest finish d" & _
        "ate""}, {""Column22"", ""Est. finished""}})," & Chr(13) & "" & Chr(10) & "    #""Added Custom"" = Table.AddColumn(#""Renamed Columns"", ""Comments"", each """")," & Chr(13) & "" & Chr(10) & "    #""Changed Type1"" = Table.TransformColumnTypes(#""Added Custom"",{{""Latest finish date"", type date}, {""Est. finished"", type date}, {""Comments"", type text}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Changed Type1"""

Answer 1

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.