Using the Excel Application Object with SalesLogix

The Excel application object gives you the ability to programmatically create, modify and manipulate Excel spreadsheets.  Use that object with SalesLogix, and you have a powerful tool for exporting SLX data.

This post will outline the basics for creating and populating a new Excel spreadsheet, and will also provide an example of doing so with SalesLogix data.

First of all, we need to instantiate the object:

    Set xlApp = CreateObject(“Excel.Application”)

Afterwards, we need to create a new worksheet, and make the spreadsheet editable by the end user:

    Set xlWb = xlApp.Workbooks.Add
    Set xlWs = xlWb.Worksheets(“Sheet1”)

These next lines make the spreadsheet visible and editable to the end user:
    xlApp.Visible = True
    xlApp.UserControl = True

At this point, populating individual cells in the active worksheet is simple:

    xlWs.Cells(1, 1).Value = “Test”

A value of “Test” is put in position 1, 1 (or A1).  All you really need to do at this point is repeat that last line for whichever cells you want to populate.  As an example, here is code that will generate a list of Contacts for a specific account:

Dim objRS, iCount, xlApp, xlWb, xlWs

Set xlApp = CreateObject(“Excel.Application”) 

Set xlWb = xlApp.Workbooks.Add
Set xlWs = xlWb.Worksheets(“Contacts”)

xlApp.Visible = True
xlApp.UserControl = True 

After setting up the Excel object, we are setting up a header row:

xlWs.Rows(cnt).Font.Bold = True
xlWs.Cells(1, 1).Value = “First Name”
xlWs.Cells(1, 2).Value = “Last Name”
xlWs.Cells(1, 3).Value = “Title”
xlWs.Cells(1, 4).Value = “Work Phone”
xlWs.Cells(1, 5).Value = “Type”

Then we create a new recordset containing contact records, and loop through that recordset to populate the new spreadsheet.

Set objRS =Application.CreateObject(“ADODB.RecordSet”)

With objRS

     .Open “select firstname, lastname, title, workphone, type from contact where
accountid = ‘” & Application.BasicFunctions.CurrentAccountID & “‘”, Application.GetNewConnection

     iCount = 2

     While not (.BOF or .EOF)

          xlWs.Cells(iCount, 1).Value = .Fields(“firstname”).Value
          xlWs.Cells(iCount, 2).Value = .Fields(“lastname”).Value
          xlWs.Cells(iCount, 3).Value = .Fields(“title”).Value
          xlWs.Cells(iCount, 4).Value = .Fields(“workphone”).Value
          xlWs.Cells(iCount, 5).Value = .Fields(“type”).Value

          iCount = iCount + 1

          .MoveNext

     Wend

     .Close

End With

Set xlWs = Nothing

Set xlWb = Nothing
Set xlApp = Nothing

Set objRS = Nothing

That’s all there is to it!

There is quite a bit more to the Excel application object that I have not touched on here.  I would suggest reviewing available resources to see what other options are available to you.  A good place to start would be here:  http://msdn.microsoft.com/en-us/library/aa213696(office.11).aspx

Thanks for reading!  [:)]