120 CSV files need to be pulled to 1 sheet

[RobE]

I receive e-mails from a customer that each have 1 CSV file attached. To date I have 120 received.
I am looking for an easy way to save all the attachements and then open them all and copy them to a single sheet.
The columns are always the same and there will never be any blank lines until the last row but the number of lines varies with each one.

Any ideas - even if I have to manually save them all to one location and all I can macro is the opening and collating to a single sheet.
Thanks
Rob

[Tom Schreiner]

Hi Rob.
How many columns?
A to ?
Thanks,
Tom

[ This Message was edited by: TsTom on 2002-04-22 05:37 ]

[RobE]

Here is a sample file
Always 12 columns, varying rows from 2 to 500ish

Sample data
5291424 Business Mr Fred Smith ABC CO 6-8 Park St Luton London NW11 1TQ
5292586 Business Mr David Smith DEF Co 73 Blackwood Rd Tamworth London B3 1RB
5283400 Business Mr Fred Smith HIJ Co 32 Salisbury Rd Dover London HP10 0BX
5280321 Business Mrs David Smith XYZ Co Grovenor House 11 St Pauls Square Birmingham London B77 1JP
5293390 Business Mrs Stephen Smith AAA Co 10 Glory Mill La Wooburn Green High W London CT16 1EU

[Tom Schreiner]

Try this out.
Place the workbook containing this
macro in the same folder in which
all of your CSV files have been dumped.

Sub Open_CSV_Files()
Dim Fs, i, FileNum, PlaceRow, ArrayCntr
Dim Data_CSV(1 To 12)
Set Fs = Application.FileSearch
PlaceRow = 2
Application.ScreenUpdating = False
With Fs
.LookIn = ActiveWorkbook.Path
.Filename = "*.csv"
If .Execute Then
For i = 1 To .FoundFiles.Count
FileNum = FreeFile
Open .FoundFiles(i) For Input As #FileNum
Do Until EOF(FileNum)
PlaceRow = PlaceRow + 1
Input #FileNum, Data_CSV(1), Data_CSV(2), Data_CSV(3), Data_CSV(4) _
, Data_CSV(5), Data_CSV(6), Data_CSV(7), Data_CSV(8) _
, Data_CSV(9), Data_CSV(10), Data_CSV(11), Data_CSV(12)
For ArrayCntr = 1 To 12
Cells(PlaceRow, ArrayCntr) = Data_CSV(ArrayCntr)
Next
Loop
Close #FileNum
Next
End If
End With
Application.ScreenUpdating = True
End Sub

Tom

[Peter100]

An alternative is to use ASAP utilities Add in which does this for you by simply selecting the files you want.

http://www.asap-utilities.com