Import and format txt file into open workbook.

[djdbg1]

I have a workbook that i copy each day, import a fresh txt file (created daily), format some columns to ensure text as they are numeric identifiers and excel always compacts them. I then proceed to run various macros against this data to conditional format and populate cells with formulas. This is a daily task using fresh extract, hence why using macros to streamline and ensure regularity.

I am just about complete, but i still manually import the file, use powerdata to select columns that i need to change to text and then load.

I have recorded macro of me importing the text file, but when i run the macro, I get the following error "[Expression.Error] The value isn't a single-character string."
I have tried a few times and always same error. But when i do steps manually then there is never an issue. Script is as follows, ( i had to obscure the column data due to sensitivity but hopefully still makes sense.

Sub Import_Suspect_Extract_1()
'
' Import_Sheet1 Macro
'

'
    ActiveWorkbook.Queries.Add Name:="Sheet1", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Source = Csv.Document(File.Contents(""[B]file location[/B]\Sheet1.txt""),[Delimiter=""  "", Columns=146, 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 Headers"",{" & _
        "{""SuspectIdPK"", type text}, {""Rec 1"", type text}, {""Rec 2"", type text}, [B]'Lots of other columns in here'[/B] {""Email "", 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=Sheet1;Extended Properties=""""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [Sheet1]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Sheet1"
        .Refresh BackgroundQuery:=True
    End With
    Application.CommandBars("Queries and Connections").Visible = False
    MsgBox ("Suspect Extract Successfully Imported")
End Sub

 

[jkpieterse]

Why are you inserting a new query using VBA, Powerquery was intended to give you "Build once, refresh many" solutions.
Using the From File, From Folder button it allows you to (e.g.) automatically import the most recent txt file from a folder on every refresh.

 

[djdbg1]

Why are you inserting a new query using VBA, Powerquery was intended to give you "Build once, refresh many" solutions.
Using the From File, From Folder button it allows you to (e.g.) automatically import the most recent txt file from a folder on every refresh.

I'm not overly familiar with Power Query, i just use it to import txt docs to excel so i can ensure columns formatted correctly.
But.. i create a new folder each day (as it is a daily task), and a fresh extract (txt doc) goes into that folder each day along with newly created workbook (copied from master which has macros saved in it), which is fed by the fresh extract

 

[jkpieterse]

I advise you to read everything on this page: How to use the current file location as the relative path so I don't need to reset all my sources? It should get you somewhere.

 

[djdbg1]

I advise you to read everything on this page: How to use the current file location as the relative path so I don't need to reset all my sources? It should get you somewhere.

Thanks for the link, I have read up and down the post many times. I tried applying the formula to a cell, but i couldn't wrap my head around it and in turn couldn't reference it within power query.