Import data from multiple txt files into one xls file
 

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!!

Import data from multiple txt files into one xls file
 

Certainly,

http://somethinglikeant.110mb.com//e...ile_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

Import data from multiple txt files into one xls file
 

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

"excel-ant" wrote:

Certainly,

http://somethinglikeant.110mb.com//e...ile_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

Import data from multiple txt files into one xls file
 

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

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Steve" wrote in message ups.com...
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!!