detmine location of file for reference

[Paul Sansom]

Hi
I have a file that uses the solver function called from a reference. This works well.

The issue I see is that my users can have Solver in different locations e.g.
ForExcel 32bit program

• The solver can resides in sub-dir
  C:\Program Files (x86)\Microsoft Office\Office16\Library\SOLVER\
  C:\Program Files (x86)\Microsoft Office\Office15\Library\SOLVER\
  

Excel 64bit program

• The solver can reside in sub-dir
  C:\Program Files\Microsoft Office\Root\Office16\Library\SOLVER\

Is there a way to locate SOLVER.XLAM file independent of Excel installation that the file locates the .xlam file ad then I can load it using current load code

For Info

Check if Solver is loaded

Sub Check_Solver_Reference()
' Check if not added then auto added[/FONT]
[FONT="Times New Roman"]'''    Sheet11.Range("F80").ClearContents
    Sheet11.Range("Win_64_32").ClearContents[/FONT]
[FONT="Times New Roman"]    With ThisWorkbook.VBProject
        For Each Ref In .References
            If Ref.Name = "Solver" Then Sheet11.Range("F80") = True
        Next
    End With
    
'    'Check is Windows and Excel are 64 or 32 bit.
    [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=If]#If[/URL]  Win64 Then
        Sheet11.Range("Win_64_32") = 64
    [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ElseIf][URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=Else]#Else[/URL] If[/URL]  Not Win64 Then
        Sheet11.Range("Win_64_32") = 32
    [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=End]#End[/URL]  If
    [/FONT]
[FONT="Times New Roman"]    If Not Sheet11.Range("F80") Then
        UserForm7.Show
    Else
        Application.StatusBar = "Solver Reference already available"
    End If
    
        
End Sub

There is some unnecessary code here but I also check for 32/b4bit version for other uses.

Code to load solver reference (BUT LOCATION IS CURRENTLY HARD CODED a F92 or F93)

Private Sub CommandButton1_Click()
    [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=If]#If[/URL]  VBA7 And Win64 Then
        Sheet11.Range("Win_64_32") = 64
        wintype = Sheet11.Range("F92")
        ThisWorkbook.VBProject.References.AddFromFile wintype
        Application.StatusBar = "Solver Reference was added"
        Sheet11.Range("F80") = True
        
    [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=Else]#Else[/URL] 
        Sheet11.Range("Win_64_32") = 32
        wintype = Sheet11.Range("F93")
        ThisWorkbook.VBProject.References.AddFromFile wintype
        Application.StatusBar = "Solver Reference was added"
    
    [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=End]#End[/URL]  If
    UserForm7.Hide
    'Calculate
    'Application.Wait (Now + TimeValue("00:00:10"))
    DoEvents
    Application.ActiveWorkbook.Save
        
End Sub

 

[Tim_Excel_]

This UDF checks if Excel (so not the entirety of Windows) is either 64-bit or 32-bit. This corrects the room of error you create when you check for Windows. Users with a 64-bit Windows installation may still use 32-bit Excel.

Private Function m_IsExcelx64(ExcelApp As Object) As Boolean    Dim l As Long
    
    l = -1
    On Error Resume Next
    l = ExcelApp.Hinstance
    On Error GoTo 0
    
    If l = -1 Then
      m_IsExcelx64 = True
    Else
      m_IsExcelx64 = False
    End If
End Function

Used with

Sub testbit()
    Dim is64bit as Boolean
    is64bit = m_IsExcelx64(Application)
    
    If is64bit = True then
    'do something
    Else
    'do something else
    End If

End Sub

 

[Paul Sansom]

Thanks Tim, but I dont believe that solves the problem.
I have at least 2 users that use 32bit , and they have SOLVER.XLAM at different locations. My 64 bit Excel has Solver at another (3rd) location. I want the file to run without the need to determine bit version, so that wherever the solver file is located it will idtify its location.
I believe your solution just re-identifies bit version and not location of Solver.xlam file
Cheers Paul

 

[Tim_Excel_]

Why don't you want to specify the bit version? It would only make it easier to locate the file. That is, assuming there are but three possible file locations.

Sub testbit()
    Dim is64bit as Boolean
    is64bit = m_IsExcelx64(Application)
    
    If is64bit = True then
        ThisWorkbook.VBProject.References.AddFromFile "C:\Program Files\Microsoft Office\Root\Office16\Library\SOLVER\SOLVER.XLAM"
    Else
        If Dir("C:\Program Files (x86)\Microsoft Office\Office16\Library\SOLVER\SOLVER.XLAM") <> "" Then
            ThisWorkbook.VBProject.References.AddFromFile "C:\Program Files\Microsoft Office\Root\Office16\Library\SOLVER\SOLVER.XLAM"
        Else
            ThisWorkbook.VBProject.References.AddFromFile "C:\Program Files\Microsoft Office\Root\Office15\Library\SOLVER\SOLVER.XLAM"
        End If
    End If
End Sub

Alternatively, you could use another UDF to loop through folders and subfolders for the SOLVER.XLAM file.

Function Recurse(Main_Folder As String, search_File As String) As String


    Dim FSO As New FileSystemObject
    Dim myFolder As Folder
    Dim mySubFolder As Folder
    Dim myFile As file
    Dim sFile As String


    sFile = search_File
    
    Set myFolder = FSO.GetFolder(Main_Folder)


    For Each mySubFolder In myFolder.SubFolders
        If Recurse <> "" Then Exit For
        For Each myFile In mySubFolder.Files
            If myFile.Name = sFile Then
                Recurse = myFile.Name & " in " & myFile.Path
                Exit For
            End If
        Next
        If Recurse <> "" Then Exit For
        Recurse = Recurse(mySubFolder.Path, search_File)
    Next


End Function

Sub findfile()

    filep = Recurse("C:\Users\", "SOLVER.XLAM")
    MsgBox filep

End Sub

 

[Paul Sansom]

Hi Tim
I can see that both solutions can work. Using (1) the If statement would identify the solver for the location so far found
I prefer function solution (2) as any future (unidentified) locations would be found.

I would be disappointed if there were yet further locations, as I am using the std solver that is shipped with Excel.
However nothing surprises me more than Microsoft at times.

Thanks again for your help

 

[Tim_Excel_]

Glad to have helped and let me know if you run into any other issues.