Hmm... I was afraid you'd ask that! There are certain limitations and idiosyncrasies involved, actually. I suppose in your situation, I would go about it this-a-way:
First, I'd make a copy of the Export_Grid_RS_To_Excel VBscript plugin, using the "Save Plugin As..." command in Architect. You could change the existing plugin, but I wouldn't recommend it unless you're committed to maintaining it across multiple SalesLogix upgrades. Call the new version "Export_Grid_RS_To_Excel EN" ("EN" for "Enhanced," but if anyone asks you can tell them it actually means "English" or "Extra-Nerdy.")
Next, you'll want to change the "ExportGridToExcel" function itself, adding parameters and some code that will tell it to not create a new Excel object each time. (As you can see on line 14, the "objExcel" object variable is declared at the script level, not the function level, so this is perfectly legal in most countries.)
Change the function declaration (on line 22) to:
Sub ExportGridToExcel(objGrid, SheetName, bNewInstance, bLastSheet)
Beneath that (on line 25), add "objSheet", like this:
Dim objRS, objSheet
Now, go down to line 44 and replace that line, and the 11 lines after it, with this:
If bNewInstance Then
If Not CreateExcelObject Then Exit Sub
objExcel.Visible = True
objExcel.Workbooks.Add
Application.BasicFunctions.ProcessWindowMessages
If objExcel.Workbooks(1).Sheets.Count > 1 Then
For intRow = 1 To objExcel.Workbooks(1).Sheets.Count - 1
objExcel.Workbooks(1).Sheets(1).Delete
Next
End If
Else
objExcel.Workbooks(1).Sheets.Add
End If
Set objSheet = objExcel.Workbooks(1).Sheets(1)
If SheetName <> "" Then objSheet.Name = SheetName
Go down to the end of the function and where it says "Set objExcel = Nothing," change it to:
Set objSheet = Nothing
If bLastSheet Then Set objExcel = Nothing
Finally, save and release the new plugin.
Okay, you're nearly there - open the form with all the grids on it and click the "Include Script" button. Remove the "Export_Grid_RS_To_Excel" reference and add "Export_Grid_RS_To_Excel EN" instead. Now, all that's left is to change your calling function to look like the code below. Take note of the fact that the sheets must be added in reverse order:
Sub btnExcelClick(Sender)
ExportGridToExcel grdDelEnv, "Del & Env", True, False
ExportGridToExcel grdEmblem, "Emblem", False, False
ExportGridToExcel grdPrepCharge, "Prep Charge", False, False
ExportGridToExcel grdMerchandise, "Merchandise", False, False
ExportGridToExcel grdContracts, "Contract", False, True
End Sub
And you're done!
You might be curious as to why you have to add the sheets in reverse order. It's because many parts of the Excel COM API aren't particularly well-thought-out, including the part where you add a sheet programmatically. The .Add method takes two integer parameters, intBefore and intAfter. But the only way to specify intAfter is to not specify intBefore at all (i.e., "objExcel.Workbooks(1).Sheets.Add ,3"), and the SalesLogix VBScript implementation includes a script parser that won't let you do that (i.e., not specify the first of two or more parameters). SLX VBScript also doesn't support explicit parameter-naming (i.e., "objExcel.Workbooks(1).Sheets.Add after:=3"). So, all newly-added sheets are inserted at the beginning, and there isn't a whole lot you can do about it, AFAIK (if I'm wrong, I'm sure someone will correct me). Meanwhile, Excel also doesn't let you control the number of sheets that are created for you when you start a new workbook - that's a per-user setting. So, the trick is to delete all but one of the auto-created sheets, noting that there might only be one to begin with; use the remaining sheet for the last of your grids, and then add each additional sheet in reverse order.
It's stupid, but that's to be expected from a Microsoft product paired with an 8-year-old parser.
John H. Hedges
JH3 Software LLC
