QueryTable.Refresh error in recorded macro (1004 of course!)

[puddleplasher]

I have tried to make this as simple as possible to track this down, but Excel's macro recording just doesn't seem to be recording the right thing... I just can't figure out WHAT it's recording wrong.

Simple macro steps: create new workbook, Data:From Text, select .txt file, when Query preview window comes up click directly on Load, data gets loaded, no problem during recording. Playback fails, giving error 1004: "The value isn't a single-character string" and debug highlights the .Refresh BackgroundQuery:=False line. I know both that line and 1004 are common sources of complaints but the actual problem is usually elsewhere... but I can't find it.

Full code:

Sub Macro1()
'
' Macro1 Macro
'


'
    Workbooks.Add
    ActiveWorkbook.Queries.Add Name:="Salesrep", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Source = Csv.Document(File.Contents(""C:\Users\Ann\Documents\Salesrep.txt""),[Delimiter=""  "", Columns=7, Encoding=1252, QuoteStyle=QuoteStyle.None])," & Chr(13) & "" & Chr(10) & "    #""Promoted Headers"" = Table.PromoteHeaders(Source, [PromoteAllScalars=true])," & Chr(13) & "" & Chr(10) & "    #""Changed Type"" = Table.TransformColumnTypes(#""Promoted Heade" & _
        "rs"",{{""Product"", type text}, {""Year"", Int64.Type}, {""Month"", type text}, {""Sales"", Int64.Type}, {""Units"", Int64.Type}, {""Salesperson"", type text}, {""Region"", type text}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Changed Type"""
    ActiveWorkbook.Worksheets.Add
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=Salesrep;Extended Properties=""""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [Salesrep]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Salesrep"
        .Refresh BackgroundQuery:=False
    End With
End Sub

Any ideas?

'plash


Follow-up: I know people have gotten this error when the data source hasn't been available -- so this is looking at a simple text file on the hard drive. I know it's been a problem in loops -- so no loop here. I know this used to mis-record the .CommandType line in previous versions, but it seems to be doing something legit in this version (Office 365 with updates). I know sometimes the .RefreshStyle setting has sometimes been a problem -- but I tried all the options with this and it made no difference. Argh!

 

[puddleplasher]

Solved and posting for reference by future folk:

The macro recorder wasn't recording the tab separator for the data values correctly. Where the code above said [Delimiter="" "" it should have said [Delimiter=""#(tab)"" instead (in other words, it recorded a literal Tab instead of the appropriately-worded tab argument for Formula string of the Queries.Add method.)

See this page for more: https://docs.microsoft.com/en-us/of...tFrameworkMoniker-Office.Version=v16)&rd=true

'plash