Check Workbook Exists
Find bottlenecks in your Excel workbooks
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: Check Workbook Exists

  1. #1
    New Member
    Join Date
    Feb 2002
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    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

  2. #2
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  3. #3
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    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


User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com