How JustAnswer Works:
  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site. Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.
Ask The-PC-Guy Your Own Question
The-PC-Guy
The-PC-Guy, Software Engineer
Category: Programming
Satisfied Customers: 1997
Experience:  Extensive Knowledge in PHP, MYSQL, CSS & Javascript
62934938
Type Your Programming Question Here...
The-PC-Guy is online now
A new question is answered every 9 seconds

I have an Excel spread sheet with OPC links. I am looking at

Customer Question

I have an Excel spread sheet with OPC links. I am looking at one of the links (from an Allen-Bradley ControlLogix processor) for a print request. The value in that register is a 0 or 1. When the value in that register changes from 0 to 1, I want to print the first page.
Submitted: 2 years ago.
Category: Programming
Expert:  The-PC-Guy replied 2 years ago.

i'm not sure what an OPC links is.

Do you have the value in a cell changing, and you want to print a page when the value in a specific cell changes?

Customer: replied 2 years ago.
I have pasted DDC/OPC Links into cells to read information from an Allen-Bradley PLC. They are all working. One of the links {=RSLINX|PRINT_TICKET|'PRINT_TICKET_REQ,L1,C1'} gives me either a 0 or a 1 from the PLC. I want to use the value in this link to print a sheet in Excel.
Expert:  The-PC-Guy replied 2 years ago.

which cell address and what is the sheet name it is in?

Customer: replied 2 years ago.
Sheet1, cell A1
Expert:  The-PC-Guy replied 2 years ago.

to use this macro, copy and paste this vba code into the sheet1 object using your vba editor, On windows I believe the keyboard shortcut to open the vba editor is Alt F11, otherwise you would have to get to it through the developer toolbar. Important this code must go in the Sheet1 section, when you first open the editor the workbook object will be selected, you will see a list of objects on the left window, you have to double click sheet1, to get to its area.

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Me.Range("A1")) Is Nothing Then
Exit Sub
Else
Application.EnableEvents = False
If Target.Value = "1" Then
Me.PrintOut From:=1, To:=1
End If

Application.EnableEvents = True
End If
End Sub

---------------------------------------------------------------------------------------------------------------

let me know if you have any questions, problems, or concerns

PLEASE DON'T FORGET TO RATE SO I AM PAID FOR MY TIME

IT WILL NOT COST ANYTHING ADDITIONAL BEYOND THE VALUE OF YOUR QUESTION

TO RATE, CLICK THE STARS AT THE TOP OF YOUR SCREEN

Do not rate negatively, instead continue the conversation with me so I can address any of your concerns

---------------------------------------------------------------------------------------------------------------

if you have any questions in the future you can visit my profile

http://www.justanswer.com/computer/expert-1expert-2rulethemall/

and ask a question right in my box,

also you can put "PC Guy only" in your question title if you want to get to me.

Expert:  The-PC-Guy replied 2 years ago.

to use this macro, copy and paste this vba code into the sheet1 object using your vba editor, On windows I believe the keyboard shortcut to open the vba editor is Alt F11, otherwise you would have to get to it through the developer toolbar. Important this code must go in the Sheet1 section, when you first open the editor the workbook object will be selected, you will see a list of objects on the left window, you have to double click sheet1, to get to its area.

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Me.Range("A1")) Is Nothing Then
Exit Sub
Else
Application.EnableEvents = False
If Target.Value = "1" Then
Me.PrintOut From:=1, To:=1
End If

Application.EnableEvents = True
End If
End Sub

---------------------------------------------------------------------------------------------------------------

let me know if you have any questions, problems, or concerns

PLEASE DON'T FORGET TO RATE SO I AM PAID FOR MY TIME

IT WILL NOT COST ANYTHING ADDITIONAL BEYOND THE VALUE OF YOUR QUESTION

TO RATE, CLICK THE STARS AT THE TOP OF YOUR SCREEN

Do not rate negatively, instead continue the conversation with me so I can address any of your concerns

---------------------------------------------------------------------------------------------------------------

if you have any questions in the future you can visit my profile

http://www.justanswer.com/computer/expert-1expert-2rulethemall/

and ask a question right in my box,

also you can put "PC Guy only" in your question title if you want to get to me.

Customer: replied 2 years ago.
Me.PrintOut From:=1, To:=1 is in red. the error message is Expected: line number or label or statement or end of statement,
Expert:  The-PC-Guy replied 2 years ago.

works for me without error.

try changing the value in the cell directly to a 1 and see if it works without the link.

Then try again with the link

Customer: replied 2 years ago.
The code now has no errors and when I toggle my pit in the PLC, I see the value change from 0 to 1. After 1 second back to 0 (that is in my PLC code). Still not printing.
Customer: replied 2 years ago.
If I type 1 in the cell, it prints.
Expert:  The-PC-Guy replied 2 years ago.

sounds to me like its a problem with your PLC code whatever that is, then. If the value changes then immediately changes back then I think excel is just not picking up on that. Is this an external program that is changing the cell value, or is it from another macro.

Customer: replied 2 years ago.
In my code, I turn the bit on for on second then off. I see the bit change states on the worksheet.
Expert:  The-PC-Guy replied 2 years ago.

see the Worksheet_Change event will fire if the specified cell value changes, but I do not know how many milliseconds it takes for that to fire.

I think what I am asking is your code, is that also in VBA and is it in the same workbook?

Customer: replied 2 years ago.
Same work book. I just tried adjusting the timer so that the bit stays on for 2,3,5,8 and 10 seconds. Still no good. The cell A1 has a link in it. The value changes but the link stays the same.
Customer: replied 2 years ago.
Is there another way to make a page print from a value in a link?
Expert:  The-PC-Guy replied 2 years ago.

link doesn't matter if the value is changing.

What is your other code, rather than triggering a cell change.

I would just add

Me.PrintOut From:=1, To:=1

directly to the code that changes the cell value.

instead of a worksheet change event.

so your code

range("A1").value = blahblah

Me.PrintOut From:=1, To:=1

rest of your code

Expert:  The-PC-Guy replied 2 years ago.

or

ActiveWorkbook.Sheets("Sheet1").PrintOut From:=1, To:=1

if you want to get technical, incase you are working with multiple worksheets, and you want to trigger the print regardless of which worksheet is active

Customer: replied 2 years ago.
The only VBA code I have is what you gave me. The link in the cell A1 looks into my PLC and displays the value in that register, 0 or 1. When I click on the cell, the menu bar reads {=RSLINX|PRINT_TICKET|'PRINT_TICKET_REQ,L1,C1'}. I have the cell set to show the value of the formula and I see it change.
Expert:  The-PC-Guy replied 2 years ago.

i need to see what is going on, do you want to set up a remote session.

Expert:  The-PC-Guy replied 2 years ago.

i need to see what is going on, do you want to set up a remote session.

Customer: replied 2 years ago.
I do not have internet on the computer running the excel program.
Customer: replied 2 years ago.
Can I email you the excel application?
Expert:  The-PC-Guy replied 2 years ago.

oh well.

I'm not exactly sure what is triggering the cell value to change without seeing it.

Can you remote desktop into that machine on your own network?

And The links wouldn't work for me unless they are online somewhere.

you say if you change the cell value to a 1, the macro triggers and it runs. But how else is the value of the cell changing.

what exactly are you doing to trigger the change?

Customer: replied 2 years ago.
If I type a 1 in the cell, it runs. If I use the link it does not.
Expert:  The-PC-Guy replied 2 years ago.

yes so what is changing is it the hyperlink or is the the cell value or the formula?

Customer: replied 2 years ago.
The link stays the same. The value in the link is either a 0 or 1
Customer: replied 2 years ago.
Can the VBA code be modified so that the value in the link is written into another cell? How about exporting and importing?
Expert:  The-PC-Guy replied 2 years ago.

a hyperlinks text property and the cell value are one and the same.

What I am still not clear on is exactly what is triggering the cell value to change.

You said you click the hyperlink in the cell and the value of the cell changes.

Customer: replied 2 years ago.
No. With the link in the cell it does not work. If I just type a 1 in the cell it works.
Customer: replied 2 years ago.
The link in the cell is a DDE/OPC link.
Expert:  The-PC-Guy replied 2 years ago.

no i'm saying how do you intend to trigger the event how does the value in the cell change?

Customer: replied 2 years ago.
I write a value of 1 into an address in my PLC. I have installed RSLinx on the desktop computer and I am using it to read addresses in the PLC. I copy the link from RSLinx to the cell. When I want to print I write a 1 into the address in the PLC. I have used these links in 9 other cells in the workbook and they read the values in the addresses correctly. The link we have been working on is just supposed to print the Excel sheet 1.
Expert:  The-PC-Guy replied 2 years ago.

i'm sorry, dont know what I can do to help you any further. Since the cell value isn't exactly changing in excel, I guess that is why it wont trigger.

Expert:  The-PC-Guy replied 2 years ago.

one more thing I thought of, sometimes when excel generates an error message, it kills any events, so maybe that is why it wasn't working. Try saving the sheet with my original macro, close excel and reboot the computer. Then open the file again. Remember you may need to activate macros to make it work. Then let me know what happens.

Sorry but this just occured to me