1

I'm using DDE to pull data from a PLC into an excel sheet. I've created a simple button that updates the links manually when pressed. Now I'm trying to create some type of indicator to let the user know that all links have finished updating, and only if they finish updating. I tried to create a MsgBox after application.DDEterminate, but the box would pop up before the links were finished updating. What would be the best solution to this problem?

Community
  • 1
  • 1
TL140
  • 11
  • 1
  • Maybe I'm wrong, but when you use DDE, you get channel numbers, right? Every channel is a Long. Maybe you could try something tricky, like summing all channels in a variable and substracting from that variable every time you execute DDETerminate. If that variable returns to its original value at start of code, then you use msgbox. – Foxfire And Burns And Burns Jan 21 '18 at 01:41
  • Unfortunately I'm only dealing with one channel through RSLinx. And with RSLinx, the tags that I receive data on are located in that one channel and called related to their tag name **ActiveWorkbook.UpdateLink Name:= "RSLINX| _topic_ ! _Tag[" & Index & "]"_ , Type:=xlOLELinks** I know I can update all links on the sheet at once, but I've been trying to use the index and for loops as a tool to help this issue. – TL140 Jan 21 '18 at 02:09
  • Have you tried with `Application.Wait`? – Foxfire And Burns And Burns Jan 21 '18 at 02:13
  • No I have not. let me try that. – TL140 Jan 21 '18 at 02:14
  • Is not a real solution, but if you get the msgbox before links finished updating, I guess that update is an asynchronous process, so maybe something like that could work. https://stackoverflow.com/questions/1544526/how-to-pause-for-specific-amount-of-time-excel-vba – Foxfire And Burns And Burns Jan 21 '18 at 02:21
  • Also, maybe using DoEvents can help you – Foxfire And Burns And Burns Jan 21 '18 at 02:23
  • @FoxfireAndBurnsAndBurns The Application.wait gave the time needed to finish out the updates, however, I did find one instance where it hung up for just long enough to have the msgbox pop up before it was complete. I really need a way to verify the data has been updated since the application.wait isn't a true check. – TL140 Jan 21 '18 at 05:41
  • Did you try using `DoEvents` after DDETerminate? – Foxfire And Burns And Burns Jan 21 '18 at 13:01

1 Answers1

0

Ok, not sure if this will work on you, but it did for me. I used Application.DDE to get info from Word, and then I used this code to make sure the channel is terminated. Hope it helps or maybe give you an idea

Dim channelNumber As Long
Dim returnList As Variant
'
'
'
'
'
'
'
'whatever your code is

Terminate_DDE:

Application.DDETerminate channelNumber
DoEvents

returnList = Application.DDERequest(channelNumber, "SysItems")
If IsError(returnList) = True Then
    'DDETerminate is done. Show MSGBOX
    MsgBox "ALL data updated"
Else
    'Channel is not terminated. We loop
    GoTo Terminate_DDE
End If
Foxfire And Burns And Burns
  • 10,665
  • 2
  • 17
  • 38
  • I just tried your code and the message box popped as soon as the macro button was hit. The items still lagged behind and the data didn't update until about 4 seconds after the macro completed. So essentially the same thing is happening. – TL140 Jan 22 '18 at 02:49
  • then my last try would be something like `Do Until Application.CalculationState = xlDone` – Foxfire And Burns And Burns Jan 22 '18 at 11:17