This sub is a Excel 2019 Power Query that imports a user selected delimited text file and then extracts the required data.

The line of code “.Refresh BackgroundQuery:=False” is causing Run-time error ‘1004’: The name ‘Source’ wasn’t recognized. Make sure it’s spelled correctly.

For this query, BackgroundQuery:=False has to set to false otherwise the query fails unless there is another way to have the query work as expected.

For reasons of confidentiality I have changed the names of the columns so titles such as “Credit Card” and “email” are all false and acting as place holders but the rest of the code is real.

Sub ImportTransformTXT_Rev_25)

   Dim backSlash As String
   backSlash = Application.PathSeparator

   ' Create a FileDialog object as a File Picker
   Dim fd As FileDialog
   Set fd = Application.FileDialog(msoFileDialogFilePicker)

   ' Filter to only allow text files to be selected
   fd.Filters.Add "Text Files", "*.txt"

   ' Shows the File Picker dialog box
   If fd.Show = -1 Then
      Dim FileFullPath As String
      FileFullPath = fd.SelectedItems(1) ' Gets the selected file path
   Else
      Exit Sub ' User cancelled, exit the sub
   End If

   Dim FileName As String
   FileName = Mid$(FileFullPath, InStrRev(FileFullPath, backSlash) + 1)
   ' remove extension and any other dots
   FileName = Replace$(Left$(FileName, InStrRev(FileName, ".") - 1), ".", "")

   Dim FileNameNoSpaces As String
   FileNameNoSpaces = Replace$(FileName, " ", "_")

   ' Add a new query to import and transform the CSV data
   ActiveWorkbook.Queries.Add Name:=FileName, Formula:= _
      "let" & vbCrLf & _
      "    Source = Csv.Document(File.Contents(""" & FileFullPath & """,[Delimiter="";"", Encoding=65001, QuoteStyle=QuoteStyle.None])," & vbCrLf & _
      "    #""Changed Type"" = Table.TransformColumnTypes(Source,{{""Column1"", type text}, {""Column2"", type text}, {""Column3"", type text}})," & vbCrLf & _
      "    #""Added Index"" = Table.AddIndexColumn(#""Changed Type"", ""Index"", 1, 1, Int64.Type)," & vbCrLf & _
      "    #""Inserted Merged Column"" = Table.AddColumn(#""Added Index"", ""Merged"", each Text.Combine({[Column3], [Column2]}, """"), type text)," & vbCrLf & _
      "    #""Removed Columns"" = Table.RemoveColumns(#""Inserted Merged Column"",{""Column2"", ""Column3""})," & vbCrLf & _
      "    #""Reordered Columns"" = Table.ReorderColumns(#""Removed Columns"",{""Column1"", ""Merged"", ""Index""})," & vbCrLf & _
      "    #""Filtered Rows"" = Table.SelectRows(#""Reordered Columns"", each ([Column1] = ""Company name"" or [Column1] = ""Complete name"" or [Column1] = ""Credit Card #"" or [Column1] = ""Credit Card Type"" or [Column1] = ""Currency"" or [Column1] = ""email""))," & vbCrLf & _
      "    #""Pivoted Column"" = Table.Pivot(#""Filtered Rows"", List.Distinct(#""Filtered Rows""[Column1]), ""Column1"", ""Merged"")," & vbCrLf & _
      "    #""Reordered Columns1"" = Table.ReorderColumns(#""Pivoted Column"",{""Index"", ""Complete name"", ""email"", ""Credit Card Type"", ""Credit Card #"", ""Currency"", ""Company name""})" & vbCrLf & _
      "in" & vbCrLf & _
      "    #""Reordered Columns1"""

   ' Add a new worksheet to paste the data
   ActiveWorkbook.Worksheets.Add

   ' Create a ListObject to hold the imported data
   Dim lo As ListObject
   Set lo = ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
      "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""" & FileName & """;Extended Properties=""""" _
      , Destination:=Range("$A$1"))

   With lo.QueryTable
      .CommandType = xlCmdSql
      .CommandText = Array("SELECT * FROM [" & FileName & "]")
      .RowNumbers = False
      .FillAdjacentFormulas = False
      .PreserveFormatting = True
      .RefreshOnFileOpen = False
      .BackgroundQuery = True
      .RefreshStyle = xlInsertDeleteCells
      .SavePassword = False
      .SaveData = True
      .AdjustColumnWidth = True
      .RefreshPeriod = 0
      .PreserveColumnInfo = True
       lo.DisplayName = FileNameNoSpaces
      .Refresh BackgroundQuery:=False ' Refresh the query without running in the background
   End With

   ' Get the range of the imported data and then unlist the ListObject
   Dim dataRange As Range
   Set dataRange = lo.Range
   lo.Unlist

   ' Hide the 'Queries and Connections' task pane
   Application.CommandBars("Queries and Connections").Visible = False

   ' Clear formatting and borders inside the data range
   With dataRange
      .Borders.LineStyle = xlNone
      With .Interior
         .Pattern = xlNone
         .TintAndShade = 0
         .PatternTintAndShade = 0
      End With
      With .Font
         .ColorIndex = xlAutomatic
         .TintAndShade = 0
      End With
   End With
End Sub