Link to home
Start Free TrialLog in
Avatar of spar-kle
spar-kleFlag for United Kingdom of Great Britain and Northern Ireland

asked on

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

ASKER CERTIFIED SOLUTION
Avatar of Saurabh Singh Teotia
Saurabh Singh Teotia
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of spar-kle

ASKER

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
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks again

I get an error '1004'
"Programmable access to Visual Basic Project is not trusted"
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
That's because not of this..

Try adding application.displayalerts=false

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

Saurabh...
Unfortunately I get the same error
Avatar of Professor J
Professor J

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.
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..
illustrated in picture below.

User generated image
User generated image
Thanks
That works well
Appreciate your help