vba macro to import text file

[just_using_excel]

im looking for a macro that will use the import feature that will import text and delimit it.

i want to manually choose the file. all i know is workbooks.open filename: function, not the import

 

[shiraprincessofpower]

Hi, I'm trying to do the same thing, Import a bunch of text files to excel, but I need each one on a separate tab and they are all fixed width, same width. For example I need it to be broken at 3, 11, 45 and 76. All of them. Can anyone help me with this? I tried recording a macro and using the ones I saw on this thread, so far no dice. Thanks!

 

[jbeaucaire]

Hi, I'm trying to do the same thing, Import a bunch of text files to excel, but I need each one on a separate tab and they are all fixed width, same width. For example I need it to be broken at 3, 11, 45 and 76. All of them. Can anyone help me with this? I tried recording a macro and using the ones I saw on this thread, so far no dice. Thanks!

Record yourself manually importing two text files into a workbook as separate sheets.
Post the recorded code here.
I can take that code and turn it into a macro that will import all the text files in a single folder.

 

[Bart196]

THe problem is that you're running the LASTROW code without referencing the target sheet first.

Sub Button20_Click()
Dim Lastrow As Long, TargetSH As Worksheet

On Error Resume Next
Set TargetSH = Workbooks("Book1.xlsx").Sheets("1. BH TGRP")
If TargetSH Is Nothing Then
    Workbooks.Open ("C:\Path\To\File\Book1.xlsx")
    Set TargetSH = Sheets("1. BH TGRP")
End If

Lastrow = TargetSH.Range("B" & Rows.Count).End(xlUp).Row + 1


    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;E:\CS DASHBORAD M\result\INTERFACE\MSS KPI D-10_NER_BH_TGRP.txt" _
        , Destination:=TargetSH.Range("B" & Lastrow))
        .Name = "MSS KPI D-10_NER_BH_TGRP"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = False
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _
        1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    Range("B" & Lastrow).Select
End Sub

How would this code change if you wanted to paste the info into an existing table ListObjects("Table24"), retaining the same formatting?