Check Workbook Exists

bobeuk

New Member
Joined
Feb 18, 2002
Messages
10
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
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,249
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top