spar-kle
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?
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks again
I get an error '1004'
"Programmable access to Visual Basic Project is not trusted"
I get an error '1004'
"Programmable access to Visual Basic Project is not trusted"
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
That's because not of this..
Try adding application.displayalerts= false
And let me know if you get the same error again..
Saurabh...
Try adding application.displayalerts=
And let me know if you get the same error again..
Saurabh...
ASKER
Unfortunately I get the same error
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.
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..
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..
ASKER
Thanks
That works well
Appreciate your help
That works well
Appreciate your help
ASKER
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