Multiple .txt file import with filenames

**luke2442** · 07-26-2011, 05:54 AM

Hi, I need to import 20,000 txt files into excel with the corresponding filenames. Each txt file should only take one cell, likewise the filename.

I came very close to solving this by copying Leith's code from this thread:

http://www.excelforum.com/excel-prog...worksheet.html

and modifying it as follows (just replaced Celldata with FileName in column B)

Sub ReadTextFiles()

Dim CellData As String
Dim FileName As String
Dim FilePath As String
Dim LineCnt As Long
Dim R As Long
Dim Text As String
Dim Wks As Worksheet

R = 1
Set Wks = Worksheets("Sheet1")

FilePath = "C:\Users\Platinum\Desktop\ExcelMacro"

FileName = Dir(FilePath & "\*.txt")

Do While FileName <> ""
LineCnt = 0
CellData = ""
N = FreeFile
Open FilePath & "\" & FileName For Input As #N
Do While Not EOF(N)
Line Input #N, Text
LineCnt = LineCnt + 1
If Text = "" Then Text = " " & vbLf
If LineCnt = 1 Then
Wks.Cells(R, "A").Value = Text
Else
CellData = CellData & Text
End If
Loop
Wks.Cells(R, "B").Value = FileName
Close #N
R = R + 1
FileName = Dir()
Loop

End Sub

But it only takes the first line of the txt file. I require all the content to be placed into one cell.

Many thanks in advance, i've tried so hard to do this on my own, but i just dont have the knowledge!

**Domski** · 07-26-2011, 06:55 AM

Try this:

Sub test()

Dim strFilePath As String
Dim strFileName As String

strFilePath = "C:\Users\Platinum\Desktop\ExcelMacro"

strFileName = Dir(strFilePath & "\*.txt")

Do While strFileName <> ""
    Open strFilePath & "\" & strFileName For Input As #1
    With Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
        .Value = strFileName
        .Offset(0, 1).Value = Input(LOF(1), #1)
    End With
    Close
    strFileName = Dir()
Loop

End Sub

Dom

**snb** · 07-26-2011, 07:07 AM

Avoid multiple writing operations as much as possible in VBA.
Most of the time 1 writing operation suffices.
End a pathstring always with a backslash

Sub snb()
  c00 = "C:\Users\Platinum\Desktop\ExcelMacro\"
  c01 = Dir(c00 & "*.txt")

  Do Untill c01=""
    Open c00 & c01 For Input As #1
      c02=c02 & "|" & c01 & "_" & Input(LOF(1),#1)
    close
    c01 = Dir
  Loop
  sn=split(mid(c02,2),"|")

  sheets(1).cells(1).resize(ubound(sn)+1)=application.transpose(sn)
  sheets(1).columns(1).texttocolumns ,1,,,False, false,false,false,true,"_"
End Sub

**andor** · 03-09-2012, 02:08 PM

Originally Posted by Domski

Try this:

Sub test()

Dim strFilePath As String
Dim strFileName As String

strFilePath = "C:\Users\Platinum\Desktop\ExcelMacro"

strFileName = Dir(strFilePath & "\*.txt")

Do While strFileName <> ""
    Open strFilePath & "\" & strFileName For Input As #1
    With Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
        .Value = strFileName
        .Offset(0, 1).Value = Input(LOF(1), #1)
    End With
    Close
    strFileName = Dir()
Loop

End Sub

Dom

I want to change it slightly but don't know how to do it. I have a set of textfiles which all have several rows with several fields (separated by comma or semicolon). What I want: Import all files from the given directory, write in the first column the filename (repeat it for all rows of that file), then fill the following columns with the comma-separated fields.
Is this possible? Any help or tipp appreciated,
thanks

**luke2442** · 07-26-2011, 07:10 AM

Amazing! Thankyou Dom

**TMS** · 03-09-2012, 05:13 PM

@andor:

Welcome to the Forum, unfortunately:

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.