You are not registered yet. Please click here to register!


 
 
plc storereviewsdownloads
This board is for PLC Related Q&A ONLY. Please DON'T use it for advertising, etc.
 
Try our online PLC Simulator- FREE.  Click here now to try it.

---------->>>>>Get FREE PLC Programming Tips

New Here? Please read this important info!!!


Go Back   PLCS.net - Interactive Q & A > PLCS.net - Interactive Q & A > LIVE PLC Questions And Answers

PLC training tools sale

Reply
 
Thread Tools Display Modes
Old January 21st, 2018, 06:52 AM   #1
TL140
Member
United States

TL140 is offline
 
Join Date: Jun 2014
Location: South Carolina
Posts: 113
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
  Reply With Quote
Old January 21st, 2018, 07:03 AM   #2
Steve Etter
Lifetime Supporting Member + Moderator
United States

Steve Etter is offline
 
Steve Etter's Avatar
 
Join Date: Apr 2002
Location: Morristown, TN
Posts: 919
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.
  Reply With Quote
Old January 21st, 2018, 08:32 AM   #3
Aardwizz
Member
United Kingdom

Aardwizz is offline
 
Aardwizz's Avatar
 
Join Date: Dec 2017
Location: Swindon
Posts: 183
Several Ideas:

Quote:
Originally Posted by TL140 View Post
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.


Quote:
Originally Posted by TL140 View Post
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
  Reply With Quote
Old January 21st, 2018, 05:57 PM   #4
TL140
Member
United States

TL140 is offline
 
Join Date: Jun 2014
Location: South Carolina
Posts: 113
Quote:
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.

Quote:
(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.

Quote:
(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
__________________
"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
  Reply With Quote
Old January 22nd, 2018, 07:01 AM   #5
Steve Etter
Lifetime Supporting Member + Moderator
United States

Steve Etter is offline
 
Steve Etter's Avatar
 
Join Date: Apr 2002
Location: Morristown, TN
Posts: 919
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.
  Reply With Quote
Old January 22nd, 2018, 09:37 AM   #6
TL140
Member
United States

TL140 is offline
 
Join Date: Jun 2014
Location: South Carolina
Posts: 113
Quote:
Originally Posted by Steve Etter View Post
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?
__________________
"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
  Reply With Quote
Old January 22nd, 2018, 11:53 AM   #7
Steve Etter
Lifetime Supporting Member + Moderator
United States

Steve Etter is offline
 
Steve Etter's Avatar
 
Join Date: Apr 2002
Location: Morristown, TN
Posts: 919
Quote:
Originally Posted by TL140 View Post
Is there any way to timeout the request command?
That's a good question. I don't know. I've had the same problem but rarely enough that I haven't sought an answer. Maybe someone else has.
  Reply With Quote
Reply
Jump to Live PLC Question and Answer Forum

Bookmarks


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Topics
Thread Thread Starter Forum Replies Last Post
RSLinx DDE Topic Config in Excel jkerekes LIVE PLC Questions And Answers 0 June 4th, 2013 02:07 PM
MS Excel Lags when DDE / OPC Link Paste from RSLinx Coulombboy LIVE PLC Questions And Answers 0 June 18th, 2012 09:01 PM
Excel VBA / RSLINX DDE randomly stops working phuz LIVE PLC Questions And Answers 10 March 16th, 2012 12:21 AM
RSLinx DDE and Excel Marie1982 LIVE PLC Questions And Answers 20 August 18th, 2011 10:53 AM
Rslinx using DDE to Excel file teal845t LIVE PLC Questions And Answers 7 January 12th, 2006 09:04 AM


All times are GMT -5. The time now is 01:46 AM.


.