title: Wait for external data to load before automatic calculations? Excel/VBA
body: <p>First time poster, been searching for a solution to this all over but no luck yet. </p>
  
  <p>To explain my problem:
  TERMS:
  OPC =  Object Linking and Embedding (OLE) for Process Control
  (allows other applications to use data from PLC tags)
  PLC = Programmable Logic Controller (computer for process control
  SCADA = Supervisory Control And Data Acquisition (interface that displays values from PLC and allows control)</p>
  
  <p>I have an excel workbook which is automatically opened by the SCADA system (WonderWare > Intouch, the software) at a specific time after the PLC has sorted some values. This workbook populates its cells using the OPC client, accessing them using this method:</p>
  
  <pre><code>=OPCLINK|EAST!'r[BELLWWPOWER]DailyValues_Z3[1,21]'
  </code></pre>
  
  <p>This works well but there are a lot of cells to populate so it takes a few seconds.</p>
  
  <p>What I want to automatically happen is for these cells to populate and any calculations to complete before all the cells with formulas are changed to just the values of those formulas. The workbook is then saved under a new workbook name ("PowerReports-YesterdaysDate") with the VBA code stripped and both workbooks are closed (without saving the original, to preserve formulas).</p>
  
  <p>This all works well except it happens too fast and the new saved copy ends up having just "#N/A" in all the cells with OPC links. When I had the first sheet's private sub as "Worksheet_Activate()" instead of "Worksheet_Calculate()", the code wouldn't stat automatically and waited for a mouse click onto one of the sheet's cells (FYI: the SCADA system opens this workbook to sheet 1 automatically to start with sheet 1's code). The new copy would save successfully in this case, but when the code stats automatically it is too fast. How can I wait for the external data to load before the calculations are done? </p>
  
  <p>I've tried things (I don't know how successfully they were implemented..) like:
  -Application.Calculate
  -Application.RefreshAll
  -Timers
  -Trying to get a flag from the PLC
  -Checking for remaining #N/As </p>
  
  <p>It seems like if a loop or something similar is running right away it doesn't let the external data refresh. </p>
  
  <pre><code>Private Sub Worksheet_Calculate()
  
      ' When first sheet "Main" is activated, all formulas are replaced
      ' with their calculated values. The second sheet, "Monthly Values" is then activated
  
      Dim rng As Range, r As Range
      Set rng = Range("A1:D52")
  
      For Each r In rng
          If r.HasFormula Then
              r.Value = r.Value
          End If
      Next r
  
      Worksheets("Monthly Data").Activate
  
  End Sub
  
  Private Sub Worksheet_Activate()
      ' When second sheet "Monthly Values" is activated, all formulas are replaced
      ' with their calculated values. The sub routine, "SaveWithoutMacros" is then called
  
      Dim rng As Range, r As Range
      Set rng = Range("A1:BJ84")
  
      'Worksheets("Monthly Data").Calculate
      'If Not Application.CalculationState = xlDone Then
      'DoEvents
      'End If
  
      For Each r In rng
          If r.HasFormula Then
              r.Value = r.Value
          End If
      Next r
  
      Call SaveWithoutMacros
  End Sub
  
  Sub SaveWithoutMacros()
      'Purpose : To save a copy of the active workbook without macros
  
      Dim vFilename As String
      Dim wbActiveBook As Workbook
      '----------------------------------------------------------------------
      'Following two lines causes an error in Excel 97 - comment them out
  
      Dim VBComp As VBIDE.VBComponent
      Dim VBComps As VBIDE.VBComponents
      '----------------------------------------------------------------------
  
      ' Save to filename in format (yesterdays date) "PowerReports-DD-MM-YYYY"
      vFilename = ("D:\PowerReports\" &amp; "PowerReport-" _
      &amp; Format(Date - 1, "DD-MMM-YYYY") &amp; ".xls")
  
      ActiveWorkbook.SaveCopyAs vFilename
      Set wbActiveBook = Workbooks.Open(vFilename)
  
      'Now strip all VBA, modules, userforms from the copy
      'This code is from Chip Pearson's website http://www.cpearson.com
  
      Set VBComps = wbActiveBook.VBProject.VBComponents
  
      For Each VBComp In VBComps
          Select Case VBComp.Type
          Case vbext_ct_StdModule, vbext_ct_MSForm, vbext_ct_ClassModule
              VBComps.Remove VBComp
          Case Else
              With VBComp.CodeModule
                  .DeleteLines 1, .CountOfLines
              End With
          End Select
      Next VBComp
      wbActiveBook.Save ' saves new version after code is stripped
  
      ThisWorkbook.Saved = True ' sets save flag to true, does not actually save
      Application.Quit ' quits entire application, all workbooks
  End Sub
  </code></pre>
  
  <p>Sorry for the lengthy post, saw other people getting ripped on for not being detailed enough, haha.</p>