DDE and Excel with RSLinx

TL140

Lifetime Supporting Member
Join Date
Jun 2014
Location
South Carolina
Posts
152
Sorry if this is off topic, but this is more of a VBA question.

I have some data that needs to be collected weekly. I have an excel sheet so that a tech can take the laptop, hook up, configure the IP, open RSLinx, open the sheet, hit a macro button, and the data is taken and filled in the appropriate cells. This used to be a pen-and-paper audit that took two hours to do. Now it takes ten minutes. I have it set up like this because the machines are not on the network (no idea why the company took them off).

The sheet works as is, but now my boss wants more added to it. He wants a message to pop up when all the data points are finished updating for one. Next, he wants to keep the same sheet and create copies for each week. Not a problem using "save as". BUT, he wants the cell to highlight if it changed from the previous week.

I made the sheet capable of these functions, but I'm having issues checking when the data is finished updating. I tried to use application.wait, and I also tried a loop with dateadd and Now function, to just give it time for the excel sheet to update. It seems like even after all my tags are finished going through their update on RSLinx Active Topic viewer, there is still a delay before the sheet takes on the new value. And the delays do not work. They both pause the excel sheet.

Any ideas guys?
 
If nobody else does before Monday, I will post the code I use that pops up a message telling me the data transfer is complete. I don’t have my work laptop with me today.
 
Several Ideas:

BUT, he wants the cell to highlight if it changed from the previous week.
(1a) Rather than using SaveAs to create new spreadsheets, keep everything in one spreadsheet on different tabs.

See function:
Application.Worksheets("Name").Add After("OtherNamedSheet")


(1b) Use Conditional Formatting to compare the identical information on one sheet to the other.

There's help in the web for how to add conditional formatting using VBA.


but I'm having issues checking when the data is finished updating.

(2a) Keep all the tags that are to be read in a single, continuous column (perhaps on a hidden sheet), and use the function:
Dim RowLast As Integer '(Last row of Tag list)
RowLast = Cells(Rows.Count, "A").End(xlUp).row


to find the last record, then a simple For r = 1 to RowLast to loop through the tags.

(2b) Use the function MsgBox "Text message" after the for-next loop to inform the user that the data has been read.

(2c) For even more fun, use the function: Application.Cursor = xlwait to display an hourglass, and = xldefault to revert back to normal. Place the former at the beginning of your VBA script, and the latter at the end (and in your OnError logic).

HTH
 
If nobody else does before Monday, I will post the code I use that pops up a message telling me the data transfer is complete. I don’t have my work laptop with me today.

I'd still like to see your solution Steve.

(1a) Rather than using SaveAs to create new spreadsheets, keep everything in one spreadsheet on different tabs.

Unfortunately, there is a worksheet for each machine in the workbook. I may have to create separate workbooks for each machine to keep that idea organized.

(2a) Keep all the tags that are to be read in a single, continuous column (perhaps on a hidden sheet), and use the function:
Dim RowLast As Integer '(Last row of Tag list)
RowLast = Cells(Rows.Count, "A").End(xlUp).row

I'll try this and keep you updated if I have any issues
 
Ok. Here is the code I use to pop-up the message.

MsgBox "Data Transfer to Machine Complete", vbExclamation, "Finished"

FYI - The method I use to do my data transfer is a For loop and I have this line directly after the loop completes and I have issued my DDETerminate command.

Hope this helps.
 
Ok. Here is the code I use to pop-up the message.

MsgBox "Data Transfer to Machine Complete", vbExclamation, "Finished"

FYI - The method I use to do my data transfer is a For loop and I have this line directly after the loop completes and I have issued my DDETerminate command.

Hope this helps.

Thanks Steve,

I think I found my issue. I've had my data links in my sheet being referenced with update link. Most of the research I've done is everyone seems to be using DDERequest.

I got it to work with request, but now I am plagued by another issue. If it cannot connect the topic, it hangs up and the escape key has to be pressed for the loop to end. Is there any way to timeout the request command?
 

Similar Topics

Hi, I'm brand new to the forum and spent some time looking around but could not find an answer to my question. I'm using an excel spreadsheet to...
Replies
3
Views
1,325
So I Read about DDE. I've already set up my topic configuration in RSLinx. I'm now trying to move forward in Excel. I see the formulas, but I...
Replies
21
Views
12,983
Hi I have Linx classic and FT View ME and want to create a config sheet so i can send info from excel into my PLC. I have read various things...
Replies
5
Views
3,646
I have the following DDE Topic in Excel from a ControlLogix processor: =RSLINX|'SW123'!'CCip_Rcp[0].FiRns.Vol_Sp,L1,C1' It was created using a...
Replies
0
Views
2,785
I know this has been covered many times and I have tried EVERY code snip-it that I have found to no avail... First off I have data already setup...
Replies
16
Views
21,929
Back
Top Bottom