How can I change the FileSearch code created for Excel 2003 to work for Excel 2010?

I use the attached code to update all .xls files in a folder "Files" with a current VBA module1

This code was created in Excel 2003 and the FileSearch is not supported in Excel 2007 upwards.
I get the following error:
Object doesn't support this action
The code stops at:
With Application.FileSearch

How can I change the code and achieve the same objective?

Sub TransferDataToWB()

Dim MyDir As String, strPath As String, vaFileName As Variant, I As Integer
Dim VBProj As VBIDE.VBProject
Dim VBComp As VBIDE.VBComponent

MyDir = ActiveWorkbook.Path ' current path

strPath = MyDir & "\files" ' files subdir
With Application.FileSearch
.NewSearch
.LookIn = strPath
.SearchSubFolders = False
.FileName = ".xls"
If .Execute > 0 Then
For Each vaFileName In .FoundFiles

' open the workbook
Workbooks.Open vaFileName

With ActiveWorkbook
Workbooks("Copy Module.xls").Worksheets("Configuration").Range("A1:K100").Copy Worksheets("Configuration").Range("A1:K100")

        Set VBProj = ActiveWorkbook.VBProject
        Set VBComp = VBProj.VBComponents("Module1")
        VBProj.VBComponents.Remove VBComp
         
CopyModule Workbooks("Copy Module.xls"), "Module1", ActiveWorkbook

.Save
.Close
End With
Next
End If
End With

End Sub

Open in new window

spar-kleOperations DirectorAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saurabh Singh TeotiaCommented:
Use this...

Sub TransferDataToWB()

    Dim MyDir As String, strPath As String, vaFileName As Variant, I As Integer
    Dim VBProj As VBIDE.VBProject
    Dim VBComp As VBIDE.VBComponent
    Dim fname As String

    MyDir = ActiveWorkbook.Path    ' current path


    fname = Dir(MyDir & "\" & "*.xl??")

    Do Until fname = ""


        ' open the workbook
        Workbooks.Open fname

        With ActiveWorkbook
            Workbooks("Copy Module.xls").Worksheets("Configuration").Range("A1:K100").Copy Worksheets("Configuration").Range("A1:K100")

            Set VBProj = ActiveWorkbook.VBProject
            Set VBComp = VBProj.VBComponents("Module1")
            VBProj.VBComponents.Remove VBComp

            CopyModule Workbooks("Copy Module.xls"), "Module1", ActiveWorkbook

            .Save
            .Close
        End With

        fname = Dir
    Loop

End Sub

Open in new window


Saurabh...

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
spar-kleOperations DirectorAuthor Commented:
Thanks Saurabh
100.xls is the first file

I get a run time error '1004':
"100.xls could not be found. Check the spelling of the file name and verify that the file location is correct."

It breaks at this code
        ' open the workbook
        Workbooks.Open fname
Saurabh Singh TeotiaCommented:
Use this...

Workbooks.Open MyDir & "\"  & fname

Open in new window


Saurabh...
CompTIA Security+

Learn the essential functions of CompTIA Security+, which establishes the core knowledge required of any cybersecurity role and leads professionals into intermediate-level cybersecurity jobs.

spar-kleOperations DirectorAuthor Commented:
Thanks again

I get an error '1004'
"Programmable access to Visual Basic Project is not trusted"
ProfessorJimJamMicrosoft Excel ExpertCommented:
go to File then Excel Options then Trust Center then Trust Center Setting
then click Macro Setting then tick mark "Trust access to the VBA project object model" then run the macro and error will not be there anymore.
Saurabh Singh TeotiaCommented:
That's because not of this..

Try adding application.displayalerts=false

And let me know if you get the same error again..

Saurabh...
spar-kleOperations DirectorAuthor Commented:
Unfortunately I get the same error
ProfessorJimJamMicrosoft Excel ExpertCommented:
spar-kle

i doubt if you have changed the setting as i have mentioned in my earlier comment. becuase if you change the setting, you will not get that error.
Saurabh Singh TeotiaCommented:
Sparkle,

I went through couple of links for reasons of this error and Jim is right you need to change the settings..

Here are details for the same by Microsoft for steps to follow...

https://support.microsoft.com/en-us/kb/282830

https://support.microsoft.com/en-us/kb/813969

You can do this for 2013 as well..
ProfessorJimJamMicrosoft Excel ExpertCommented:
illustrated in picture below.

2015-06-08-15-51-33-Microsoft-Excel---Bo
2015-06-08-15-51-46-Microsoft-Excel---Bo
spar-kleOperations DirectorAuthor Commented:
Thanks
That works well
Appreciate your help
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.