Copying CSV-XLS, large amounts-different folders

[Yarroll]

Hello!

I run the following macro to copy large amounts of data from CSV to XLS
files:


Sub Tester3()
Dim nam_0 As String, nam_1 As String, nam_2 As String
Dim fil As Workbook
Dim i As Integer
Dim myVal As Variant

With Application.FileSearch

.NewSearch
.LookIn = "c:\windows\pulpit\data"
.SearchSubFolders = False
.FileName = "*.csv"

If .Execute() > 0 Then
myVal1 = Application.InputBox("Which sheet this time: ")
For i = 1 To .FoundFiles.Count

nam_1 = .FoundFiles(i)
nam_0 = Left(nam_1, InStr(1, nam_1, ".csv") - 1)
nam_2 = nam_0 & ".xls"

If Dir(nam_2) = "" Then
MsgBox ("No file " & nam_2)
Else
Set fil = Workbooks.Open(nam_2)
If myVal1 <> False Then
With Workbooks.Open(nam_1)
.Worksheets(1).Cells.Copy Destination:= _
plik.Worksheets(myVal1).Cells
.Close
End With
fil.Save
End If
fil.Close
End If
Next
Else
MsgBox "No files to process!"
End If
End With
Set fil = Nothing
End Sub

The macro works alright. All it takes is every now and then replace one set
of CSVs with another and then type the new sheet name. However, since
there's so many files to process, it seems it'll take ages to complete.
So I was wondering if it's possible to build up: put in additional MyVal
sheets (MyVal2, MyVal3 etc., no problem here) and - that's the difficult
part - make the macro use CSV files frm additional folders? I can hardly
keep these additional CSV files in the same folder, for the names are
identical. The whole point is to be able to leave the macro running
unattended a little longer (at night, etc.)

Any help much appreciated.

Best, Yarroll

 

[Tom Ogilvy]

.NewSearch
.LookIn = "c:\windows\pulpit"
.SearchSubFolders = True
.FileName = ".csv"

Put your folders below a common directory.