在自动计算之前等待外部数据加载？ Excel中/ VBA [英] Wait for external data to load before automatic calculations? Excel/VBA
问题描述
第一次海报，一直在寻找一个解决方案，但没有运气。
$ b
解释我的问题：
TERMS：
OPC =过程控制的对象链接和嵌入（OLE）
（允许其他应用程序使用PLC标签的数据）
PLC =可编程逻辑控制器（用于过程控制的计算机）
SCADA =监控和数据采集（显示PLC的值并允许控制的接口）
我有一个excel工作簿，在PLC对一些值排序后的特定时间由SCADA系统（WonderWare> Intouch，软件）自动打开，该工作簿使用OPC客户端填充其单元格使用这种方法访问它们：
= OPCLINK | EAST！'r [BELLWWPOWER] DailyValues_Z3 [1,21]'
这个工作很好，但是有很多单元格填充，所以需要几秒钟。
我想要自动发生的是这些单元格填充，任何计算在所有具有公式的单元格之前完成改为这些公式的值。然后将该工作簿保存在新的工作簿名称（PowerReports-YesterdaysDate）中，并将VBA代码剥离，并且两个工作簿都已关闭（不保存原始文件以保留公式）。
这一切都很好，除了它发生得太快，新保存的副本最终在OPC链接的所有单元中只有＃N / A。当我将第一个工作表的私有子作为Worksheet_Activate（）而不是Worksheet_Calculate（）时，代码将不会自动统计，并等待单击其中一个工作表的单元格（FYI：SCADA系统打开此工作簿自动从表1的代码开始）。在这种情况下，新副本可以成功保存，但是当代码自动统计时，速度太快。在计算完成之前，如何等待外部数据加载？
我已经尝试了一些事情（我不知道他们是如何成功实现的..）像：
-Application.Calculate
-Application .RefreshAll
-Timers
- 尝试从PLC获取$ flag
- 检查剩余＃N / As
似乎像一个循环或类似的东西正在运行，它不会让外部数据刷新。
Private Sub Worksheet_Calculate（）
'当第一张表Main被激活时，所有公式为用
'替换它们的计算值。然后激活第二张每月价值
Dim rng As Range，r As Range
Set rng = Range（A1：D52）
对于每个r在rng
如果r.HasFormula然后
r.Value = r.Value
结束如果
下一个r
工作表（每月数据）。激活
End Sub
Private Sub Worksheet_Activate（）
'当第二张月度值被激活时，所有公式都被替换
用他们的计算值。然后，子程序SaveWithoutMacros被称为
Dim rng As Range，r As Range
Set rng = Range（A1：BJ84）
' Worksheets（Monthly Data）。计算
'如果不是Application.CalculationState = xlDone然后
'DoEvents
'End If
对于每个r在rng
如果r.HasFormula然后
r.Value = r.Value
结束如果
下一个r
调用SaveWithoutMacros
End Sub
Sub SaveWithoutMacros（）
'目的：保存没有宏的活动工作簿的副本
Dim vFilename As String
Dim wbActiveBook As Workbook
' -------------------------------------------------- --------------------
'以下两行导致Excel 97中的错误 - 将其注释出来
Dim VBComp作为VBIDE .VBComponent
Dim VBComps作为VBIDE.VBComponents
'--------------------------------- -------------------------------------
'以格式保存到文件名（昨天日期）PowerReports-DD-MM-YYYY
vFilename =（D：\PowerReports\ PowerReport-_
&格式（日期 - 1，DD-MMM-YYYY）& .xls）
ActiveWorkbook.SaveCopyAs vFilename
设置wbActiveBook = Workbooks.Open（vFilename）
'现在从副本中剥离所有VBA，模块和用户窗体
'这段代码来自Chip Pearson的网站http://www.cpearson.com
设置VBComps = wbActiveBook.VBProject.VBComponents
对于VBComp中的VBComp
选择案例VBComp.Type
案例vbext_ct_StdModule，vbext_ct_MSForm，vbext_ct_ClassModule
VBComps.Remove VBComp
案例Else
使用VBComp.CodeModule
.DeleteLines 1，。 CountOfLines
结束
结束选择
下一个VBComp
wbActiveBook.Save'在代码被删除后保存新版本
ThisWorkbook.Saved = True'将保存标记为true，实际上并不保存
Application.Quit'退出整个应用程序，所有工作簿
End Sub
对不起，很长的帖子，看到其他人因为没有足够的细节而被剥夺了，哈哈。
只测试最后一个单元格，那么您将确保所有数据都在更改为值之前。
基本上告诉工作表_计算直到最后一个单元格有一个公式
注意：请参阅下面的解决方案，以便在本SCADA状态下使用的修改代码
Private Sub Worksheet_Calculate（）
Dim rngLastCell As Range
设置rngLastCell =范围（D52）
如果rngLastCell.HasFormula然后
Dim rng As Range，r As Range
Set rng = Range（A1：D52）
对于每个r在rng
如果r.HasFormula然后
r.Value = r.Value
结束If
下一步r
工作表（月度数据）激活
如果
End Sub
First time poster, been searching for a solution to this all over but no luck yet.
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)
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:
=OPCLINK|EAST!'r[BELLWWPOWER]DailyValues_Z3[1,21]'
This works well but there are a lot of cells to populate so it takes a few seconds.
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).
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?
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
It seems like if a loop or something similar is running right away it doesn't let the external data refresh.
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\" & "PowerReport-" _
& Format(Date - 1, "DD-MMM-YYYY") & ".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
Sorry for the lengthy post, saw other people getting ripped on for not being detailed enough, haha.
This should work for you now , by testing only the last cell being filled in then you will be sure all the data is in before you change them to values
Were basically telling worksheet_calculate to do nothing until the last cell has a formula
NOTE : SEE OP ANSWER BELOW FOR MODIFIED CODE THAT WORKED IN THIS SCADA SITUATION
Private Sub Worksheet_Calculate()
Dim rngLastCell As Range
Set rngLastCell = Range("D52")
If rngLastCell.HasFormula Then
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 If
End Sub
这篇关于在自动计算之前等待外部数据加载？ Excel中/ VBA的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
