Marco issue Excel 2003 vs Excel 2007

[swcaputo]

I created a macro - with alot of help from people on this board...
Thanks!
The macro creates a report of certain data points in files that are saved on the computer's hard drive. It is a performance evaluation... and let's say you have 5 people - the macro creates a report showing you each person's top score, total score, avg score etc...

It works great in Excel 2003 but does nothing in 2007.
Unfortunately I don't have 2007 installed - and the end users only have a few people who have 2007 installed - so before I upgrade I thought I would ask if anyone can tell me why this wouldn't work in 2007. I would think that there must be some command that is not recognized there any longer...

Thanks in advance for your help.

Steve

Private Sub Worksheet_Activate()

Range("A3").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.ClearContents

Dim sdir, fdir As String
On Error Resume Next
Application.ScreenUpdating = False

Set fs = Application.FileSearch

With fs
sdir = "C:\2010 Performance Review"
.LookIn = sdir
.Filename = ".xls"

If .Execute > 0 Then
For i = 1 To .FoundFiles.Count
'Range("a" & i).Hyperlinks.Add Anchor:=Range("a" & i), _
Address:=.FoundFiles(i)
fdir = .FoundFiles(i)
Range("a" & i + 2).Formula = Right(fdir, Len(fdir) - Len(sdir) - 1)
Range("b" & i + 2).Formula = "='" & sdir & "\[" & Right(fdir, Len(fdir) - Len(sdir) - 1) & "]Answer Questions'!c146"
Range("c" & i + 2).Formula = "='" & sdir & "\[" & Right(fdir, Len(fdir) - Len(sdir) - 1) & "]Answer Questions'!c147"
Range("d" & i + 2).Formula = "='" & sdir & "\[" & Right(fdir, Len(fdir) - Len(sdir) - 1) & "]Answer Questions'!c148"
Range("e" & i + 2).Formula = "='" & sdir & "\[" & Right(fdir, Len(fdir) - Len(sdir) - 1) & "]Answer Questions'!c149"
Range("f" & i + 2).Formula = "='" & sdir & "\[" & Right(fdir, Len(fdir) - Len(sdir) - 1) & "]Answer Questions'!c145"

Next i
End If
End With

Application.ScreenUpdating = True

Columns("A:A").Select
Selection.Columns.AutoFit
Selection.Font.ColorIndex = 0
Columns("C:C").Select
Selection.NumberFormat = "0.00"

Range("A3").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

 

[VoG]

Application.FileSearch was removed in Excel 2007.

If you search the board you'll find alternatives.

 

[swcaputo]

Thanks for your reply...
And I did find some alternatives... but can't seem to get them to work for me...
Ultimately what I am trying to do is to look in a directory called:
C:\2010 Performance Review
and for each excel file in there to copy data from a worksheet and add it to individual rows in a new spreadsheet.

Any and all help is appreciated.
As I said I got lots of help to do this before and my VBA coding is rusty at best!
Thanks so much for your help!

Steve

 

[VoG]

This is an example using Dir

Sub ProcessFiles()
Dim MyFolder As String
Dim MyFile As String
MyFolder = "C:\example"
MyFile = Dir(MyFolder & "\*.xls")
Do While MyFile <> ""
    Workbooks.Open Filename:=MyFolder & "\" & MyFile
    '
    'do stuff
    '
    ActiveWorkbook.Close savechanges:=True
    MyFile = Dir
Loop
End Sub

 

[yomero]

I tried this code and workls great and superfast but
how can you get the fiels within the subfolders?

This is an example using Dir

Sub ProcessFiles()
Dim MyFolder As String
Dim MyFile As String
MyFolder = "C:\example"
MyFile = Dir(MyFolder & "\*.xls")
Do While MyFile <> ""
    Workbooks.Open Filename:=MyFolder & "\" & MyFile
    '
    'do stuff
    '
    ActiveWorkbook.Close savechanges:=True
    MyFile = Dir
Loop
End Sub