PDA

View Full Version : [SOLVED] Application.FileSearch in Office 2010



adygelber
05-07-2014, 08:09 AM
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:\ (file://\\10.230.30.15\Telefoni\ICU)"
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:\ (file://\\10.230.30.15\Telefoni\ICU)"
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
05-07-2014, 08:20 AM
Use fso. Here is an example I recently posted. http://www.vbaexpress.com/forum/showthread.php?49572-FileSearch-VBA-error-please-help!

adygelber
05-07-2014, 12:58 PM
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
05-07-2014, 02:00 PM
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
05-08-2014, 12:01 PM
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.