Check Workbook Exists

[bobeuk]

I am using the code below ot check whether multiple workbooks exist. I have a list of titles (RTM.xls, GSLL.xls, GTM.xls, SLL.xls etc) and cycle these through this sub. If the workbook doesn't exist it creates a new one. The title is in the code as the variabel aRea

It works fine for 21 titles until it gets to SLL. It then seems to get confused between GSLL and SLL. It says that SLL exists when it doesn't. If I pull GSLL out of the subdirectory, it creates a new GSLL!

Is this code right? Do I have to put something in to specify an exact match? If it;s right then I guess it must be something else in my code...

Many Thanks

John

Sub doesworkbookexist()
Dim i As Integer
With Application.FileSearch
.LookIn = Pathname
.Filename = aRea
If .Execute = 0 Then ' no workbook exists
errornumber = 2
Else 'there is a workbook
errornumber = 1
End If
End With

End Sub

 

[Ivan F Moala]

Try adding

.NewSearch

eg
With FileSearch
.NewSearch
.LookIn = sDir

I believe there is a problem ??
with application filesearch ?

otherwise try Dir("your fullpath to file")


Ivan

 

[Dave Hawley]

Hi bobeuk

Settings are retained between searches so you should always (as Ivan sais) use NewSearh as show below from the help.

More to the point though you need the MatchTextExactly Property set to True!


With Application.FileSearch
.NewSearch
.LookIn = "C:My Documents"
.SearchSubFolders = True
.FileName = "Run"
.MatchTextExactly = True
.FileType = msoFileTypeAllFiles
End With