Export/Copy DINT Value as Bits/BOOLs to Excel

[AMarks95]

Hey everybody,

I have a bunch of bits used for diagnostics all compiled into a couple of DINTS. I have an x-ref sheet in Excel that aligns explanations for each bit of the DINT in columns. Is there any way to copy/export to column of BOOL values that make up the DINT and paste into a column in Excel?

I've considered using a DDE connection with RSLinx to directly link to the tags, but the computer that I go online with the controller with is on a VM that does not have an Office license, so no Excel access to the tags.

 

[drbitboy]

So you want to convert the DINT values back to BOOLs in the sheet, one bool in each column (or row)?


It's trivial with e.g. Python, assuming there is a driver to get the DINTs.


EDIT: I am not really sure what you are trying to do, and what the various pieces are e.g. if the computer that has RSLinx cannot run eXcel, where will eXcel be run?

 

[drbitboy]

E.g. would it be good enough to enter the DINTs into a couple of cells in a worksheet, and have some formulas to convert those DINTs into their composite bits, one bit per cell adjacent to it x-ref?

 

[drbitboy]

Does this help?

 

[AMarks95]

So you want to convert the DINT values back to BOOLs in the sheet, one bool in each column (or row)?


It's trivial with e.g. Python, assuming there is a driver to get the DINTs.


EDIT: I am not really sure what you are trying to do, and what the various pieces are e.g. if the computer that has RSLinx cannot run eXcel, where will eXcel be run?

E.g. would it be good enough to enter the DINTs into a couple of cells in a worksheet, and have some formulas to convert those DINTs into their composite bits, one bit per cell adjacent to it x-ref?

That could work.

My initial though was, when you expand the DINT data type in Logix 5000 you can see each of the bits (0 or 1) in a column. I've set up my Excel sheet to match. So, it'd be nice if I could just copy/paste that full column of 0's and 1's into a column in Excel.

Typing in a number and converting to a column of 0's and 1's is probably my best bet and is acceptable for this use. Thanks for the suggestion.

 

[drbitboy]

That could work.

My initial though was, when you expand the DINT data type in Logix 5000 you can see each of the bits (0 or 1) in a column. I've set up my Excel sheet to match. So, it'd be nice if I could just copy/paste that full column of 0's and 1's into a column in Excel.

Typing in a number and converting to a column of 0's and 1's is probably my best bet and is acceptable for this use. Thanks for the suggestion.

and you should be able to copy/past the DINT into a cell.

 

[drbitboy]

​

 

[AMarks95]

View attachment 62765​

Mind sharing the formula for splitting the DINT into 32 bits?

I see it at the top now.

 

[drbitboy]

Mind sharing the formula for splitting the DINT into 32 bits?

I see it at the top now.

Here is the text for copy/paste:

=IF(BITAND(B$1+IF(B$1<0,2^32),2^[COLOR=Blue][B]MID($A2,4,2)[/B][/COLOR])>0,"On","Off")

P.S. You may need to do summat else for the bit in blue.

P.P.S. that is LibreOffice, so YMMV.

The only non-formula cells are Row 1 (DINT row) and A2 ("bit31:"). A3 ("bit30:") and below look like this:

=CONCATENATE("bit",RIGHT(CONCATENATE("0",MID(A2,4,2)-1),2),":")