Looping through files

[Mark K]

First, a quick description of what I'm trying to do:

I have a number of csv files in a directory. The ones I'm interested in are all named in the format "MyFile(x).csv" where "x" is a number from 1 to the number of files (generally 5 to 10). What I wish to do is:

Open the first file (MyFile(1).csv).
Select the entire contents of the file and paste to the sheet "Temp" in the active workbook.
The csv file can now be closed.
Then use VBA to manipulate/format the data and select a range for cut and paste.
I then need to paste the data to a sheet named MyFile(1) in the active workbook.
Loop and do the same on the rest of the files.

I can do this no problems on a single, specified file. But how do I set it up to loop through the required files, changing the number for the file to be opened and the target sheet for the cut and paste?

I figure I need to get a count on the number of files to be accessed and set a variable using this (1 to numFiles) then use this variable to do what I need. Just don't know how.

TIA

[mudraker]

Here are 2 examples

The 1st one gives full control to which file you open 1st - from Myfile(1).csv to last numbered file

Sub ddd1()
Dim iCnt As Integer
Dim sFname As String
Dim i4File As Integer
sFname = Dir("c:\MyFolder\MyFile(*.csv")
Do Until sFname = ""
iCnt = iCnt + 1
Loop
For i4File = 1 To iCnt Step 1
sFname = "MyFile(" & i4File & ").csv"
Workbooks.Open Filename:="c:\MyFolder\" & sFname
your code here
Next i4File
End Sub


Sub ddd2()
Dim sFname As String
sFname = Dir("c:\MyFolder\MyFile(*.csv")
Do Until sFname = ""
Workbooks.Open Filename:=sFname
your code here
Loop
End Sub

[Mark K]

Thanks mudraker, should be able to work something with them.