Load | Delimited file into Sheet3 of the excel workbook

[nmakkena]

Hi All,
I have to load the text file which is a pipe (|) delimited file and the content of this file needs to be loaded into sheet3 of excel.

Sheet 1 is having few buttons, like "Load Table data" and "Load Text file".
If i click on "Load Table data" then sheet2 will be loaded with table data. I am done with this table data.

But the issue is i am not able to load the text file data into sheet3. pls let me know how can i import text file data to sheet3 using vba.
I tried google to solve this, but text file is opening in new workbook, not in sheet3

Thanks in advance

[p45cal]

but text file is opening in new workbook, not in sheet3

if you quote the code, we'll tweak it.

[monarchd]

See if this gets you started. I usually record a macro first walking thru all the import steps, and then replace those lines in the code below. I also find Ron's site to be most helpful at http://www.rondebruin.nl/txtcsv.htm

Sub DoTheImportPipeDelimited()
Dim varFileName
' Suppress screen flicker and updating of screen while processing
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
'change to a mapped drive if needed
'ChDrive "N"
'ChDir "N:\SomeFolder\AnotherFolder"
'prompt to ask where and which file to import using Windows built in File Explorer
varFileName = Application.GetOpenFilename("Text Files (*.txt), *.txt")
' exit if user tries to import a blank file name, let them try again
If varFileName = False Then
MsgBox "File not selected. Please try again."
Exit Sub
End If
Sheets("Sheet3").Select
If TypeName(varFileName) = "String" Then
        With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & varFileName, Destination:=Range("A1"))
        
        .Name = "My_Pipe_File"
        .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 = xlFixedWidth
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        'change to True for the delimiter you want
        'currently set for pipe delimiter
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileOtherDelimiter = "|"
        'you will want to record a macro and then replace the .TextFileColumnDataTypes below
        .TextFileColumnDataTypes = Array(1)
        'or try this one
        '.TextFileColumnDataTypes = Array(2)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    End If
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
MsgBox ("Data imported successfully.")
Exit Sub
End Sub