Application.FileSearch not working in Excel 2007

[mrugesh]

I had a friend of mine create a macro for me which consolidates number of .csv files into a single file. Now this macro was created in Excel 2003 & since I switched to Excel 2007, I'm unable to use this macro. It seems that there is some Application.Filesearch which is not compatible with Excel 2007. I understand that some FileObject command is to be used for a workaround. However, I do not know how to go about this. Can someone help me here? Given below is the code in my macro.

Sub cons()
Dim fs As Object
Dim source As Workbook
Dim filepath As String
Dim output As Worksheet
Dim Total_Files As Variant


Set fs = Application.FileSearch
filepath = ThisWorkbook.Sheets("Variables").Cells.Find("Filepath:").Offset(0, 1)
Set output = ThisWorkbook.Sheets("Output")

Application.ScreenUpdating = False

Application.DisplayAlerts = False

output.Range("A1:IV50000").ClearContents

With fs
.NewSearch
.LookIn = filepath
.Filename = "*.csv"


If .Execute() > 0 Then

For i = 1 To .FoundFiles.Count

Set source = Workbooks.Open(.FoundFiles(i))
Range("A1").Select
ActiveCell.CurrentRegion.Copy
output.Activate
Range("A65536").End(xlUp).Offset(1, 0).Select
ActiveCell.PasteSpecial xlPasteValuesAndNumberFormats

source.Close False

Next i

Else
MsgBox "File(s) not found.", vbExclamation

End If

output.Copy

output.Range("A1:IV50000").ClearContents

ActiveWorkbook.SaveAs filepath & "Consolidated_File" & ".xls"
ActiveWorkbook.Sheets(1).Range("A1").Select

'ActiveWorkbook.Close SaveChanges:=False


End With

'output.Range("A1").Select


Application.ScreenUpdating = True

Application.DisplayAlerts = True

End Sub

 

[Scott Huish]

Application.Filesearch does not work in 2007. My understanding is that Microsoft thought people didn't use this anymore. Where they got this idea is hard to say.

Have a look at the Dir function.

 

[bboysen]

I had the same problem and now I just use the CMD prompt in windows.

It works nice and this is how i do mine>

copy *.csv all.csv

It might not be as nice as the excel version but it works.

 

[mrugesh]

Thanks for replying Hotpepper... I did read about DIR function when I googled this issue. However, problem is that I don't have any clue about VBA. This code was written by a friend.