PDA

View Full Version : Load | Delimited file into Sheet3 of the excel workbook



nmakkena
11-24-2011, 08:05 PM
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
11-25-2011, 06:18 AM
but text file is opening in new workbook, not in sheet3if you quote the code, we'll tweak it.

monarchd
12-06-2011, 01:14 PM
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