Does anyone know of a way to toggle DDE links "off" for an Excel worksheet, other than closing it and electing the "don't update" option when you reopen the sheet? I'm trying to stop the links from updating in real time when I don't need it. Excel Help had nothing on it, and searches of Google and here on ET yielded nothing that looked like it addressed what I'm after. Thanks in advance.
I think the only way would be to turn off sheet calcs all together, and that may be too much for you. Maybe it's possible to turn them off for a particular worksheet or range.
My experience is that this command does not stop the links from accepting data: Application.Calculation = xlCalculationManual The only other way is to blank-out or convert the formulas to a string: Dim eRng as Range, lElementCnt as Long, j as Long Dim DQ as String, strFormu as String DQ = Chr(34) Set eRng = Range("Elements") ' this is the range of all of the DDE Links lElementCnt = eRng.Count For j = 1 To lElementCnt strFormu = eRng(j).Formula If Left(strFormu,1) <> DQ Then _ eRng(j).Formula = DQ & strFormu & DQ Next j Just place this in a Sub, and call it from a button called "Deactivate". Create an "Activate" button and call another sub that reverses the above.
Yeah, excel should have this as standard. My links take up a lot of resources when they are running, so I delete the columns they are in when I don't need them, and hit undo when I need them. Stupid simple, but it works for me. Needed to find a better way, thanks.
Well, since most DDE references are formula-based inside of a cell, there is really no method to turn it off and on. There is a DDERequest method that was designed to allow you to programatically control when the data comes-in, but I discovered that not all DDE servers support this method...i.e. they only support in-cell DDE requests. IB DOES support DDERequest and most of the other DDE functions. And from the code below, you can see they build all of their formula links via low-level VBA functions: Function composeShortLink(server, topic, rawReq) As String composeShortLink = server & topic & EXP_POINT & cleanReq(rawReq) End Function Function composeLink(server, topic, id, rawReq) As String composeLink = server & topic & EXP_POINT & id & QMARK & cleanReq(rawReq) End Function Function composeControlLink(server, topic, id, reqType, rawReq) As String composeControlLink = composeLink(server, topic, TICK_CHAR & id, reqType & QMARK & rawReq & TICK_CHAR) End Function Function composeSubscriptionLink(server, topic, Optional ByVal req As String = FULL_CONTRACT_REQ) As String composeSubscriptionLink = server & topic & EXP_POINT & req End Function
Just found this in Excel help after searching on "turn off calculations". It is exactly what I was after: --Links to other programs-- These options are for links to other programs that use OLE (Object Linking and Embedding) or DDE (Dynamic Data Exchange). Links to other programs can be set to be updated automatically (when you open the destination file or any time the source file changes while the destination file is open) or manually (you specifically request the update). By default, new links are set to automatic updating. Set a link to update manually when you want to see the previous data before updating to the new data. --Set a link to another program to be updated manually-- 1. On the Edit menu, click Links. The Links command is unavailable if your file does not contain linked information. 2. In the Source list, click the linked object you want to update. An A in the Update column means the link is automatic, and a M in the Update column means the link is set to Manual update. To select multiple linked objects, hold down CTRL and click each linked object. To select all linked objects, press CTRL+A. 3. To update a linked object only when you click Update Values, click Manual. --Set a link to another program to be updated automatically-- 1. On the Edit menu, click Links. The Links command is unavailable if your file does not contain linked information. 2. In the Source list, click the linked object you want to update. An A in the Update column means the link is automatic, and a M in the Update column means the link is set to Manual update. To select multiple linked objects, hold down CTRL and click each linked object. To select all linked objects, press CTRL+A. 3. To update a linked object every time you open the file that contains the object or any time the original information changes while the file is open, click Automatic. 4. Click OK. 5. On the Tools menu, click Options, and then click the Calculation tab. 6. Make sure the Update remote references check box is selected.
FINALLY, after much research, I discovered the most "elegant" solution in VBA....... 1) place this in your Workbook_Open event procedure: ActiveWorkbook.UpdateRemoteReferences = False ' DDE Links will not start now when workbook opened 2) Place this in a procedure called by a button on a form or on the sheet: ' called by Activate/Deactivate Button Private Sub cmdActiveDeactive_Click() Const ErrMsg1 As String = "The DDE Server has not been started !!" Application.DisplayAlerts = False ' this prevents the stupid Microsoft Dialog box from coming up On Error GoTo Err If cmdActiveDeactive.Caption = "Deactivate" Then Application.Calculation = xlCalculationManual ' DDE.EstablishLinkOnData Deactivate ActiveWorkbook.UpdateRemoteReferences = False ' turn-off the links cmdActiveDeactive.Caption = "Activate" Else ActiveWorkbook.UpdateRemoteReferences = True ' below detects any #REF errors in one of the cells If IsError(Range("UpperLeft")) Then Err.Raise 10000, "", ErrMsg1 DDE.EstablishLinkOnData EnumLink.Activate Application.Calculation = xlCalculationAutomatic cmdActiveDeactive.Caption = "Deactivate" End If Exit Sub Err: MsgBox ErrMsg1, vbCritical End Sub There it is....no stupid message from Microsoft, and a nice user-friendly message appears if the DDE server application has not been started. Notes: 1) Range("UpperLeft") just points to any cell that has a DDE link reference 2) DDE.EstablishLinkOnData EnumLink.Activate is a procedure that makes sure that each and every DDE link gets "trapped" by a procedure so that calculations can be performed...moving averages, etc. This is optional.