VBA Help

[curtus123]

I am relatively new to Visual Basic, so I am hoping someone can help me out. I have been trying to go through some data to find the minimum and maximum values of a parameter in mutpile files. These files are all formatted the same- the data I am looking at is in the same column for each file. I recorded a macro that opens the first data file, finds the min and max, displays it on a new sheet, and then closes the data file. Since I have over 600 of these data files to go through, I was hoping I could have the macro do it. I do not know how to set a loop that will go through each file and place the min/max in the following cell. Can anyone help me out?

[Bob Phillips]

Sub ProcessFiles()
Dim FSO As Object
Dim sFolder As String
Dim fldr As Object
Dim Folder As Object
Dim file As Object
Dim Files As Object
Dim this As Workbook
Dim cnt As Long
Dim i As Long

Set FSO = CreateObject("Scripting.FileSystemObject")

Set this = ActiveWorkbook
sFolder = "C:\MyTest" '<<<<<<<< change to suit
If sFolder <> "" Then
Set Folder = FSO.GetFolder(sFolder)

Set Files = Folder.Files
cnt = 1
For Each file In Files

If file.Type Like "*Microsoft*Excel Worksheet*" Then

Workbooks.Open Filename:=file.Path
this.Worksheets.Add.Name = "File" & cnt
With ActiveWorkbook

'>>>>>>>>>>>>>>>>
'your code to extract data goes here
'<<<<<<<<<<<<<<<<<

.Close
End With

cnt = cnt + 1
End If
Next file
End If ' sFolder <> ""
End Sub

[curtus123]

Thank you for your help. Here is the macro I have (from recording as well as a few things I added in). I want the intfilenum to increase one at a time, extract the data from the file, store it in the new spreadsheet, then move onto the next one. Any ideas?


Sub Analysis

Range("A1").Select
ActiveCell.FormulaR1C1 = "Reading #"
Range("B1").Select
ActiveCell.FormulaR1C1 = "Min "
Range("C1").Select
ActiveCell.FormulaR1C1 = "Max"
Range("A2").Select
Do

intfilenum = 1
intfilenum = Format(intfilenum, "000000")



ActiveCell.FormulaR1C1 = "1"
Range("B2").Select
Workbooks.Open Filename:= _
"C:\RDG-" & intfilenum & ".csv"
Windows("book1.xlsm").Activate
ActiveCell.FormulaR1C1 = _
"=MIN(RDG-" & intfilenum & ".csv'!C54,'RDG-" & intfilenum & ".csv'!R1C54)"
Range("C2").Select
ActiveCell.FormulaR1C1 = _
"=MAX('1B_EMU-CERT~RDG-" & intfilenum & ".csv'!C54,'RDG-" & intfilenum & ".csv'!R1C54)"
Range("A3").Select
Windows("RDG-" & intfilenum & ".csv").Activate
ActiveWindow.Close

Loop Until filenum = 219

End Sub

[Bob Phillips]

Yes I have, and I gave them to you in my previous post.