+1-757-461-3022 x124

Tutorial: Using Microsoft Excel with API views

Many a database administrator and ERP consultant has gnashed their teeth over the love affair the business world has with Excel. End users frustrated with the rigidity of highly structured database clients often resort to keeping separate spreadsheets of identical information because it allows them add formulas, highlights and other custom attributes at will. No amount of features added to the ERP system will convince these people to part with their beloved spreadsheets and rely solely on the xTuple Application client. If we can not bring the spreadsheet to xTuple, why don't we bring xTuple to the spreadsheet?

Below are a couple examples of quick-and-dirty spreadsheet applications using Visual Basic for Applications (VBA) macros to connect to an xTuple database. Though they are fully functional, they are not necessarily meant to be deployed in a production environment, but rather to give ideas as to how the xTuple API can be used to integrate office applications to xTuple ERP.

Interactive Customer List

This Office 2003 spreadsheet will allow a user to load the list of customers in our PostBooks® demo database alter them or add new ones, then save the information back to PostBooks®. A copy should have been included in the zip file you downloaded from the introduction page. To run it you must:

  • Change the credentials on the "Login" tab
  • Use ctrl+l to "Load" your customer data
  • Use ctrl+s to "Save" your changes back to the database

Note: You will need to configure Microsoft Office to enable Macros for this application to work correctly.

The examples shown below are built in an Office 2007 environment. To build this application from scratch, do the following:

Add Header fields to Customers page

APIexcel1.png

Open a new Excel spreadsheet workbook and change the name of the first page to "Customers." Enter header titles on the first row as shown.

Add login credentials to Login page

APIexcel2.png

Change the name of the second page to "Login" and enter database connection parameter names in column "A" as shown. The actual values in column "B" should reflect your local database connection parameters.

Now click on the "View" tab in Excel and select the macros drop down as circled above and select "View Macros."

Create the Load macro

APIexcel3.png

On the Macro name line type "Load" and click "Create."

Add Reference to ADODB

APIexcel4.png

The VBA Editor should appear. Before we can add our macro code, we need to include a reference use ActiveX data objects. Go to Tools > References to bring up the references dialog. Scroll down and select the Microsoft ActiveX Data Objects 2.5 library. There will likely be several versions. We used 2.5 library for this example so it would also work on older installations of Office, but you should be able to use the latest version available. Click "OK."

Now it's time to add code. This discussion assumes the user has an understanding of Visual Basic and ActiveX data objects (ADODB). These are very broad and deep topics that goes beyond the scope of this paper, but for which thousands of articles and books have been written. We have, however, included comments to help users new to these technologies follow along on what's happening

First we'll need to add some global variables. We need to do this so Excel can maintain the connection and data between the time when we load our data, change it and send it back to the database. Type or paste this code above the Load sub routine that was created:

  'Set up Global data objects
  Public pbConn As New ADODB.Connection
  Public custRcd As New ADODB.Recordset
  Public strConn

Next we'll type or paste in the code to get customer data into our Load subroutine:

Sub Load()

    'Get Connection information from worksheet
    If strConn = "" Then
        strConn = "Driver=" & Sheet2.Range("B1") & ";" & _
                "Server=" & Sheet2.Range("B2") & ";" & _
                "Port=" & Sheet2.Range("B3") & ";" & _
                "Database=" & Sheet2.Range("B4") & ";" & _
                "Uid=" & Sheet2.Range("B5") & ";" & _
                "Pwd=" & Sheet2.Range("B6") & ";"

        'Pass connection parameters and Open the connection
        pbConn.Open strConn
    Else
        'Reset the connection
        pbConn.Close
        pbConn.Open strConn
    End If

    'Get the data
    custRcd.Open "SELECT * FROM api.customer ORDER BY customer_number", _
                 pbConn, adOpenDynamic, adLockPessimistic

    'Clear the worksheet
    Range("A2", "I50000").ClearContents

    Dim intRow
    intRow = 2

    'Now populate new data
    Do While Not custRcd.EOF 'Start looping through the records until we reach 'End of File'

        Range("A" & CStr(intRow)).Select
        ActiveCell.Value = custRcd("customer_number")

        Range("B" & CStr(intRow)).Select
        ActiveCell.Value = custRcd("customer_name")

        Range("C" & CStr(intRow)).Select
        ActiveCell.Value = custRcd("customer_type")

        Range("D" & CStr(intRow)).Select
        ActiveCell.Value = custRcd("sales_rep")

        Range("E" & CStr(intRow)).Select
        ActiveCell.Value = custRcd("default_terms")

        Range("F" & CStr(intRow)).Select
        ActiveCell.Value = custRcd("ship_via")

        Range("G" & CStr(intRow)).Select
        ActiveCell.Value = custRcd("credit_limit")

        Range("H" & CStr(intRow)).Select
        ActiveCell.Value = custRcd("credit_rating")

        Range("I" & CStr(intRow)).Select
        ActiveCell.Value = custRcd("notes")

        intRow = intRow + 1
        custRcd.MoveNext  'move on to the next customer
    Loop

    'Move focus to top
    Range("A2").Select

End Sub

The result should look something like this:

Visual Basic Editor

APIexcel5.png

Next let's go ahead and create the "Save" sub routine to upload our changes. Type or paste this code after the end of the Load procedure:

Sub Save()
  Dim intRow
  intRow = 2

  On Error GoTo ErrorHandler

  'Move back to the first customer record and loop through
  'to update from changes made on Excel
  Range("A" & CStr(intRow)).Select      'Move focus to first cell
  custRcd.MoveFirst                     'Move to first customer record in xTuple
  Do While Not ActiveCell.Value = ""  'Loop through spreadsheet as long as we find something

    If custRcd.EOF Then                 'If no xTuple customers left, must be a new one
      custRcd.AddNew
    End If

    custRcd("customer_number") = ActiveCell.Value

    Range("B" & CStr(intRow)).Select
    custRcd("customer_name") = ActiveCell.Value

    Range("C" & CStr(intRow)).Select
    custRcd("customer_type") = ActiveCell.Value

    Range("D" & CStr(intRow)).Select
    custRcd("sales_rep") = ActiveCell.Value

    Range("E" & CStr(intRow)).Select
    custRcd("default_terms") = ActiveCell.Value

    Range("F" & CStr(intRow)).Select
    If ActiveCell.Value <> "" Then        'Ignore blanks so we get default
      custRcd("ship_via") = ActiveCell.Value
    End If

    Range("G" & CStr(intRow)).Select
    If ActiveCell.Value <> "" Then        'Ignore blanks se we get default
        custRcd("credit_limit") = ActiveCell.Value
    End If

    Range("H" & CStr(intRow)).Select
    If ActiveCell.Value <> "" Then        'Ignore blanks se we get default
        custRcd("credit_rating") = ActiveCell.Value
    End If

    Range("I" & CStr(intRow)).Select
    custRcd("notes") = ActiveCell.Value

    intRow = intRow + 1                     'Increment row
    Range("A" & CStr(intRow)).Select        'Move focus back to first column for next loop
    custRcd.MoveNext                        'Move on to the next xTuple customer
  Loop                                      'Look Ma, No SQL!

  'Refresh the data
  Load

  Exit Sub

ErrorHandler:
  MsgBox Err & ": " & Error(Err)
  custRcd.CancelUpdate

End Sub

Save your changes and close the Visual Basic editor. From Excel, go back to view the macros and run the Load macro. Your spreadsheet should be populated with the list of customers from your database!

To make things a bit easier, from the macro list highlight the Load macro and select Options. Type "l" in for the shortcut key. Do the same for the Save macro using "s."

Now with the quick ctrl+l keystroke you can refresh your customer list. Try making changes to a customer name, then ctrl+s to save them. Try entering an invalid customer type and save. What happens? Enter a new customer line and save. Note the api has the same validation rules as the UI, but none of that had to be dealt with explicitly in the macro code. It's all taken care of by the xTuple API and ActiveX objects.

Electronic Order Form

The electronic order entry form is a concept that could potentially solve a couple of problems. One is a situation where users don't want all the features in the standard client and simply want a stripped down order entry screen. With some improved security measures, this idea could also be the beginning of a solution where customers could fill out this form and email it to customer service where it could be uploaded into an xTuple database with only a keystroke. To use it simply do the following:

  • Change the credentials on the login tab to point to your local environment
  • Back on the order form tab, enter the required order information.
  • Hit ctrl+s keys to save.

We won't bother walking through the steps of setting up this application as it is pretty much the same as the customer list above. You can review the comments in the macro code to see how it works. However there is an important configuration change in PostBooks® that needs to be made to allow this application to work:.

Sales Configuration

APIexcel6.png

In xTuple ERP, go to System > Configure Modules > Sales and make sure Sales Order# Generation is set to "Automatic, Allow Override." This will allow the Excel application to assign an order number.

Electronic Order Form

APIexcel7.png

Some interesting things to note about this form are that first if a customer number is not specified, or one is but is not found, both the customer and the order are created together on save. Also note the order number is automatically generated, and some error trapping is included that transcends xTuple logic (like making the contact information required) for this particular business need.

Other Examples

Hopefully this tutorial has helped you understand better the flexibility and benefits of using xTuple databas API views. We think it would be helpful if the community could help contribute other application examples written in languages such as Java, .NET, Python, or Ruby. In addition, it would also be useful to see examples of object wrappers for these views, which should be a very straightforward task since no translation or mapping would be required. If you have an interest in adding to our library of examples, please post your idea on our developer forum.

See xTuple Training.

up
85 users have voted.