PDA

View Full Version : Excel VBA Error 445 in 2007/10 but 2003 is fine



gunny2k9
10-17-2012, 02:52 AM
MORNING ALL !!

ok so i have been pasted this vba project it was written in excel 2003 and works fine however in 2007 and 2010 it errors on a line of code.... i m guess due to changes in vba...


Do Until x = 23
file = Sheets("CheckSheet").Cells(x, 1).value
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = False
Set objsearch = objExcel.FileSearch
objsearch.LookIn = SourceFolderName
objsearch.SearchSubFolders = False
objsearch.fileName = file
objsearch.Execute
For Each strfile In objsearch.FoundFiles
If strfile <> "" Then
'Sheets("CheckSheet").Cells(31, 2) = Sheets("CheckSheet").Cells(31, 3) + 1
Sheets("CheckSheet").Cells(x, 2) = "File Present"
End If
Next
objExcel.Quit
x = x + 1
Loop
x = 4
Do Until x = 23


basicly theres a Sheet with filenames on in column a that the user puts in a folder that they select and it checks if all files are in the folder and puts Present or Not Present in column b

2003 works perfect but 2007 and 2010 errors with Runtime error 445 Object doesnt support this method and debug highlights Set objsearch = objExcel.FileSearch

any ideas ?

Aflatoon
10-17-2012, 03:52 AM
The Filesearch object is deprecated from 2007 onwards so won't work. You'll need to use an alternative such as Dir or the FileSystemObject - there are examples all over the place. :)