Using the Excel Application Object with SalesLogix

Jason Buss — Thu, 19 Feb 2009 14:16:00 GMT

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!

Jason Buss' Blog : SalesLogix, scripting, Application Objects, How-To

Using the Excel Application Object with SalesLogix