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:
Adding a header row is equally as easy:
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:
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.