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: 1874
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: 1 year ago.
Category: Programming
Expert:  The-PC-Guy replied 1 year 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 1 year 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 1 year ago.

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

Customer: replied 1 year ago.
Sheet1, cell A1
Expert:  The-PC-Guy replied 1 year 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 1 year 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 1 year 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 1 year 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 1 year 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 1 year ago.
If I type 1 in the cell, it prints.
Expert:  The-PC-Guy replied 1 year 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 1 year 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 1 year 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 1 year 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 1 year ago.
Is there another way to make a page print from a value in a link?
Expert:  The-PC-Guy replied 1 year 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 1 year 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 1 year 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 1 year ago.

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

Expert:  The-PC-Guy replied 1 year ago.

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

Customer: replied 1 year ago.
I do not have internet on the computer running the excel program.
Customer: replied 1 year ago.
Can I email you the excel application?
Expert:  The-PC-Guy replied 1 year 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 1 year ago.
If I type a 1 in the cell, it runs. If I use the link it does not.
Expert:  The-PC-Guy replied 1 year ago.

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

Customer: replied 1 year ago.
The link stays the same. The value in the link is either a 0 or 1
Customer: replied 1 year 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 1 year 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 1 year 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 1 year ago.
The link in the cell is a DDE/OPC link.
Expert:  The-PC-Guy replied 1 year ago.

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

Customer: replied 1 year 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 1 year 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 1 year 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

Related Programming Questions