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

 

[xdonjonx]

This version of that macro will put the incoming data into the next empty row on the current sheet.

Option Explicit

Sub ImportTextFile()
Dim fName As String, LastRow As Long

fName = Application.GetOpenFilename("Text Files (*.txt), *.txt")
If fName = "False" Then Exit Sub

LastRow = Range("A" & Rows.Count).End(xlUp).Row + 1

    With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & fName, _
        Destination:=Range("A" & LastRow))
            .Name = "sample"
            .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .TextFilePromptOnRefresh = False
            .TextFilePlatform = 437
            .TextFileStartRow = 1
            .TextFileParseType = xlDelimited
            .TextFileTextQualifier = xlTextQualifierNone
            .TextFileConsecutiveDelimiter = True
            .TextFileTabDelimiter = False
            .TextFileSemicolonDelimiter = False
            .TextFileCommaDelimiter = True
            .TextFileSpaceDelimiter = False
            .TextFileOtherDelimiter = "" & Chr(10) & ""
            .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, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
            .TextFileTrailingMinusNumbers = True
            .Refresh BackgroundQuery:=False
    End With
End Sub

hi jb, actually i already add "lastrow" for destination. but it didnt work for me. when i ran macro "sample.xlsm", import "mss kpi d-1.txt", to "book1.xlsx" sheet " 1.bh tgrp", txt file not imported to destination..it always import to row A2.

i zip 3 files for you to analyze what the problem is..
https://drive.google.com/file/d/0B2k5FFT2Vr04REx0WEVTNmtMTzA/edit?usp=sharing
thanks
regards
xdonjonx

 

[jbeaucaire]

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

 

[xdonjonx]

thank you so much, jb, :cheers

 

[xdonjonx]

i have a lil problem,, what if i want to apply in many sheets with different row target,
right now..i put row target in sheet1, then for sheet2(have a different row) following from row target sheet1 automatically. so data in sheet 2 shifting.

thx
donjon

 

[jbeaucaire]

uhm.... what?

 

[xdonjonx]

i know my english is terible. i try to explain again what my problem is.

what if I want to apply the script to many sheets? the destination target in different row each sheet? is it possible?

thx jb!

 

[jbeaucaire]

You want to open a single text file and paste it into all the sheets in a workbook?

 

[xdonjonx]

yes, actually,i have a 5 output text files and paste it into 5 sheets in a workbook... each sheet has different target destination

regards,d

 

[jbeaucaire]

Then using the macro example given above, write 5 "sections" of that to import the 5 different text files and target each to the correct destination sheet.

Set TargetSH = Sheets("1. BH TGRP")
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))
.blah
.blah
.
.
.
.

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


    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;E:\CS DASHBORAD M\result\INTERFACE\MySecondTextFilename.txt" _
        , Destination:=TargetSH.Range("B" & Lastrow))
.blah
.blah
.
.
.
.
Set TargetSH = Sheets("MyThirdSheetname")
Lastrow = TargetSH.Range("B" & Rows.Count).End(xlUp).Row + 1


    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;E:\CS DASHBORAD M\result\INTERFACE\MyThirdTextFilename.txt" _
        , Destination:=TargetSH.Range("B" & Lastrow))
.blah
.blah
.
.
.
.
etc.....

 

[xdonjonx]

as simple as that
thank you so much, jb

regards.d