Quick and Dirty Way to Dump a Grid to Excel

If you’re looking for just a quick way to dump it out and don’t care if it is pretty or anything, then you can use Excel’s CopyFromRecordset method which copies a recordset into a range of cells.

Note that this only works in Excel 2000 or higher (Excel 97 only supported DAO recordsets, so you’ll get an error). You can pull the recordset out of the grid and pass it off to Excel. Pretty quick way to get it done.

 

Dim xlApp
Dim xlWb
Dim xlWs

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 DataGrid1.Recordset

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

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

 

You’ll notice however, that this will dump any fields in the recordset out to Excel, including any hidden ID fields. But at least it is a quick way to get it done. I suppose you could always clone the recordset to get rid of any undesired fields.

Of course another quick way is to take the code from the existing VBScript plugin that exports groups to Excel and modify it to fit your needs. However, this way is a quick way to get that done and can easily be added to a generic include script that yo can add anytime to you need to dump a SalesLogix grid to Excel.

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. Awesome, works like a charm!

    I’d been futzing around with TempGroups etc., but this is much better. And now that I understand you can actually manipulate the excel spreadsheet, I can do much more in terms of formatting etc. Thanks.

    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!