Application.FileSearch in Office 2010

[adygelber]

Hello everyone,

Until few days ago I used the below mentioned script to update a file from a specific location under Office 2003. Now, under Office 2010 is no longer working.
Can anyone help me to adapt it to the new Office version?

Thanks in advance!

Sub checkforupdates()
    Dim fs As FileSearch, ws As Worksheet, i As Long
    Set fs = Application.FileSearch
    With fs
        .SearchSubFolders = False
        .FileType = msoFileTypeExcelWorkbooks
        .LookIn = "c:\"
        If .Execute > 0 Then
            Set ws = ActiveSheet
            For i = 1 To .FoundFiles.Count
                ws.Cells(i, 31) = Mid$(.FoundFiles(i), InStrRev(.FoundFiles(i), "\") + 1)
            Next
        Else
            MsgBox "No files found"
            Exit Sub
        End If
    End With
    
    If Range("AD1").Value >= Range("AF1").Value Then
    
    MsgBox "                         CONGRATULATIONS!" & vbNewLine & _
    "You are using the latest version of IBAN Check Utility."
    
    Else
    
    Dim varResponse As Variant
    varResponse = MsgBox("There is a new version available. Do you want to download it?", vbYesNo, "New version available")
    If varResponse = vbYes Then
    GoTo download
    ElseIf varResponse = vbNo Then
    GoTo endscript
    End If
    
download:
    Dim FSO As Object
    Dim FromPath As String
    Dim ToPath As String
    
    Dim objWSHShell As Object
    Dim strSpecialFolderPath
    
    Set objWSHShell = CreateObject("WScript.Shell")
    SpecialFolderPath = objWSHShell.SpecialFolders("Desktop")
    FromPath = "c:\"
    ToPath = SpecialFolderPath
    If Right(FromPath, 1) = "\" Then
        FromPath = Left(FromPath, Len(FromPath) - 1)
    End If
    If Right(ToPath, 1) = "\" Then
        ToPath = Left(ToPath, Len(ToPath) - 1)
    End If
    Set FSO = CreateObject("scripting.filesystemobject")
    If FSO.FolderExists(FromPath) = False Then
        MsgBox FromPath & " doesn't exist"
        Exit Sub
    End If
    FSO.CopyFolder Source:=FromPath, Destination:=ToPath
    
    MsgBox "You can find the new file on your Desktop."
    End If
    
    Exit Sub
    
endscript:
    
    Exit Sub
    
End Sub

[Kenneth Hobs]

Use fso. Here is an example I recently posted. http://www.vbaexpress.com/forum/show...or-please-help!

[adygelber]

Thank you for your quick answer, but I encounter difficulties in implementing the code you exemplified in that thread, as the problem is somehow different and my experience is a little bit limited.
Practically, I have a file which ends with 3 digits (e.g. Book 100). Occasionally, I put updated versions, with the three digits incremented by 1 in a specific folder, for example c:\ (e.g. Book 101).

My code, in the most simple way compares the digits from the current version with the digits from the new version founded in C:\ . But with the new implementations is no longer able to do it.

For ease of understanding, I attached a sample file which basically has to do the required work in Office 2003. Can you help me to fix the code so that the file works also on the Office 2010?

Thank you and best regards!

[Kenneth Hobs]

While seemingly more complicated, I think your best option is to use a FileSearch-like class.

If you know how to use the old FileSearch, using a class method like it is very similar. Once you add the classes, and the Microsoft Scripting Runtime in VBE's Tools > References, you can try some tests using it. You have to add that as it uses early binding to fso whereas you used late binding in your fso code.

With the file below open and yours, just drag and drop the class from it to yours. Then add the line of code to Dim that class as New. The Module mTest shows how to Dim it and how to use the class. It is very similar to the old FileSearch.

I posted it here for you: https://app.box.com/s/dzd5a74mryb9u1907pob

There are some things in your code that I had some issues with. Basically, you are parsing out the number part of the base filename and comparing that to a number in the worksheet. You are getting all of the files. I am not sure why you don't just get the first one.

[adygelber]

Thank you very much Mr. Hobs. I managed to make it work adding the Classes Modules and adapting a little bit the code from the search module.