Update/Don't Update Links

you might turn of the updating in:
Tools>>Options>>Calculation tab>>uncheck Update remote references

regards,
jeroen

The Update Links prompt is displayed as the workbook is opened so I am not sure you will be able to *easily* control this based on the value of a cell.

Here is the skinny on how this feature works:

When Excel opens a workbook with external links to other workbooks it, by default, shows a prompt asking whether or not to update those links. Whether or not the prompt is displayed and the default behavior if it is not displayed can be controlled using Excel option settings. When the settings are set such that the links are not updated then any missing source workbooks are ignored and the "cannot update links" alert is not displayed.

There are two different options to consider in Excel 2002 and later with regard to whether or not the update links prompt is displayed and one option in Excel 2000 and earlier.

2002 and later:

There are two different but related settings: a user specific option and a workbook specific option.

User Specific Option - To control how the prompt is displayed for the current user only and for all workbooks opened by that user, choose the menu command Tools->Options and click the Edit tab. Locate the check box "Ask to update automatic links". Setting this check box on will show the prompt unless the workbook specific option dictates otherwise. Setting this check box off tells Excel to never show the prompt and to update the links based on the workbook specific option setting.

Workbook Specific Option - The workbook specific option setting is set by choosing the menu command Edit->Links and clicking the Startup Prompt command button. There are three settings:

Let users choose to display the alert or not: If the user specific option is set on (ask) then the prompt is displayed. If the user specific option is set off (don't ask) then the prompt is not displayed and the links are updated.

Don't display the alert and don't update automatic links: The prompt is never displayed and the links never updated, regardless of the user specific setting.

Don't display the alert and update links: If the user specific option is set on (ask) then the prompt is displayed. If the user specific option is set off (don't ask) then the prompt is not displayed and the links are updated.

2000 and earlier:

Only one option is provided: A user specific option to control how the prompt is displayed for the current user only and for all workbooks opened by that user. To set it choose the menu command Tools->Options and click the Edit tab. Locate the check box "Ask to update automatic links". Setting this check box on will show the prompt for all workbooks. Setting this check box off tells Excel to never show the

Kevin

I am aware of this option.  But I would like this to only be triggered by a cell that is turned to certain value.

Many regards,
WDelaney

Is the specific cell in the workbook you are opening?

Kevin

or use a macro like:

Private Sub Workbook_Open()
If UCase(Sheets(1).[A1]) = "YES" Then
    ThisWorkbook.UpdateRemoteReferences = True
Else
    ThisWorkbook.UpdateRemoteReferences = False
End If
End Sub

place this code in the ThisWorkbook code pane. if in the first sheet the value Yes is in cell A1 then it will turn the update links on otherwise it will turn it of

perhaps this might help you out

regards,
Jeroen

Yes, the specific cell is in the workbook I am opening.

Regards,
WDelaney

If the cell is in the workbook to be opened then you will need to use a function that can pull the cell value from the workbook before you open it. Here is such a function:

Public Function GetCellFromClosedWorkbook( _
   ByVal WorkbookPath As String, _
   ByVal WorkbookName As String, _
   ByVal WorksheetName As String, _
   ByVal CellReference As String) As Variant
   
   Dim MacroArgument As String
   
   ' Append trailing slash
   If Right(WorkbookPath, 1) <> "\" Then
      WorkbookPath = WorkbookPath & "\"
   End If
   
   ' Exit if workbook does not exist
   If Dir(WorkbookPath & "\" & WorkbookName) = "" Then
      Exit Function
   End If
   
   ' Build macro argument
   MacroArgument = "'" & WorkbookPath & "[" & WorkbookName & "]" & _
      WorksheetName & "'!" & Range(CellReference).Address(True, True, xlR1C1)
   
   ' Read cell value from cell workbook
   On Error Resume Next
   GetCellFromClosedWorkbook = ExecuteExcel4Macro(MacroArgument)
   
End Function

Then, use the Workbooks.Open command as follows:

   If GetCellFromClosedWorkbook("c:\full\path\to\", "file.xls", "Sheet1", "A1") = "Ignore Links" Then
      Workbooks.Open Filename:="c:\full\path\to\file.xls", UpdateLinks:=False
   Else
      Workbooks.Open Filename:="c:\full\path\to\file.xls"
   End If

Kevin

Where would I put this?  Would this be a macro?

Public Function GetCellFromClosedWorkbook( _
   ByVal WorkbookPath As String, _
   ByVal WorkbookName As String, _
   ByVal WorksheetName As String, _
   ByVal CellReference As String) As Variant

It is a VBA function that you will add to your VBA project. But...it sounds like you have not been doing any VBA as of yet.

Can you describe more about the situation? Are you wanting to just double-click a workbook and control the external links prompt?

Kevin

Where am I supposed to put this stuff.  I tried the following:

Module of Sheet 15:
Public Function GetCellFromClosedWorkbook( _
   ByVal WorkbookPath As String, _
   ByVal WorkbookName As String, _
   ByVal WorksheetName As String, _
   ByVal CellReference As String) As Variant
   
   Dim MacroArgument As String
   
   ' Append trailing slash
   If Right(WorkbookPath, 1) <> "\" Then
      WorkbookPath = WorkbookPath & "\"
   End If
   
   ' Exit if workbook does not exist
   If Dir(WorkbookPath & "\" & WorkbookName) = "" Then
      Exit Function
   End If
   
   ' Build macro argument
   MacroArgument = "'" & WorkbookPath & "[" & WorkbookName & "]" & _
      WorksheetName & "'!" & Range(CellReference).Address(True, True, xlR1C1)
   
   ' Read cell value from cell workbook
   On Error Resume Next
   GetCellFromClosedWorkbook = ExecuteExcel4Macro(MacroArgument)
   
End Function


Inserted into "This Workbook":

'Then, use the Workbooks.Open command as follows:

   If GetCellFromClosedWorkbook("F:\Data\SHARED\Marcus Reitz\Projects\Job Worksheet", "Master Fulfillment Job Worksheet 10.26.05-Code1.xls", "Sheet15", "A39") = "Ignore Links" Then
      Workbooks.Open Filename:="F:\Data\SHARED\Marcus Reitz\Projects\Job Worksheet\Master Fulfillment Job Worksheet 10.26.05-Code1.xls", UpdateLinks:=False
   Else
      Workbooks.Open Filename:="F:\Data\SHARED\Marcus Reitz\Projects\Job Worksheet\Master Fulfillment Job Worksheet 10.26.05-Code1.xls"
   End If

End Sub

HELP!!!!!!
Thanks

W,

We need to take a breather here ;-)

Before continuing I need to understand the situation. Are you trying to open the workbook from another workbook or by double-clicking on it?

Kevin

double-clicking on it

Where do we have to put the first piece of code, Public Function GetCellFromClosedWorkbook? Inserting the code for the Workbooks.Open command into thisWorkbook and the Open event made sense. Just don't understand where to put the function. Also inserted into This Workbook?
Regards,
wdelaney

The code above assumes you were opening the workbook from another workbook and it was that workbook which would contain the code. Since Excel displays the Update Links prompt before any VBA code is run you will not be able to control the behavior of this prompt under this scenario. However, you can control when links are updated.

Assuming you are on Excel 2002 or later, choose the menu command Edit->Links and click the Startup Prompt command button. Choose the setting "Don't display the alert and don't update automatic links"

Then add this code to the ThisWorkbook module. To add VBA code to the ThisWorkbook module in an Excel workbook, press ALT+F11 to open the VBA development environment (VBE). Press CTRL+R to open the VBE project explorer. Find the module named ThisWorkbook and double-click it. Paste the code into the document window that appears. Press ALT+F11 to return to the Excel workbook. Make sure you remove all other code I posted earlier.

Private Sub Workbook_Open()

   Dim LinkSource As Variant

   If Sheets("Sheet1").[A1] = "Update Links" Then
      For Each LinkSource In ThisWorkbook.LinkSources
         ThisWorkbook.UpdateLink Name:=LinkSource, Type:=xlExcelLinks
      Next LinkSource
   End If

End Sub

When you open the workbook it will only refresh the links if cell A1 of sheet Sheet1 is set to "Update Links". Of course you can change this condition to whatever you want.

Kevin

Kevin, the code still doesn't work. I put the above code into the open event of this workbook. I want it to refer to Sheet15 which is named "Closing". I am assuming that I put "Closing" in the spot where it states "Sheet1" and then A39 for the cell that I want referred to in "Closing". A39 refers to another cell  (on "Closing", the same sheet) that has today's date in it. A39 has this formula IF(B2=0,"Update Links", ""). So, if someone hasn't entered the date in B2, by all rights, then this particular statement "If Sheets("Sheet1").[A1] = "Update Links" Then..." should work, shouldn't it?
Regards,
Wendy

Wendy,

Try this:

Private Sub Workbook_Open()

   Dim LinkSource As Variant

   If IsDate(Sheets("Closing").[B2]) Or Int(Now()) <> Sheets("Closing").[B2] Then
      For Each LinkSource In ThisWorkbook.LinkSources
         ThisWorkbook.UpdateLink Name:=LinkSource, Type:=xlExcelLinks
      Next LinkSource
   End If

End Sub

Kevin

Hi Kevin

Just put your code in and the debugger stops as pointed out below.  I did modify the code (the "or" statement) because all we want to have it do is read that one cell to see if there is any date in it at all.  When that cell is blank we want to be asked to "Update or Not Update".  


Private Sub Workbook_Open()

   Dim LinkSource As Variant

   If IsDate(Sheets("Closing").[B4]) Then
      For Each LinkSource In ThisWorkbook.LinkSources
         Debugger stops here:  ThisWorkbook.UpdateLink Name:=LinkSource, Type:=xlExcelLinks
      Next LinkSource
   End If

Thanks for all your help.
wdelaney

Wendy,

I made some changes so the code operates as you want. Try it and if the code still fails then let me know what the error is.

Private Sub Workbook_Open()

   Dim LinkSource As Variant
   Dim Result As Long

   If Not IsDate(Sheets("Closing").[B4]) Then
      Result = MsgBox("Do you want to update external links?", vbYesNo + vbQuestion)
      If Result = vbYes Then
         For Each LinkSource In ThisWorkbook.LinkSources
            ThisWorkbook.UpdateLink Name:=LinkSource, Type:=xlExcelLinks
         Next LinkSource
      End If
   End If

End Sub

Kevin

Kevin

Unfortunately it didn't work.  It actually might have worked but it is not exactly what I am looking for.  Below is what the MsgBox says that I do not want to appear when a date is in the Closing B4 cell.  This MsgBox pops up every time you open the workbook.  I don't want the user to have the option to update the data.  I actually don't want them to update the data when a date is in that cell.  I hope that makes sense.

This workbooks contains links to other data sources.

*If you update the links, Excel attempts to retreive the latest data.
*If you don't update, Excel uses the previous information.

Update                Don't Update           Help (These are buttons to select)

Thanks
WDelaney

You need to turn off the automatic link update dialog: Choose the menu command Edit->Links and clicking the Startup Prompt command button. Select the setting "Don't display the alert and don't update automatic links"

With this dialog turned off then the code we wrote will run and do what you want.

Kevin

IT WORKS!!!!

The only item that doesn't is when there isn't a date in the cell and I click Yes to update the external links it gives me an error saying that I can't update because the sheet is protected.  When I click debug it highlights this statement "ThisWorkbook.UpdateLink Name:=LinkSource, Type:=xlExcelLinks".  All of my sheets within this workbook need to be protected.  Is there a way to let the protection still apply?

Thanks Kevin!  This is great.  

You're welcome :-)

Kevin