Use Microsoft Office for Your Next Automation Project

Microsoft Office

Automate with Office

If a software development platform can call a DLL, it can be used to control the Expert I/O 1000. Microsoft Office with Visual Basic for Applications (VBA) is no exception.

For most people, Office would be the last tool they would consider for PC based automation, but in some cases it might be the best. For example, you could easily log data to an Excel spreadsheet or Access database as you collect it and once it’s in these applications, it’s simple to manipulate, plot and report the data.

Office provides two big advantages.

  1. Low start up cost.
  2. Short learning curve.

The main advantage of Office is its low start up cost. Practically every computer in existence has Microsoft Office installed. If you don’t need the features of a full fledged software development suite, you can save a bunch of money by using a tool that is already on your computer.

The other big advantage of Office is that many people who aren’t software engineers know a great deal about VBA. It is used all the time for office automation by non-engineers. Microsoft has given us a development tool that can be used by everyone, not just geeky software engineers.

Controlling the Expert I/O using VBA in Excel is very easy. Here is the entire code of a VBA macro I created. The entire subroutine is only about a dozen lines.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
    ' Expert I/O API.
    Declare Function eioOpen Lib "eio.dll" () As Long
    Declare Function eioClose Lib "eio.dll" () As Long
    Declare Function eioClaimInterfaceEz Lib "eio.dll" (ByVal ModelId As Integer, ByVal SerialNumber As Long, ByRef Handle As Long) As Long
    Declare Function eioReleaseInterface Lib "eio.dll" (ByVal Handle As Long) As Long
    Declare Function eioDioSetPull Lib "eio.dll" (ByVal Handle As Long, ByVal Port As Long, ByVal PullLevel As Long) As Long
    Declare Function eioDioSet Lib "eio.dll" (ByVal Handle As Long, ByVal Port As Long, ByVal PinLevels As Byte) As Long
    Declare Function eioDioGet Lib "eio.dll" (ByVal Handle As Long, ByVal Port As Long, ByRef PinLevels As Byte) As Long

    ' Constants
    Const MODEL_ID As Integer = 1
    Const SERIAL_NUMBER As Long = 99
    Const INPUT_PORT As Long = 0
    Const OUTPUT_PORT As Long = 2
    Const eioDIO_PULL_3_3 As Long = 1
    Const eioDIO_PULL_GND As Long = 2
   
Sub DigitalIo()
    Dim Handle As Long
    Dim PinLevels As Byte
    Dim ReturnCode As Long


    ' Open API and get handle. Normally, this would be done only once at the
    ' beginning of the program. It has been included here to keep the
    ' example as simple as possible.
    ReturnCode = eioOpen()
    ReturnCode = eioClaimInterfaceEz(MODEL_ID, SERIAL_NUMBER, Handle)

    ' Set digital pulls.
    ReturnCode = eioDioSetPull(Handle, INPUT_PORT, eioDIO_PULL_3_3)
    ReturnCode = eioDioSetPull(Handle, OUTPUT_PORT, eioDIO_PULL_3_3)

    ' Set output levels equal to cell in spreadsheet.
    PinLevels = Cells(3, 5)
    ReturnCode = eioDioSet(Handle, OUTPUT_PORT, PinLevels)
   
    ' Get input levels and set spreadsheet cell to the value read.
    ReturnCode = eioDioGet(Handle, INPUT_PORT, PinLevels)
    Cells(5, 5).Value = PinLevels
   
    ' Clean up. Normally, this would be done only once at the end of the
    ' program. It has been included here since we included eioOpen() and
    ' eioClaimInterfaceEz() in this subroutine.
    ReturnCode = eioReleaseInterface(Handle)
    ReturnCode = eioClose()

End Sub

Lines 27 and 28 initialize the Expert I/O API. Lines 31 and 32 set the output voltage level and input pull up state. These lines would normally be placed in a subroutine that runs once at the beginning of the macro. The initialization does not need to be performed for every I/O access.

Lines 35 and 36 set a digital output port to the value in the spreadsheet at E3.

Lines 39 and 40 read a digital input port and write its value to the spreadsheet at E5.

The interface is released and the API is closed with lines 45 and 46. As with the initialization, this cleanup only needs to be performed once at the end of the macro.

As you can see, writing a VBA macro to control the Expert I/O is an incredibly simple task. So for your next automation project, don’t forget to consider Microsoft Office. It might be just what you need.

Click here to download the Excel file that contains this macro.


Submit Story to Del.icio.us Share on Twitter Submit Story to StumbleUpon

Tags: , , , , , , , , ,

Comments are closed.


Call or Email       (317) 608-0500       sales@dajac.com