Dear members,

I need to import 100+ text files into Excel.

In addition I want Excel to show corresponding filename (where the
data comes from) in each row.

How do I accomplish this?
TIA

Hi

Take a look at Ron de Bruin's site. He has lots of code examples to
consolidate files.
http://www.rondebruin.nl/tips.htm
--
Regards
Roger Govier

"avi" wrote in message
...
Dear members,

I need to import 100+ text files into Excel.

In addition I want Excel to show corresponding filename (where the
data comes from) in each row.

How do I accomplish this?
TIA

__________ Information from ESET Smart Security, version of virus
signature database 4830 (20100203) __________

The message was checked by ESET Smart Security.

http://www.eset.com




__________ Information from ESET Smart Security, version of virus signature database 4830 (20100203) __________

The message was checked by ESET Smart Security.

http://www.eset.com

Specify the folder and try the below..

Sub Macro()

Dim strFolder As String, strFile As String
Dim strData As String, intFile As Integer, lngRow As Long

strFolder = "D:\PhoneNo"
If Right(strFolder, 1) < "\" Then strFolder = strFolder & "\"

strFile = Dir(strFolder & "*.txt", vbNormal)
Do While strFile < ""
intFile = FreeFile
Open strFolder & "\" & strFile For Input As #intFile
Do While Not EOF(intFile)
Line Input #intFile, strData
lngRow = lngRow + 1
Range("A" & lngRow) = strFile
Range("B" & lngRow) = strData
Loop
Close #intFile
strFile = Dir
Loop


End Sub

--
Jacob


"avi" wrote:

Dear members,

I need to import 100+ text files into Excel.

In addition I want Excel to show corresponding filename (where the
data comes from) in each row.

How do I accomplish this?
TIA
.

Thanks Jacob! Works perfect!
Just one note (that I forgot to mention): data in my text files is
comma delimited.
Your code imports correctly filenames & data BUT all the data is
imported into one cell.
OK, I can use "text to columns" but would like to have it
delimited.... :-)

That was not mentioned in the original post..I have modified to suit your
requirement.

Sub Macro()

Dim strFolder As String, strFile As String, lngRow As Long
Dim strData As String, arrData As Variant, intFile As Integer

strFolder = "D:\PhoneNo"
If Right(strFolder, 1) < "\" Then strFolder = strFolder & "\"

strFile = Dir(strFolder & "*.txt", vbNormal)
Do While strFile < ""
intFile = FreeFile
Open strFolder & "\" & strFile For Input As #intFile
Do While Not EOF(intFile)
Line Input #intFile, strData
arrData = Split(strData, ",")
lngRow = lngRow + 1
Range("A" & lngRow) = strFile
Range("B" & lngRow).Resize(, UBound(arrData) + 1) = arrData
Loop
Close #intFile
strFile = Dir
Loop


End Sub

--
Jacob


"avi" wrote:

Thanks Jacob! Works perfect!
Just one note (that I forgot to mention): data in my text files is
comma delimited.
Your code imports correctly filenames & data BUT all the data is
imported into one cell.
OK, I can use "text to columns" but would like to have it
delimited.... :-)

.

Jacob Skaria wrote:
* * arrData = Split(strData, ",")

That will fail if a quoted value contains a comma.

Phil Hibbs.

Why ?

"Phil Hibbs" wrote in message
...
Jacob Skaria wrote:
arrData = Split(strData, ",")

That will fail if a quoted value contains a comma.

Phil Hibbs.