PLC Reporter Problem

[ECSI 17]

I am using PLC Reporter v3.70 to bring 3975 DM values into a spreadsheet. I am using a Finsgateway serial connection. The communications work great after upgrading Finsgateway, and I can read all data values, but there is one problem. I need each cell in the spreadsheet to display a 4 digit value. Depending on the data value in the DM word, some cells will only display 2 digits. For example: If DM0 = 00B6, then the spreadsheet will show it as B6. This is no good. If DM0 = 0006, then the spreadsheet will show it as 0006. This is good. If DM0 = 006B, then the spreadsheet will show it as 006B. This is good. It seems that any DM hex value that has a letter as the most signicant digit will not display properly. This may be an Excel problem more than a PLC Reporter problem, but I've tried every cell format I could think of (General, Text, Special, Number....) and still no success. The reason I need all 4 digits displayed is because there are macros in the spreadsheet that take all of these cell values and generate an alarm list on a different sheet. It works great except two digit values create errors. Any ideas how to solve this? Thanks Edited 19 Apr 2006 by ECSI

[TimWilborne 108]

I don't know how this will work with your macros but I have had problems displaying hex numbers in the same way. The following works for displaying them =DEC2HEX(HEX2DEC(A1),4) A1 being the cell (hidden on mine) and the 4 to the right of the A1 being the number of places you want the hex to display Yes it looks kind of funny. Pretty much convert the hex number to decimal and turn right around and convert it back to hex so you can input your number of places

[benbrad 0]

Could try formatting the cell as text, that way excel doesn't do anything with the number entered.

[TimWilborne 108]

I should have mentioned that the HEX2DEC and DEC2HEX functions are part of the Analysis ToolPak add-in for Excel. It comes with Excel but is not automatically installed. Go to Tools, Add-Ins.. to install

[ECSI 17]

Benbrad, text format was the first one I tried but it didn't work. TWControls, thanks for the suggestion...I'll try it out tomorrow.

[TimWilborne 108]

ECSI - I might have to do something similar to this. I was wondering if the hex to dec to hex conversion worked in a macro or if you came up with something better? Edited 26 Apr 2006 by TWControls

[ECSI 17]

I still havent had a chance to try it, but hopefully I will in the next couple of days yet. I'll be sure to let you know if it works.

[TimWilborne 108]

Ok thanks