Open and paste a .txt file to a pre-defined table

Hi all,

1. I have a pre-formatted spreadsheet. Would it be possible using a macro to open a text file (note I have files for everyday of the month) to a pre-defined table in a spreadsheet.

This macro would be taking place of selecting my table and using Get External Data> Import Text File.

This needs to be connected to a command button that is located on the spreadsheet.

2. I have a command button that clears all the data from the pre-defined data table.

3. This is to simplify everything for my employees (about 6) to be able to view data but to be able to look at certian variable that are in formulas like averages and simple addition to determine data from that day.

I can also supply a copy of my spreadsheet and a couple days of data if it will help.



Also here is what I have for code on it right now.

Sub l()
'
' l Macro
'
'
Application.Goto Reference:="Ls1_data"
Dim Fname As Variant
Fname = Application.GetOpenFilename("Text Files (*.txt),*.txt", , _
"Select Text Data File")
If Fname = False Then Exit Sub
Worksheets("Template").Range("A7:P1447").Select
Close
End Sub

Where Im not sure where to go is how to take the file I opened from the dialog and paste it to my table.

I have included my template, 3 days of data and 2 macros

If anyone can help Thanks



Tim

Try this. It requires your template to be in the same folder as the workbook.

Sub ReadTextFile()
  Dim A    As Integer, B  As Integer, C     As Single
  Dim iRow    As Long
  Dim Fname   As Variant
  Dim shtData As Worksheet
  
  Fname = Application.GetOpenFilename("Text Files (*.txt),*.txt", , _
           "Select Text Data File")
  If Fname = False Then Exit Sub
  
    Set shtData = Workbooks.Add(Template:=ThisWorkbook.Path & "\InformationTemplate.xltm").Worksheets(1)
    With shtData.QueryTables.Add(Connection:="TEXT;" & Fname, Destination:=shtData.Range("A7"))
        .Name = Replace(".txt", "", shtData.Name)
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 850
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(4, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    
End Sub