Application.FileSearch not in XL2007?

[jmthompson]

Good morning,
Several months ago, with the help of several MRExcel buddies, I crafted some complicated macros. The following is one step from these macros. This step looks for a file in a specific folder using Application.FileSearch. This application does not appear to be supported in 2007. Is there a workaround? I can provide the full macro, if needed.

Regards,

Function OpenCsv(MyCSVPath As String, CSVFileName As String, CurBook As Worksheet, _<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
    BValue As String, SaveAsPath As String) As Boolean<o:p></o:p>
'------------------------------------------<o:p></o:p>
'DEFINE VARIABLES<o:p></o:p>
'------------------------------------------<o:p></o:p>
Dim SrceBook As Workbook<o:p></o:p>
Dim lastRow As Long, lastRowWFO As Long<o:p></o:p>
'------------------------------------------<o:p></o:p>
'SET SPECIFIC HANDLER<o:p></o:p>
'------------------------------------------<o:p></o:p>
On Error GoTo Err_OpenCsv<o:p></o:p>
'------------------------------------------<o:p></o:p>
'OPEN PARSED FILE<o:p></o:p>
'------------------------------------------<o:p></o:p>
With Application.FileSearch<o:p></o:p>
    .NewSearch<o:p></o:p>
    .LookIn = MyCSVPath<o:p></o:p>
    .FileType = msoFileTypeAllFiles<o:p></o:p>
    .Filename = CSVFileName<o:p></o:p>
    .Execute<o:p></o:p>
     Set SrceBook = Workbooks.Open(.FoundFiles(1))<o:p></o:p>
End With<o:p></o:p>
'------------------------------------------<o:p></o:p>
'RESET HANDLER<o:p></o:p>
'------------------------------------------<o:p></o:p>
On Error GoTo 0<o:p></o:p>
'------------------------------------------<o:p></o:p>
'SET PASTE ROW + LAST ROW IN SOURCE FILE<o:p></o:p>
'------------------------------------------<o:p></o:p>
lastRowWFO = CurBook.Cells(Rows.Count, "A").End(xlUp).Row + 1<o:p></o:p>
SrceBook.ActiveSheet.Name = "Sheet1"<o:p></o:p>
lastRow = SrceBook.Worksheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row<o:p></o:p>
JobDate = SrceBook.Worksheets("Sheet1").Cells(3, 1)<o:p></o:p>
JobNum = SrceBook.Worksheets("Sheet1").Cells(2, 1)<o:p></o:p>
CurBook.Activate<o:p></o:p>
Dim c As Range<o:p></o:p>
        For Each c In Range([A4], Cells(Rows.Count, "A").End(xlUp))<o:p></o:p>
            If c = JobDate And c.Offset(, 1) = BValue And c.Offset(, 2) = JobNum Then GoTo Dupe<o:p></o:p>
        Next c

<o></o>

 

[Andrew Poulsom]

Try replacing:

With Application.FileSearch<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
    .NewSearch<o:p></o:p>
    .LookIn = MyCSVPath<o:p></o:p>
    .FileType = msoFileTypeAllFiles<o:p></o:p>
    .Filename = CSVFileName<o:p></o:p>
    .Execute<o:p></o:p>
     Set SrceBook = Workbooks.Open(.FoundFiles(1))<o:p></o:p>
End With<o:p></o:p>

with:

Set SrceBook = Workbooks.Open(MyCSVPath & "\" & CSVFileName)<o:p></o:p>

 

[ThomasB]

Hi Jim

Have a look at this link

http://www.ozgrid.com/forum/showthread.php?t=71409

Rgs

Thomas