Click Here for a question on DDE

[azecraze 0]

I have already PM to Chakorules on this topic but anyone who can help.......: In the downloads section of this site there is a file called "Allen Bradley SLC500 DDE Link to Excel Logger 1.4" Can someone - preferably the author (chakorules) please advise me as to whether they have had this working. I am using an Allen Bradley ML1200 and I was wondering if you could point me in the right direction as to what is the correct changes I need to make in the macro for it to run correctly using this PLC, as I have only limited skills in VBA. I will also be wanting to change the data input from reading Forces, to reading in Binary and Integer values. I have the following installed, and can do a simple DDE: Allen Bradley ML1200 RSLinx Single Node MSExcel

[Chris Elston 60]

The first thing you're gonna need to change is where you store your logs here: 'change directories ChDir "C:\qsi" Next you need a Trigger bit in the PLC. When you want the spreadsheet to GET the data then edit this address: B3/161 Go to FORMAT > SHEET > UNHIDE (the DDE sheet). Click on DDE workbook, find the hotlink cell, and change the address in the formula bar. If you changed your DDE topic name, mine was "M1138", your gonna need to change your topic name there too. Next you need the edit the Macro Bit number also:    'assign PLC bit values to VB variant varibles    varLogging = DDERequest(RSIchan, "B3/163")    varCycle = DDERequest(RSIchan, "B3/161") Last you need to edit what data you want to pull from:        'opens a COLD DDE link        RSIchan = DDEInitiate("RSLinx", "M1138")                'there might be a better way to do this like        'using this somehow ???????? but I don't know how        'data = DDERequest(RSIchan, "f11:0,L7,C7")        'Range("[M1138.xls]LOG!R[x]C1:R[x]C7").Value = data                                                                    'read word F8:10 and load into a VB variant variable        f810data = DDERequest(RSIchan, "F8:10")        'read word F8:11 and load into a VB variant variable        f811data = DDERequest(RSIchan, "F8:11")        'read word F8:12 and load into a VB variant variable        f812data = DDERequest(RSIchan, "F8:12")        'read word F8:16 and load into a VB variant variable        f816data = DDERequest(RSIchan, "F8:16")        'read word F8:18 and load into a VB variant variable        f818data = DDERequest(RSIchan, "F8:18")        'read word F8:17 and load into a VB variant variable        f817data = DDERequest(RSIchan, "F8:17")        'read word F8:20 force check #1 and load into a VB variant variable        f820data = DDERequest(RSIchan, "F8:20")        'read word F8:21 force check #2 and load into a VB variant variable        f821data = DDERequest(RSIchan, "F8:21")        'read word F8:22 force check #3 and load into a VB variant variable        f822data = DDERequest(RSIchan, "F8:22")        'read word F8:23 force check #4 and load into a VB variant variable        f823data = DDERequest(RSIchan, "F8:23")        'read word F8:24 max force and load into a VB variant variable        f824data = DDERequest(RSIchan, "F8:24")        'read word F8:25, get PASS or FAIL status from PLC and load into a VB variant variable        varResults = DDERequest(RSIchan, "F8:25")                'close COLD DDE link        DDETerminate (RSIchan) Hope that helps. Most people forget to change the HOT LINK bit on the hidden workbook. This is how I do an event trigger. When data changes on that sheet, I know to excute the macro to get data from the PLC and write it to the Excel Sheet.

[azecraze 0]

Thanks Chakorules, I will have a go at all that and see what I come up with.

[Guest Guest_azecraze]

Chakorules, Where is the correct place to change the update rate - the rate that data is updated? Once per second or every four seconds, or per hour etc.

[Chris Elston 60]

The sheet is designed to be triggered from the PLC. I used Bit B3/161 to trigger the sheet. Everytime the PLC turns on and off B3/161, the sheet updated and read the data I told it to read. So to answer your question, your PLC would control the rate of the update. In this example, the spread sheet was able to keep up with the PLC when the cycle time was 3 seconds. I wouldn't push it any faster than that. If you need faster than 1 or 2 seconds, then DDE is not the answer for your application, you're gonna need to step up to an OPC server/client software. Hoepfully you don't need to poll the PLC any faster than 1 or 2 seconds....

[Guest Guest]

Aha! Now I understand thankyou.

[azecraze 0]

Chakorules, Everything seems to be running, except for the fact that I am not getting any data on the sheet. 1/ Do I need to change any Topic references in the macro from M1138 to my topic? 2/ I have changed B/161 & B/163 to this in the macro: CODE 'assign PLC bit values to VB variant varibles varLogging = DDERequest(RSIchan, "B3:0/1") varCycle = DDERequest(RSIchan, "B3:0/0") What is the purpose of: varLogging = DDERequest(RSIchan, "B3:0/1") Nothing will happen without this toggled on, but I still get no data onto even when it is toggled on. I have tried changing these: f810data = DDERequest(RSIchan, "F8:10") to bit and word addresses like this: f810data = DDERequest(RSIchan, "B3:0/0") f810data = DDERequest(RSIchan, "B3:0") All the green lights are on, and the DDE link says active. How long does the GET DATA bit need to stay on for it to work? Maybe I need an OSR on the rung?

[Chris Elston 60]

You might not have your trigger setup right. To test the sheet WITHOUT a trigger, you can excute the macro manually. While connected to the PLC, open the Excel sheet and click on TOOLS > MACRO > MACROS... Highlight "START", now click RUN. That runs just the portion of the VBA code that will request and get data from the PLC and place it on the spreadsheet. If you don't get any data, then you either have your topics wrong, or bits not pointing in the correct place. Keep in mind that I see you are changing my F file (which are real numbers) to a B3 file, I assume you want to get the integer value of the B3 file. If so, you want to format your data: f810data = DDERequest(RSIchan, "B3:0"). That is looking at your B3 data as a word. Basically troubleshoot your collection first. Is the Sheet able to collect the data? Troubleshoot by lanuching the macro manually. Once you have collected data like you want, next work on triggering the sheet from a bit change in the PLC.

[azecraze 0]

Ok Chakorules, I have finally got it running - with your help. The problem was the references to the incorrect topic name in the macro. Now that it is going I will be changing around some of the less important stuff, like column names and the pass/fail section. Thankyou for going to all the trouble and effort of coding all this and then just making it available to people like me. I do very much appreciate it. regds AzeCraze.