![]() ![]() ![]() ![]() ![]() ![]() |
||
![]() |
||
![]() ![]() ![]() ![]() This board is for PLC Related Q&A ONLY. Please DON'T use it for advertising, etc. |
||
![]()
|
New Here? Please read this important info!!!
![]() |
#1 |
Lifetime Supporting Member
![]() ![]() Join Date: Jun 2014
Location: South Carolina
Posts: 144
|
DDE and Excel with RSLinx
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?
__________________
"The best thing about a boolean is even if you are wrong, you are only off by a bit." -Anonymous "A good programmer is someone who always looks both ways before crossing a one-way street." -Doug Linder |
![]() |
![]() |
#2 |
Lifetime Supporting Member + Moderator
|
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.
|
![]() |
![]() |
#3 | ||
Member
|
Several Ideas:
Quote:
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. Quote:
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 |
||
![]() |
![]() |
#4 | |||
Lifetime Supporting Member
![]() ![]() Join Date: Jun 2014
Location: South Carolina
Posts: 144
|
Quote:
Quote:
Quote:
__________________
"The best thing about a boolean is even if you are wrong, you are only off by a bit." -Anonymous "A good programmer is someone who always looks both ways before crossing a one-way street." -Doug Linder |
|||
![]() |
![]() |
#5 |
Lifetime Supporting Member + Moderator
|
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. |
![]() |
![]() |
#6 | |
Lifetime Supporting Member
![]() ![]() Join Date: Jun 2014
Location: South Carolina
Posts: 144
|
Quote:
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?
__________________
"The best thing about a boolean is even if you are wrong, you are only off by a bit." -Anonymous "A good programmer is someone who always looks both ways before crossing a one-way street." -Doug Linder |
|
![]() |
![]() |
#7 |
Lifetime Supporting Member + Moderator
|
|
![]() |
![]() |
Bookmarks |
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests) | |
Thread Tools | |
Display Modes | |
|
|
![]() |
||||
Thread | Thread Starter | Forum | Replies | Last Post |
RSLinx DDE Topic Config in Excel | jkerekes | LIVE PLC Questions And Answers | 0 | June 4th, 2013 03:07 PM |
MS Excel Lags when DDE / OPC Link Paste from RSLinx | Coulombboy | LIVE PLC Questions And Answers | 0 | June 18th, 2012 10:01 PM |
Excel VBA / RSLINX DDE randomly stops working | phuz | LIVE PLC Questions And Answers | 10 | March 16th, 2012 01:21 AM |
RSLinx DDE and Excel | Marie1982 | LIVE PLC Questions And Answers | 20 | August 18th, 2011 11:53 AM |
Rslinx using DDE to Excel file | teal845t | LIVE PLC Questions And Answers | 7 | January 12th, 2006 10:04 AM |