Loading text file with specific name in excel

[wes2706]

I will need to load a text file into excel into a particularworksheet called “Temp_Cal” in cell “A1”… the text file should be loaded as delimitedand the delimiters are tab and space only.
As an input I have the folder location in an excel cell…letssay Sheet1 in cell C3
And the file name is also in an excel cell lets say Sheet1in cell C4
Using the values in Sheet1 cell C3 andSheet1 Cell C4 as my input, I need to create a string with the file name and location, then openthe text file and insert it into Excel Sheet (“Temp_Cal”) from Cell “A1” onwards…
Can someone please help?

[mdmackillop]

Use the macro recorder to determine data types etc. and substitute accordingly.

Sub Imports()
    Dim Conn As String


    With Sheets("Sheet1")
        Conn = "TEXT;" & .Cells(3, 3) & "\" & .Cells(4, 3)
    End With
    
    With Sheets("TempCal")
        With .QueryTables.Add(Connection:=Conn, Destination:=.Range("$A$1"))
            .Name = "TextData"
            .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .TextFilePromptOnRefresh = False
            .TextFilePlatform = 1257
            .TextFileStartRow = 1
            .TextFileParseType = xlDelimited
            .TextFileTextQualifier = xlTextQualifierDoubleQuote
            .TextFileConsecutiveDelimiter = True
            .TextFileTabDelimiter = True
            .TextFileSemicolonDelimiter = False
            .TextFileCommaDelimiter = False
            .TextFileSpaceDelimiter = True
             '.TextFileColumnDataTypes = Array(1, 1, 1)
            .TextFileTrailingMinusNumbers = True
            .Refresh BackgroundQuery:=False
        End With
    End With
End Sub