Check for Addin before Update Links

[John Michl]

I have a number of workbooks that are distributed across my company
that use functions found in the Analysis Tool Pak. I'm learning that
some people don't have that add-in checked so they receive an update
links error/message when they open the file. I placed the following
code in the Woerksheet.Open" event. Unfortunately, it appears to run
after the sheet attempts to update links. How can I avoid the update
links message?

Thanks - John

Private Sub Workbook_Open()
AddIns("Analysis ToolPak").Installed = True
AddIns("Analysis ToolPak - VBA").Installed = True
End Sub

 

[Jim Rech]

I tried to duplicate your problem and it really didn't happen exactly. I
saved a workbook that used the EDATE function, cloed it and detached the
add-ins. When I opened the workbook there was no error message about not
being able to update links. Of course when I calced Excel the function
returned an error.

Anyway, if it really is a link updating issue and your users have Excel 2002
or later, you can use Edit, Links, Startup Prompt to specify that links
should not be updated and users not prompted. Then your Workbooks_Open code
will have time to save the day.

 

[gimme_this_gimme_that]

You use this to see if a reference exists ...

Dim result as boolean

On Error Resume Next
result = Not Me.VBProject.References("Analysis Tool PAC") Is Nothing

I don't have an immediate example handy of loading a reference but it
should be found in the VBProject.