OT: Ipac Controller -----> Excel

[Jim]

Heres a different one to have a look at.

We have used an IPAC controller at work to connect the counters from 4 injection molding machines to a PC.

We are looking for a cheap and easy solution and after building my cabinet I had some ideas.

What I am trying to do is use the simulated key presses from the IPAC to record how many parts each machine has made. As we already use excel for a lot of our planning we would like each keypress to increase the value in a particular excel cell by 1.

EG CELL A1 value increase by 1 each time the "s" key is pressed.
     CELL B1 value increase by 1 each time the "a" key is pressed.

This needs to run continuously and perhaps have an easy way of reseting any of the cells to 0

Unfortunately I spent too much time playing arcade games as a kid and not enough learning skills like excel / vba programming.

We are only a small company and do not have an IT department that we can rely on. I have asked around elsewhere and someone did give me the following VBA solution but my skills dont extend far enough to implement it....

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Application.OnKey "a", ""
Application.OnKey "s", ""
Application.OnKey "d", ""
Application.OnKey "f", ""

End Sub

Private Sub Workbook_Open()

Application.OnKey "a", "AddOne"
Application.OnKey "s", "AddOne"
Application.OnKey "d", "AddOne"
Application.OnKey "f", "Addone"

End Sub

Then this in standard module

Sub AddOne()

Sheet1.Range("A1").Value = Sheet1.Range("a1").Value + 1

End Sub


I am taking a shot that there is someone here who can see what I am trying to do and could perhaps help me out or point me in a direction of people who could.

Hey if someone could sort it for me I am sure we could work out some $ for them.

Sorry for the OT . Jim

[Minwah]

Must it be in Excel??  I could write a VB program to do that in <5 minutes...

[FractalWalk]

The code you have looks pretty good except it only increments one cell. Therefore, you couldn't track seperate key entries. To do that you would either need seperate subroutines or incorporate a way to recognize the keypress within the AddOne sub.


Try This:

Open a new Excel workbook..

Go to the VBA editor by pressing Alt+F11 or clicking Tools, Macro, Visual Basic editor

In the Project Window, you should see a project called VBAProject(?) The ? is whatever the name of the workbook is.
Expand that project until you see the "This Workbook" entry.
Double-Click on it and it should open the code window for "This Workbook"
Copy the following code and paste it in the window.


Private Sub Workbook_BeforeClose(Cancel As Boolean)

Application.OnKey "a", ""
Application.OnKey "s", ""
Application.OnKey "d", ""
Application.OnKey "f", ""

End Sub

Private Sub Workbook_Open()

Application.OnKey "a", "Add1"
Application.OnKey "s", "Add2"
Application.OnKey "d", "Add3"
Application.OnKey "f", "Add4"

End Sub



Then Insert a module by selecting Insert, Module (duh).
This will open a new code window. Copy and paste the following code into this window.


Sub Add1()
    Range("A1").Value = Range("a1").Value + 1
End Sub

Sub Add2()
    Range("A2").Value = Range("a2").Value + 1
End Sub

Sub Add3()
    Range("A3").Value = Range("a3").Value + 1
End Sub

Sub Add4()
    Range("A4").Value = Range("a4").Value + 1
End Sub

Save and close the workbook and then re-open it for the macros to be recognized. If you have done it correctly, then every time you hit "a" cell A1 will increase in value by 1. Every time you hit "s", Cell A2 increments by one etc.

You can substitue the key presses for whatever you want, just change the references to the letters in the preceding code. Remember however, that the way this code is set up you always have to close an re-open the workbook before your edits will change.

[FractalWalk]

Here is an alternate way with a bit simpler code:

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Application.OnKey "a", ""
Application.OnKey "s", ""
Application.OnKey "d", ""
Application.OnKey "f", ""

End Sub

Private Sub Workbook_Open()

Application.OnKey "a", "'Add 1'"
Application.OnKey "s", "'Add 2'"
Application.OnKey "d", "'Add 3'"
Application.OnKey "f", "'Add 4'"

End Sub


The above code passes a variable to the macro so it knows what key was pressed (i.e. a=1, s=2 etc.) Note that I did more than just add a numerical argument to the procedure call. The procedure 'Add' and its argument '1' (or 2,3,4) is embedded in single quotes, which is in turn embedded in double quotes. Without the quotes within quotes, it won't work.


Then the macro code change would be:

Sub Add(x)
    Range("A" & x).Value = Range("A" & x).Value + 1
End Sub


Same result as my previous post but just a little cleaner. If you need to customize something, I would be happy to help at no charge. Just be warned, I'm not a programmer and so I can't do any of the fancy stuff, but I can do what you have outlined.

[Lilwolf]

trouble you will find is it's not certified.

I work in software controlling manufacturing equipment in the semiconductor industry.  I had the same idea when people need to control / monitor just a few inputs... but nobody would go for it.

btw, there are certified equipment that does that for 150 bucks or so that will continue to work if dropped 20 feet in the middle of a hurrican...

the trouble isn't the hardware, its the software.

[Jim]

Thanks FractalWalk for your help it works just as I was hoping it would. I appreciate your offer and all of your assistance

We should have this completely up and running well ahead of schedule. Should give us a big help in knowing exactly where our production schedule stands.

Thanks for your input as well Lilwolf.   Being a small business if we are happy that it gives us what we need then that is enough for me.

Again thanks for all the help great board, great people, now I can spend some more time playing games then worrying about schedules.