Import data from multiple txt files into one xls file

[Steve]

Hi all. I have to import about 40 text files into excel. And I have
to drop the data into a single worksheet, one entry under the other.
Is there a way to select the txt files from a single folder and import
into excel dropping one under the other, so I have one giant data
column?

The data in the txt file is a simple string...no comma or tab
delimited. So the data can simply be dropped in column A. Thanks in
advance!!

 

[excel-ant]

Certainly,

http://somethinglikeant.110mb.com//excel-ant/examples/Multiple_Text_File_Import.xls

Just tried this and it worked a treat. I had 20 text files setup in
test.

-------------------------------------------------------------
Sub Import_Multiple_Text_Files()
Dim FileS As FileSearch
Dim F As Variant
Dim x As Integer
'switch calculation off to speed up macro
Application.Calculation = xlManual
'Pick up file path information
qfolder = [B5]
Set FileS = Application.FileSearch
With FileS
.NewSearch
.Filename = "*"
.LookIn = qfolder
.SearchSubFolders = True
.Execute
End With
x = 1
Sheets("Data").Select
For Each F In Application.FileSearch.FoundFiles
Open F For Input Access Read As #1
Do Until EOF(1)
Line Input #1, qdata
If qdata <> "" Then
Cells(x, 1) = qdata
x = x + 1
End If
Loop
Close #1
Next F
End Sub
--------------------------------------------------------------------------------

You will have to setup your folder by typing it out int the Macro tab.
Let me know if you need any further tweaks or assistance.

http://www.excel-ant.co.uk

 

[Guest]

Here is code that doesn't use filesearch.

Sub Import_Multiple_Text_Files()

Dim F As Variant
Dim x As Integer

Const MyPath = "c:\temp\"

first = True

RowCount = 1
Do
If first = True Then
Filename = Dir(MyPath & "*.txt")
first = False
Else
Filename = Dir()
End If

If Filename <> "" Then

Open (MyPath & Filename) For Input Access Read As #1
Do Until EOF(1)
Line Input #1, qdata
If qdata <> "" Then
Cells(RowCount, 1) = qdata
RowCount = RowCount + 1
End If
Loop
Close #1
End If
Loop While Filename <> ""
End Sub

 

[Ron de Bruin]

See also
http://www.rondebruin.nl/csv.htm