Dump Grid Data to Excel – Revisited

In a previous post, Quick & Dirty Way to Dump a Grid to Excel, I outlined a quick and easy way to get your data from a SalesLogix Grid into Excel. I’ve been asked a few questions related to how to modify that to work differently, but the most common is how to remove unwanted columns and add a header row, such as this comment from Richard Weck of Harris Technology:

This is great.  How do I programmatically delete the “A” column and then insert a new row at the top?  I want to be able to put in column headings when this is done.

Deleting a column is easy enough since we already have a reference to the Worksheet:

xlWs.Columns("A").Delete

Adding a header row is equally as easy:

xlWs.Rows("1").Insert

Now the part to add the column headers for each field in the Recordset. To do this we will loop through each field object in the Recordset’s fields collection and add the field name to the new row in the Worksheet.

Set rs = DataGrid1.Recordset
For i = 1 to rs.Fields.Count - 1
     xlWs.Cells(1, i).Value = rs.Fields(i).Name
Next

Since we deleted the first column from the Worksheet, we start our loop at 1, instead of 0, so we skip the first field. That’s it. We’ve now deleted the first column (which would be the ID column) and then added a header row. Another alternative would be to use the Captions of the Column headers instead of the field names in the Recordset to allow for “prettier“ & more meaningful values for the header. The complete code would look like this:

Dim xlApp
Dim xlWb
Dim xlWs
Dim rs
Dim i

    Set rs = DataGrid1.Recordset

    Set xlApp = CreateObject("Excel.Application")
    Set xlWb = xlApp.Workbooks.Add
    Set xlWs = xlWb.Worksheets("Sheet1")

    xlApp.Visible = True
    xlApp.UserControl = True

    'here's the magic
    xlWs.Cells.CopyFromRecordset rs

    'now delete the first column
    xlWs.Columns("A").Delete

    'and insert a new row
    xlWs.Rows("1").Insert

    'now let's add the columns headers
    For i = 1 to rs.Fields.Count - 1
         xlWs.Cells(1, i).Value = rs.Fields(i).Name
    Next

    'now auto-fit the width of the columns
    xlApp.Selection.CurrentRegion.Columns.AutoFit

    Set xlWs = Nothing
    Set xlWb = Nothing
    Set xlApp = Nothing

There are many other options we have here. We can apply formatting to cells or anything. Easy enough to accomplish. One cool option is to sent the Excel spreadsheet to the printer and then get rid of it. The user never even sees that Excel was involved at all. All they know is that they clicked a button and the grid data magically prints out.

To do this we need to make Excel invisible when it creates the Worksheet, and also supress any prompts to the user. Then create the worksheet as above, then print it and quit Excel (throwing away the Spreadsheet). Supressing prompts & making Excel invisible is easy. Just add these lines after creating the Excel Worksheet:

xlApp.Visible = False
xlApp.UserControl = False
xlApp.DisplayAlerts = False

Then before setting the object references to Nothing, add the following:

xlWs.PrintOut
xlApp.Quit

That’s it. All magic 😉

Download sample code in a bundle. This bundle demonstrates how to both view the grid data in Excel as well as do a background print. It also uses the grid column captions for the header instead of the actual field names. Enjoy.

ABOUT THE AUTHOR

Ryan Farley

Ryan Farley is the Director of Development for Customer FX and creator of slxdeveloper.com. He's been blogging regularly about SalesLogix, now Infor CRM, since 2001 and believes in sharing with the community. His new passion for CRM is Creatio, formerly bpm'online. He loves C#, Javascript, web development, open source, and Linux. He also loves his hobby as an amateur filmmaker.

1 Comment

  1. Thanks so much, thats great. One more issue though.

    When the data comes into Excel it seems to be stored as TEXT. If I change the format for some cell to say Phone Number, the cells do not change unless I go in to each one and click in the cell and hit enter.

    One trick to fix this I found is to copy the data and then do a paste special (Add). Which will convert the data to numeric form. But I am having trouble figuring out how to do this from within SalesLogix.

    The following gives a syntax error in SalesLogix:

    xlApp.Selection.PasteSpecial Paste:=xlAll, Operation:=xlAdd, SkipBlanks:=False, Transpose:=False

    it does not like the ":=". How can I pass these variants to excel.

    Thanks so much again.

    -Jim

    Reply

Submit a Comment

Your email address will not be published. Required fields are marked *

Subscribe To Our Newsletter

Join our mailing list to receive the latest Infor CRM (Saleslogix) and Creatio (bpm'online) news and product updates!

You have Successfully Subscribed!