PDA

View Full Version : Automatic update of links



K. Georgiadis
03-10-2006, 11:43 AM
This may or may not be VBA-related -- the workbook in questions does contain several macros which may be overriding the standard Excel commands. (normally I would have posted at MS Community Groups first because this does look like a regular Excel question but the MS site seems to have been designed to make it difficult to post):

Under EDIT>LINKS>STARTUP PROMPT I chose "don't display the alert and update links." However, on start up the alert is displayed and, moreover, the workbook initially displays data link errors until the values are updated manually.

What kind of macro is likely to override the "startup prompt" selection, so that I can go on a hunt and see if resides in my workbook? Or, are you aware of a non-VBA fix?

Many thanks.

mdmackillop
03-10-2006, 01:19 PM
I would have though links update would take precedence, otherwise it could make a nonsense of some autorun macros, but I've no technical knowledge of such things.

Cyberdude
03-10-2006, 02:44 PM
I have been fighting with the links display problem ever since I got Excel 2003. In some workbooks it got so bad that I got rid of the links and did my own linking programatically. Not being able to suppress the link notice at open time baffles me. It's supposed to work, but if there's anything it doesn't feel comfortable with, then it notifies you no matter what you do to prevent it. :soupbox:

K. Georgiadis
03-10-2006, 02:49 PM
I would certainly like to learn how to do the linking programmatically!

Cyberdude
03-10-2006, 03:14 PM
I was afraid you might ask that. I'll try to put together a short example and post it.

Shazam
03-10-2006, 03:16 PM
You could try to put this code in your macro.



Sub Test()
With Application
.AskToUpdateLinks = False


' your macro here



.AskToUpdateLinks = True
End With
End Sub



Or try to you this code below to break all links in the workbook.





Sub RemoveExternalLinks()
Dim AllLinks As Variant, i As Long
AllLinks = ActiveWorkbook.LinkSources(xlExcelLinks)
If IsEmpty(AllLinks) Then GoTo XIT
For i = LBound(AllLinks) To UBound(AllLinks)
Debug.Print "Breaking link to file " & AllLinks(i)
ActiveWorkbook.BreakLink Name:=AllLinks(i), _
Type:=xlExcelLinks
Next i
XIT:
End Sub

mdmackillop
03-10-2006, 03:22 PM
Hi Shazam,
If you set your range to check only formulae, you'll speed things up in large used ranges

Set Rng1 = .Cells.SpecialCells(xlCellTypeFormulas, 23)

Shazam
03-10-2006, 03:25 PM
Hi mdmackillop,


I revised my last thread. Because I think this code is might be better.





Sub RemoveExternalLinks()

Dim AllLinks As Variant, i As Long
AllLinks = ActiveWorkbook.LinkSources(xlExcelLinks)
If IsEmpty(AllLinks) Then Goto XIT
For i = LBound(AllLinks) To UBound(AllLinks)
Debug.Print "Breaking link to file " & AllLinks(i)
ActiveWorkbook.BreakLink Name:=AllLinks(i), _
Type:=xlExcelLinks
Next i
XIT:
End Sub

K. Georgiadis
03-10-2006, 03:28 PM
I would be most appreciative.

K. Georgiadis
03-10-2006, 03:31 PM
The links are essential because this is a consolidation workbook that pulls in data from other sources that are constantly being updated. Breaking the links would make it unusable.

Cyberdude
03-10-2006, 04:03 PM
In the following example you have a workbook named "Financial Data" which contains a worksheet named "Accts".
The basis for this type of "linking" is the "ExecuteExcel4Macro" method (see Help). To make it easy to work with, I STRONGLY advise you to define a name for each cell that you are going to extract a value from. In my example below, I used the defined names "Savings", "Checking", and "CD" to identify cells containing balances.
Define a variable (which I called "Path") to use as the path to each source cell. Do not omit the "!" at the end of the path. Note that the rest of the path is enclosed in single quotes.
You have to put this logic (or a call to it) in the Open Workbook event handler module.
Private Sub Workbook_Open() which is located in the "ThisWworkbook" Microfsoft Excel Object.
This makes it execute whenever you open the workbook, thus updating your data just like a link would. Since some of my "link" macros are lengthy, I add logic ahead of it to determine whether I need to execute it each time the workbook is opened. I make the decision based on the time of day the workbook is opened. This avoids the brief delay while it is executing, although it is surprisingly fast. I might add that the workbook from which you are extracting data DOES NOT have to be open during the extraction!

Sub PseudoLinkExample()
Dim Path As String
Application.ScreenUpdating = False
Path = "'C:\Excel Documents\[Financial Data.xls]Accts'!"
Range("A10") = ExecuteExcel4Macro(Path & "Savings")
Range("A11") = ExecuteExcel4Macro(Path & "Checking")
Range("B4") = ExecuteExcel4Macro(Path & "CD")
Application.ScreenUpdating = True
End Sub

K. Georgiadis
03-10-2006, 05:42 PM
Thanks very much. I have to give some thought whether, in my case, setting up the macro is going to be much of a time saver. I am extracting dozens of rows of data from three different workbooks.

XLGibbs
03-10-2006, 05:47 PM
I use a similar method to extract thousands of cells from dozens of workbooks. It will be plenty fast.

K. Georgiadis
03-10-2006, 07:05 PM
thank you guys. I'll give it a try

John_Mc
06-21-2006, 11:14 PM
Hi All,

It's not VBA, but if you go to Edit/Links and click the StartUp button on the dialog box that appears, you can choose to "dont prompt and don't update" as standard for the work book.

Afterwards, you could write code to force the update.

Cheers,
John Mc

Cyberdude
06-24-2006, 09:40 AM
John, how do you write code to force the links update?

John_Mc
06-25-2006, 07:32 PM
Sorry mate, didn't mean to imply i knew in my previous post :doh: . From previous posts your vba is a lot better than mine... Had a reasonable search on the web though and couldn't find much either...

Good luck