You need to create a new sheet TEMP1 to use as a temporary sheet to read the
data. I then copy the corrct column to the DATA sheet. You also need to
change the Folder Location as required. I didn't know if the file names
contained an extension or not. The line below may need to change. Also you
sheet TEMP was't completely filled in. What will happen if there is not
column number? The code will have an error when you run it.

Modify if necessary below
from
Connection:="TEXT;" & Folder & FName, _
to
Connection:="TEXT;" & Folder & FName & ".txt", _


Sub GetData()
'
'
Folder = "C:\temp\"

With Sheets("Data")
.Cells.ClearContents
.Range("A1") = "Category"
.Range("B1") = "Number"
.Range("C1") = "Location"
End With

With Sheets("Temp")
RowCount = 2
Do While .Range("A" & RowCount) < ""
FName = .Range("A" & RowCount)
Category = .Range("B" & RowCount)
Col = .Range("C" & RowCount)
Location = .Range("D" & RowCount)

With Sheets("Temp1")
.Cells.ClearContents
With .QueryTables.Add( _
Connection:="TEXT;" & Folder & FName, _
Destination:=Range("A1"))

.Name = "Test"
.FieldNames = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFileStartRow = 10
.TextFileParseType = xlDelimited
.TextFileOtherDelimiter = "|"
.Refresh BackgroundQuery:=False
End With

Set LastCell = .Cells(Rows.Count, Col).End(xlUp)
Set CopyRange = .Range(Cells(1, Col), LastCell)
End With

With Sheets("Data")
LastRow = .Range("B" & Rows.Count).End(xlUp).Row
NewRow = LastRow + 1
CopyRange.Copy Destination:=.Range("B" & NewRow)
LastRow = .Range("B" & Rows.Count).End(xlUp).Row
.Range("A" & NewRow & ":A" & LastRow) = Category
.Range("C" & NewRow & ":C" & LastRow) = Location
End With
RowCount = RowCount + 1
Loop
End With

End Sub


"Sinner" wrote:

Hi,

I have around 25 text files with pipe sign delimited data.
Header lines start at row 9.

I want to consolidate all the data in one worksheet named DATA.

Requirement is as follows:

- Worksheet data is of three columns.
..columnA header is Category
..columnB header is Number
..columnC header is LOCATION

- A group of text files have required data in columnB & another in
columnC. So these group of files need to be defined in import
sequence.

- Import values from all text files as text.

- Text file name are 1,2,3,4,5......25. The Category name should be a
list of names like for 1 its 'K', for 2 its, 'P' an so on making it
possible to modify later on as well.
Once the data is imported from file 1 text file, the data should goto
columnB & category should be 'K' for all data & so on for rest of the
files accordingly.

A 'TEMP' sheet will help to make sure we can modify import
requirements from there instead the main code.

Sample file is at
http://www.savefile.com/files/1786679

Any help is appreciated.

Thx.