Links and Add-Ins Error

[John Cole, Jr.]

I am writing a program for my company that involves multiple workbooks
together. One is teh actual output file that multiple users will
have. One is file that has several spreadsheets of technical data
related to dimensions, weights and company specific info. The other
is Add-In I've written to tie everything together. I'm trying to make
it easy for inexperienced Excel users, so most of the formulas used on
the output file are user-defined functions that exist in the Add-In
but use the technical file to pull the information.

I don't want the Add-In to be open all the time. I just want it
available when the Output file(s) is open, so I've written a
Workbook_Open routine for all the different output files. My problem
is that when opening the file, its prompting me for the Update Links.
If I choose, do not update, the open routine stops and it doesn't
install the add-in or open the technical file. If I choose to update,
they open but I have to go to each cell and use F2 to reset the values
for every column with a formula.

Is there anything I can add to the code supress the update links
window and update all the formulas in the same manner the f2 would do?

Here is the code:

Private Sub Workbook_Open()
'Place in ThisWorkbook of all Excel Supports
Application.DisplayAlerts = False
'Install AddIn
On Error GoTo addInError
Application.AddIns.Add Filename:=APPFullPathName, CopyFile:=False
Application.AddIns(APPNAME).Installed = True
On Error GoTo 0

'Open DataBook
On Error GoTo DataBookError
Dim moduleLineList As Object
Set moduleLineList = GetObject(DataBookFullName)
On Error GoTo 0

ThisWorkbook.Activate
ActiveWindow.Visible = True
Application.DisplayAlerts = True
Exit Sub

'Error Handling
addInError:
MsgBox ("The " & APPNAME & " AddIn isn't in the correct directory, or
it isn't named correctly." & vbCr & _
"Place it in the directory as follows and re-open this Support
File." & vbCr & APPFullPathName)
GoTo ExitAfterErrorMsg

DataBookError:
MsgBox ("The " & DataBook & " file could not be found in teh correct
directory, or it isn't named correctly." & vbCr & _
"Place it in the directory as follows and re-open this Support
File." & vbCr & DataBookFullName)
GoTo ExitAfterErrorMsg

ExitAfterErrorMsg:
Application.DisplayAlerts = False
ThisWorkbook.Close
Application.DisplayAlerts = True
Exit Sub

End Sub



Thanks in advance,
JC

 

[Peter T]

I don't want the Add-In to be open all the time.

No need to add the addin to the addins collection and install it. Why not
simply load the xla as a normal wb when required and unload when done.
Depending on your links (assuming they are correct) it might not even be
necessary for you to open the xla at all.

However I don't think the above relates directly to your problem, yet -

If I choose, do not update, the open routine stops and it doesn't
install the add-in or open the technical file.

I don't understand why the routine stops etc, notwithstanding the links
issue.

Whatever, if there are links (formulas, UDF's buttons etc), and it's not
viable to place the addin in identical relative paths in each system (ie
same as when the wb was saved with the links) best get those links updated.

Could do this with code, perhaps set a flag (a name or some cell) in the WB
that if present tells the open event to update the relevant link before
opening the addin.

Data file, output file, code file > indeed a good setup !

Regards,
Peter T