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 C:DTOOLSRISK45 while on the laptop they are in C:Program FilesPalisadeRISK45. In order to use @Risk through VBA I need to set a reference to Risk.xla – 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 
). 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
ThisWorkbook.VBProject.References.AddFromFile
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…
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
I get the following results in the message box:
5 Copies Found C:DTOOLSRISK45Risk.xla C:DTOOLSRISK45RISKINTL.xla C:DTOOLSSYSTEMRISKRepS.xla C:DTOOLSSYSTEM4.5.3 BackupRISKRepS.xla C:DTOOLSSYSTEM4.5.4 BackupRISKRepS.xla
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…
