Importing a Text File Using a Macro

Hi,

I am new to using excel macros and I am wondering how to go about this.

I need import 4 text files from the same directory. However I need to be able to import the file path into a cell (for example A1), and the file name to be in the macro. I think I have almost done this but I keep getting a run time 1004 error.

Below is what I have so far to import just 1 text file.

Sub txt_import()
'
' txt_import Macro
'
' Keyboard Shortcut: Ctrl+t

Dim FilePath As String
FilePath = Range("A1").Value

    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;FilePath & Textfile1.txt", Destination:=Range("$A$17"))
        .name = "avg_fft_di_8"
        .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 = True
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = True
        .TextFileColumnDataTypes = Array(1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
End With
End Sub

If someone could help me with this it would be most appreciated.

Thanks.

Moderator's note: Welcome to the forum James. When posting codes they should be enclosed with code tags. Select the code then click the "#" symbol to wrap it with code tags. I'll do it for you now. Thank you!

Assuming FilePath has a valid path and 'TextFile.txt' is a valid filename try this.

With ActiveSheet.QueryTables.Add(Connection:= _
 "TEXT;"& FilePath & "Textfile1.txt", Destination:=Range("$A$17"))

.....-----------------------------

Whats the actual error message?

Check that 'FilePath' has a trailing backslash eg. c:\temp\
Are the text files, in fact, Tab Delimited - as you have told the QueryTable they are (".TextFileTabDelimiter = True")?

FWIW, unless you're wedded to QueryTables I'd recommend tackling your problem with ADO instead.