Automation - Turn off Excel Links

[BillP]

I have an access application that iterates through a bunch of excel files to read data that will be written to some tables. Each of these Excel files is linked to another file. When each workbook is opened, it prompts me with a "Do you want to refresh the links" dialog. I can turn the dialog off be setting "Application.AskToUpdateLinks = False". The problem is that, although this setting turns off the dialog, it's defaults to refreshing the links. Relinking over the LAN makes the process far too slow for my purposes. What I need to do is:
1. Turn off the links; and
2. Turn off the dialog.

 

[BillP]

A friend of mine found the answer, and it works. I am posting this for the benefit of any other users who might be interested.
--------------------------------------------
I found the solution (at least it works for me so far!). I checked in the Excel help files and under the reference for the Open method it says you can set the "UpdateLinks" option when you use the Workbooks.Open method.

Here's a exert:

UpdateLinks Optional Variant. Specifies the way links in the file are updated. If this argument is omitted, the user is prompted to specify how links will be updated. Otherwise, this argument is one of the values listed in the following table.

ValueMeaning
0Doesn't update any references
1Updates external references but not remote references
2Updates remote references but not external references
3Updates both remote and external references
<--------->

So I tried it on our Access form that retrieves the Bloomberg inputs using the following statement:

'Open the excel file
'-------------------
Set MyApp = Excel.Application
Set MyFile = MyApp.Workbooks.Open(FilePath, 0)