Import data from notepad

[Ryanmm50]

I need to import data from notepad but I need the source notepad file to be selected by the open file prompt. I tried using the macro recorder but I can't get it to adjust to the variable. And does anyone know the character [10] and [13] means? The commented out part of the code is the original code I thought would work using the variable datafile. It doesn't import any data, just the headers.
Thanks in Advance!

Sub GetData()
'7/26/17 New Macro
'Gets Data from the POS and formats it into usable data


 Dim dataFile As String
 Dim dataSheet As Worksheet
 Dim rawDat As String
  
'    Set dataSheet = Sheets.Add
'    dataSheet.Name = ("Raw Data")
    'dataFile = Application.GetOpenFilename("Text Files (*.txt), *.txt")
    
'If dataFile = "False" Then
'    Exit Sub
'End If


'
    Range("A1").Select
    ActiveWorkbook.Queries.Add Name:="July test (4)", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Source = Table.FromColumns({Lines.FromBinary(File.Contents(""C:\Users\user\Desktop\July test.txt""), null, null, 1252)})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    Source"
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""July test (4)""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [July test (4)]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "July_test__4"
        .Refresh BackgroundQuery:=False
    End With
    Range("A2").Select










''First try by recording macro and what i thought to change
'Range("A1").Select
'    ActiveWorkbook.Queries.Add Name:="Raw Data", Formula:= _
'        "let" & Chr(13) & "" & Chr(10) & "    Source = Table.FromColumns({Lines.FromBinary(File.Contents(datafile), null, null, 1252)})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    Source"
'    ActiveWorkbook.Worksheets.Add
'    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
'        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=datafile" _
'        , Destination:=Range("$A$1")).QueryTable
'        .CommandType = xlCmdSql
'        .CommandText = Array("SELECT * FROM " & dataFile)
'        .RowNumbers = False
'        .FillAdjacentFormulas = False
'        .PreserveFormatting = True
'        .RefreshOnFileOpen = False
'        .BackgroundQuery = True
'        .RefreshStyle = xlInsertDeleteCells
'        .SavePassword = False
'        .SaveData = True
'        .AdjustColumnWidth = True
'        .RefreshPeriod = 0
'        .PreserveColumnInfo = True
'        '.ListObject.DisplayName = "Raw Data List"
'        '.Refresh BackgroundQuery:=False
'    End With
'    Range("A2").Select
'

 

[Norie]

Re: Inport data from notepad

Try this.

Sub GetData()
'7/26/17 New Macro
'Gets Data from the POS and formats it into usable data


 Dim dataFile As String
 Dim dataSheet As Worksheet
 Dim rawDat As String
  
'    Set dataSheet = Sheets.Add
'    dataSheet.Name = ("Raw Data")
    'dataFile = Application.GetOpenFilename("Text Files (*.txt), *.txt")
    
'If dataFile = "False" Then
'    Exit Sub
'End If


'
    Range("A1").Select
    ActiveWorkbook.Queries.Add Name:="July test (4)", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Source = Table.FromColumns({Lines.FromBinary(File.Contents(""" & datafile & """), null, null, 1252)})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    Source"
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""July test (4)""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [July test (4)]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "July_test__4"
        .Refresh BackgroundQuery:=False
    End With
    Range("A2").Select