Clicky

We just upgraded our MS Office 2010 and one of the macro is not working!

Can someone help?

This is the error we get and in the macro, this is where it references "FileSearch".
"You entered an expression that has an invalid reference to the property FileSearch."
 With Application.FileSearch         .NewSearch         .LookIn = "g:\main\files"         .SearchSubFolders = False         .filename = "INVC"          .Execute                             
1: 2: 3: 4: 5: 6: 7: 

Select allOpen in new window

asked 12/09/2011 02:45

TeknikDev's gravatar image

TeknikDev ♦♦


11 Answers:
The FileSearch option is not available anymore in later versions. You can use as alternative the Scripting.FileSystemObject instead.

link

answered

danishani's gravatar image

danishani

You can use class from this page:
http://ivenuti.altervista.org/risorse/vba.htm
link

answered 2011-12-09 at 11:00:02

als315's gravatar image

als315

What's the syntax given my current code? Im new to VBA.....
link

answered 2011-12-09 at 11:14:45

TeknikDev's gravatar image

TeknikDev

Pls someone help me re-write. I need a solution to this soon. Thanks
link

answered 2011-12-09 at 11:14:59

TeknikDev's gravatar image

TeknikDev

Ok, I attempted to re-write using the DIR() function and failed miserably. Can someone help?

 
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
Dir("g:mainfiles") = "INVC"

to replace

With Application.FileSearch
        .NewSearch
        .LookIn = "g:mainfiles"
        .SearchSubFolders = False
        .filename = "INVC"

        .Execute
link

answered 2011-12-09 at 11:21:32

TeknikDev's gravatar image

TeknikDev

Try this to check if file exists or not:

1:
2:
3:
4:
5:
6:
7:
8:
9:
If Len(Dir("g:mainfilesINVC.*")) = 0 Then

MsgBox "Did not found file"

Else

MsgBox "found file"

End If


Note: that it will check for all filenames containing INVC.

To be more exact use the full path with file extension (for example INVC.txt).


For use of the FileSytemObject see examples in this thread:

http://www.microbion.co.uk/developers/fso.htm

link

answered 2011-12-09 at 11:29:20

danishani's gravatar image

danishani

Hi,

Try this:

Create this function.

Function FileList(fldr As String, Optional fltr As String = "*.*") As Variant
    Dim sTemp As String, sHldr As String
    If Right$(fldr, 1) <> "\" Then fldr = fldr & "\"
    sTemp = Dir(fldr & fltr)
    If sTemp = "" Then
        FileList = False
        Exit Function
    End If
    Do
        sHldr = Dir
        If sHldr = "" Then Exit Do
        sTemp = sTemp & "|" & sHldr
     Loop
    FileList = Split(sTemp, "|")
End Function

Then test using this function:
Function TestIt()
    'replace Application.FileSearch with the code below.
   
    Dim vList As Variant, i As Integer, vFileNameFound As String
    vList = FileList("C:\Windows", "*.exe")
    If TypeName(vList) <> "Boolean" Then
        For i = LBound(vList) To UBound(vList)
            vFileNameFound = "C:\Windows" & vList(i)
            Debug.Print vFileNameFound
        Next
    End If
   
End Function

Regards,

Bill
link

answered 2011-12-09 at 11:40:14

BillDenver's gravatar image

BillDenver

Hi Bill, thanks so much for helping...but I do not want to create a brand new function or stored proc to do the job. I was hoping to just embed the snippet of code in the existing body so I don't have to tweak much just to search in the folder.

Could you helP?
link

answered 2011-12-09 at 11:40:18

TeknikDev's gravatar image

TeknikDev

Hi,

As danishani states it is not available.  You cannot use something that is not there.  Sorry,

Bill
link

answered 2011-12-09 at 11:52:06

BillDenver's gravatar image

BillDenver

Try this example with class module from Ivan Venuti (http://ivenuti.altervista.org). Some errors were corrected. See code for button in Form1. You should change only Object type.
link

answered 2011-12-09 at 13:00:35

als315's gravatar image

als315

Thanks, I'll try at work tomorrow. Hopefully that will work.

I realized the Application.FileSearch had a .FILECOUNT property as well.

Hopefully that didn't change.
link

answered 2011-12-09 at 23:38:05

TeknikDev's gravatar image

TeknikDev

Your answer
[hide preview]

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Tags:

×1
×4
×16
×83

Asked: 12/09/2011 02:45

Seen: 247 times

Last updated: 12/15/2011 05:23