PDA

View Full Version : Solved: Install Add in and set reference



austenr
08-14-2007, 07:35 AM
Is ther a way to check in the workbook open event if the Analysis Tool Pack is installed and if not install it? Also, can you set any references for that add in with code? Thanks

Bob Phillips
08-14-2007, 08:07 AM
Dim adn As Object

Set adn = AddIns("Analysis Toolpak")
If Not adn.Installed Then
adn.Installed = True
End If

austenr
08-14-2007, 08:10 AM
Thanks Bob. Can you set a reference with code?

Bob Phillips
08-14-2007, 08:14 AM
You can, I think there is a KB article about it here somewhere.

austenr
08-14-2007, 08:45 AM
OK Thanks

Ken Puls
08-14-2007, 10:16 AM
Hi Austen,

To get the GUID, which you'll need to set a reference, see here (http://vbaexpress.com/kb/getarticle.php?kb_id=278).
And to set the reference, here (http://vbaexpress.com/kb/getarticle.php?kb_id=267).

austenr
08-14-2007, 10:20 AM
Hey Ken, I found a KB article that Im trying. If that fails, Ill check it out. Thanks

Bob Phillips
08-14-2007, 10:20 AM
There you are Austen, I got my boy to dig out the information for you <G>

austenr
08-14-2007, 10:37 AM
Well I get the code from the KB entry to work to a degree. Now the problem. (I'm posting the code below)

The code adds the solver add in if not installed, but if you go to look for the reference to be set in the VBE its not there. Granted, the KB says it was tested for 2002 but I would think it would work in 2003 as well. Any suggestions?

Option Explicit
Sub FindSolverexcel()
'search the add-in file and try to install
On Error Resume Next
With Application.FileSearch
.NewSearch
.SearchSubFolders = True
.Filename = "Solver.xla"
.LookIn = Application.Path
.Execute
If .Execute > 0 Then
If AddIns("Solver Add-in").Installed = False Then
AddIns("Solver Add-in").Installed = True
'if not installed check cause
If Err.Number > 0 Then
'cause is security restriction
If Err.Number = 1004 Then
MsgBox "Please check if your security setting do not allow you to use VBProject" _
& vbCr & "Click Tools, Macro, security" & vbCr _
& "trusted source check access to Visual Basic Project" & vbCr & "close and open the file again" _
& vbCr & "if there is no option then proceed"
Err.Clear
Exit Sub
Else
'cause is just not in the add-in list yet and it try to reinstall again
AddIns.Add(.FoundFiles(1)).Installed = True
Err.Clear
End If
'give message installation successful
CreateObject("WScript.Shell").Popup "Solver successfully added!"
End If
End If

Else
MsgBox "Solver not installed on this conputer", vbCritical
Exit Sub
End If
End With

Dim i As Integer, x As Long
'checks to see if solver is already a reference in the VBA tool
x = ThisWorkbook.VBProject.References.Count
For i = 1 To x
If ThisWorkbook.VBProject.References(i).Name = "SOLVER" Then
CreateObject("WScript.Shell").Popup "Reference already set", 1, "Solver"
Exit Sub
End If
Next i
'this puts the solver as a reference in VBA tool
ThisWorkbook.VBProject.References.AddFromFile Application.LibraryPath _
& Application.PathSeparator & "SOLVER" & Application.PathSeparator & "SOLVER.XLA"
CreateObject("WScript.Shell").Popup "Reference successfully installed", 1, "Solver"
'this just clear the solver bugs
Application.Run "Solver.xla!auto_open"
End Sub

austenr
08-14-2007, 10:42 AM
Correction, Solver shows up in the references but its not checked.

austenr
08-14-2007, 12:01 PM
Ok, is there a way to activate the reference via code? Cant seem to find it. The reference is added but the box doesn't get checked. However, if the add-in is not installed and gets installed the check box is populated. :dunno

tpoynton
08-14-2007, 06:25 PM
I started digging into this, with the analysis toolpak - VBA actually, and gave up on setting references via code because I found that Application.Run "ATPVBAEN.XLA!Regress" followed by the needed parameters works, as long as the atpvbaen.xla is installed. To avoid errors, I remove the menu item that runs regression if atpvbaen can not be installed.

setting the reference on the development computer helped me with figuring out the parameters, but I decided to pass on setting the reference programmatically.

austenr
08-14-2007, 09:03 PM
Yea, it's not worth it. The KB item installs the add-in and reference. Would have been nice if it could be done. Thanks

austenr
08-15-2007, 01:37 PM
OK, maybe I'm not seeing this and have been playing around with it most of the day. The code I posted earlier in the thread, works as indicated but what I am trying to avoid is the message box, "Reference successfully Installed" every time you open the WB. It needs to display only if the reference had to be installed. Currently it displays every time the WB is opened.

Ideally, if both the reference and add-in are already istalled and activated, I want a box to say "Add-in and Reference are already installed. Proceed"

Ken Puls
08-15-2007, 07:55 PM
There you are Austen, I got my boy to dig out the information for you <G>

LOL!

Austen, I tried your code, and it ran fine for me on Office 2007. If solver was not checked, it became so.

austenr
08-15-2007, 08:31 PM
Thanks Ken. Perhaps I needed to leave it and go do something else for a while. It does work doesn't it? :rotflmao:

austenr
08-16-2007, 06:47 AM
Well when you think you have something working, another thing pops up. If I run the attached WB it opens and does what it is supposed to do. However if you close the WB with the VBE window open I get the following error:

Runtime error 1004: Method 'Calculation' of object 'Application' failed

The error is on this line of code:

Application.Calculation = calcset in the Before Close workbook event.

Ken Puls
08-19-2007, 09:18 PM
In order to change the cacluation mode, you must have at least one (visible) rkbook open. I haven't even downloaded your file yet, but would place money on this being your issue. It whacked me a few times before I started coding around as a standard. :)

If that is the issue, something like this should fix it:
If ActiveWorkbook Is Nothing Then Application.Workbooks.Add

Put it just before the line you gave above.

HTH,

austenr
08-20-2007, 06:32 AM
Hey Ken,

The WB is open, it is when I try to close it that I get the error.

austenr
08-20-2007, 06:58 AM
Hi Ken,

This is what I was refering to:

Private Sub Workbook_WindowDeactivate(ByVal Wn As Excel.Window)
'restore
Application.Calculation = calcset
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
'restore
Application.Calculation = calcset
End Sub

This is where the error comes from.

Ken Puls
08-20-2007, 07:36 PM
Dunno, Austen. This works fine for me:

Private CalcSet As Long
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Debug.Print CalcSet
Application.Calculation = CalcSet
End Sub
Private Sub Workbook_Open()
CalcSet = Application.Calculation
End Sub

austenr
08-20-2007, 08:37 PM
Thanks Ken. Doesn't happen all the time. I inherited this WB from someone else. What does that code do anyway?