Want to remove message about updating automatic links upon opening spreadsheet

[Tom Rowley]

When I open my spreadsheet that refers to other workbooks I receive the
following message:
The workbook you opened contains automatic links to information in
another workbook. Do you want to update this workbook with changes made
to the other workbook?
* To update all linked information, click Yes.
* To keep the existing information, click No.
I want to get rid of this message. The user always has to press No
which is annoying. The following VB code updates the links after
opening the other workbooks with the option 3. I tried putting in code
like this:
ActiveWorkbook.UpdateRemoteReferences = False
Application.DisplayAlerts = False
in the ThisWorkbook (Code) Open operation but it didn't work. It seems
this code isn't run until after I get the annoying message goes away.
Any suggestions?
Thanks, Tom Rowley

[Bob Umlas]

Workbooks.open "Whatever.xls",False

the false says do not open links

Tom Rowley wrote in message <37729498...@bradycorp.com>...

[Tom Rowley]

Amy Mason wrote this directly to me:
Tom,
I suggest recording a macro to do this in Excel. Go to Tools,
Options, and under the Edit tab, clear the checkbox that states 'Ask
to automatic update links.' This will prevent Excel from asking. In
order to do it through VBA, record a macro and see what the exact code
is. Good luck

Amy

Thanks, Amy for the suggestion, I didn't realize that tool option was there.
The code it creates is: Application.AskToUpdateLinks = False. It is too late
to put it in the open procedure because it happens on the open. What I did
find out is that it saves these options with the workbook, so I just
unclicked the box. The problem is now it creates a new message. External
Copy: file format is not valid. I think it is because my external linked
file is in a .csv format. Does any one know how I can get rid of this
message now? Maybe I should post this as a new question?
Thanks for your help, Tom Rowley

P.S. Bob, this doesn't work, my problem occurs on my original first opened
spreadsheet.

[Tom Ogilvy]

Tom,
Doing what Amy suggested and you tried (changing the options setting) means
that it doesn't ask, it updates the link - thus your new problem. What Bob
was saying so concisely that you missed it, is that you need to create a
dummy workbook (so to speak) which opens and automatically runs code to open
the subject workbook. It then closes itself.

Let's say workbookA.xls is the workbook with the problem. Rename it to
WorkbookB.xls. Create a new workbook and name it workbookA.xls In the
new workbook, create macro named Auto_Open or Use the workbooks_Open event
and use Bob's suggestion:

Workbooks.open "WorkbookB.xls",False

That said,
I appears that, if the link can't be updated anyway, there is no reason to
have it. Select the data with the links and do Edit=>Copy and without
changing the selection, do Edit=>PasteSpecial=>Values. That should remove
the links (as long as you got all of them). Of course, only you know if
this is an appropriate approach.

Regards,
Tom Ogilvy

Tom Rowley wrote in message <37739E60...@bradycorp.com>...

[Tom Rowley]

Thank You All for your help, it works. I had another spreadsheet that I was
opening after my original, so I just flip-flopped the order in which I was
opening them and used Bob's method of opening the original workbook with the
links.
Thanks again now I get to work on the documentation to finish up my first VBA
project, Yea!
Tom R.