DDE and Excel with RSLinx

[TL140]

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?

[Steve Etter]

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.

[Aardwizz]

Several Ideas:

Originally Posted by TL140

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.

Originally Posted by TL140

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

[TL140]

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

[Steve Etter]

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.

[TL140]

Originally Posted by Steve Etter

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?

[Steve Etter]

Originally Posted by TL140

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.