I have an application where I trap Allen Bradley PLC5 registers when a machine faults using hyperlinks to a Excel spreadsheet. I have this PLC networked to my PC using RSLINX. I am able to look at only the last occurrence. I would like to save the Excel spreadsheet for each occurrence.

So far I have been able to hyperlink the PLC register into the Excel spreadsheet and have been able to break this info down into bits using Excel commands. I would like to automate this process using script programming that looks at a single register in the PLC, N12:154, and if this register is not zero, archive the fault spreadsheet as:

Fault(number from N12:154) YYYYMMDDHHmmss.XLS

I have already figured out how to extract the date stamp information using script commands.

The fault file will automatically point out what caused the specific fault with a section for each specific fault number. This will help mechanics in troubleshooting problems with this complicated machine.

The problem that I currently have is that when I try to extract the fault register information hyperlinked to cell “M1” from my spreadsheet, “Get Fault Value.XLS”, I get an error

“Type Mismatch”

This is for the command

CstrFaultNumber = objSheet.Range(“M1”).Value

This command works fine for any numeric or text cell, but I get this error for the hyperlinked cell, which has in it

{=RSLINX|KONICA!`N12:154,L1,C1`}

I have tried to use the script hyperlink command setting

CstrFaultNumber = {RSLINX|KONICA!`N12:154,L1,C1`}

But this hangs up on the : (colon) character. All the examples I find use the word HYPERLINK in this command. I have tried that and it doesn’t work. Unfortunately all my searching on the network has not turned up anything specific on how to use this hyperlinked information from an Allen Bradley PLC5. I get it into Excel by using the “insert hyperlink” hotkey and the clicking on the cell in a live RSLINX table. The Excel spreadsheet works fine. I would just like to write a program to save the fault file when it happens for troubleshooting purposes. The register N12:154 goes back to zero when the fault is cleared. The fault must be cleared for the machine to go on working. If another fault happens the trap registers in the PLC are overwritten, prior fault information is lost, so I would like to save this file each time a fault occurs. I would have my script program looping and triggering off N12:154 not equaling zero and save all faults traps when they occur.

Any suggestions?

I'm a little confused with your post.... but you may want to check this thread out..

http://forums.mrplc.com/index.php?showtopic=5412&hl=

From what I see in your post I don't see why you would get a type mismatch. I would reccomend posting some of your actual code, it would be much more helpful.

such as.....

What datatype do you have the variable "CstrFaultNumber" declared as???
What is the object "objsheet" set as ???

Dear JT:
I've seen something similiar to what you describe and used the following work around.
First let the code paste from Linx to Excel. You might need to research the DDE commands.
objSheet.Range(“M1”).Value = {RSLINX|KONICA!`N12:154,L1,C1`}
Then move teh data from the cell to you variable.
CstrFaultNumber = objSheet.Range(“M1”).Value
I have stored on a file server I cannot excess right this instant an example of what you describe if you are interested.

BobLfoot