Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    268
    Thanks
    0
    Thanked 1 Time in 1 Post

    FileSearch error (XL 97 & 2K)

    I have to work with the Excel add-in "@Risk" (that's not the problem), and some of my work involves accessing @Risk through VBA (and that's not the problem, either). The problem arises when I try to move spreadsheets between my desktop and my laptop. The desktop is running XL97 on Win 2K, while the laptop is running XL2K on Win98SE. The locations of the @Risk files are different - on the desktop they are in <font color=blue>CTOOLSRISK45</font color=blue> while on the laptop they are in <font color=blue>C:Program FilesPalisadeRISK45</font color=blue>. In order to use @Risk through VBA I need to set a reference to <font color=blue>Risk.xla</font color=blue> - that works, but when I move files between the two machines the different locations of Risk.xla means I get a "missing reference" error and other good stuff. When I am using the files constantly I usually remember to re-set the reference - but when I pick them up after a break for a month or two, I forget, and it is a pain. There is also the possibility of running into the same problem if I send them to other users after I have been using them on my laptop, although fortunately that hasn't happened yet

    I ran into a routine to automatically detect the references set for a workbook and add specified references and delete broken ones, so I though it would be nice to include that in a module to see if it solved my problems (at least these problems <img src=/S/grin.gif border=0 alt=grin width=15 height=15>). The first issue was that the routine adds the reference based on the GUID. That makes sense, since you can access it via the registry, but Risk.xla doesn't have a GUID, so I though I would have to rely on adding the reference through
    <font color=blue>ThisWorkbook.VBProject.References.AddFr omFile</font color=blue>
    this is frowned upon, according to Messrs Bullen, Green, Bovey and Rosenberg (Excel 2002 VBA, p 863) but I can't see any way around it - suggestions are most welcome, although that's not why I am posting.

    So, I happily launched into FileSearch, thinking that I could find the one instance of Risk.xla then set a string variable to capture the file location then add the reference that way...

    I am not getting good results from FileSearch using the ".FileName" property to restrict the search. I did a search (ahem...) here in the lounge, but didn't find anyone with the same problem. When I run the following code to test FileSearch...
    <pre>Sub FindRiskReference()

    Dim FS As Office.FileSearch
    Dim vaFileName As Variant
    Dim i As Long
    Dim iCount As Long
    Dim stMessage As String

    Set FS = Application.FileSearch

    With FS
    .NewSearch
    .LookIn = "C:"
    .SearchSubFolders = True
    .FileName = "Risk.xla"
    iCount = .Execute

    stMessage = Format(iCount, "0 ""Copies Found""")

    For Each vaFileName In .FoundFiles
    stMessage = stMessage & vbCr & vaFileName
    Next vaFileName

    End With

    MsgBox stMessage

    End Sub
    </pre>

    I get the following results in the message box:
    <pre>5 Copies Found
    CTOOLSRISK45Risk.xla
    CTOOLSRISK45RISKINTL.xla
    CTOOLSSYSTEMRISKRepS.xla
    CTOOLSSYSTEM4.5.3 BackupRISKRepS.xla
    CTOOLSSYSTEM4.5.4 BackupRISKRepS.xla
    </pre>

    I don't think it should be returning the last four files listed - they exist, but they don't match the FileName setting - seems that VBA is interpreting the FileName setting as if it includes wildcards. Is there any way to force FileSearch to treat the FileName setting strictly, rather than the way it is doing now? There is a ".Match Text Exactly" property in FileSearch, but it refers to instances where you are searching for specified text in a file...

  2. #2
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Ankeny, Iowa, USA
    Posts
    298
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: FileSearch error (XL 97 & 2K)

    Maybe I'm way off base on this, and if so, please ignore my post <img src=/S/noevil.gif border=0 alt=noevil width=25 height=17>

    Could you possibly just make a copy of risk.xla and put that file in the same directory on both machines?

  3. #3
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    268
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: FileSearch error (XL 97 & 2K)

    Hmmm.....

    Always overlooking the obvious, I guess. I am not sure if that will work - getting the software installed and registered was difficult; matching serial numbers, etc. I assumed that a bunch of that sort of stuff was stored in the registry, and moving files around would give us problems. Perhaps not.

    I am still wondering about the odd behaviour in FileSearch, though.

    Thanks for your suggestion (I might just try that on someone else's computer)

Posting Permissions

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