02-04-2011 09:30 AM
I'm using the ExportGridToExcel function to output a grid to Excel but I'm having to output several grids and I only know to use the function to ouput to a separate Excel file for each grid. Is there a way to ouput several grids into the same Excel file? ... perhaps into separate sheets in the same file?
Solved! Go to Solution.
02-04-2011 06:02 PM - edited 02-04-2011 06:03 PM
The code that exports grids to Excel is editable - it's in a VBscript plugin called System:Export_Grid_RS_To_Excel. You'll have to copy and modify it so that for the second (and all subsequent) sheets, it uses the reference to the Excel COM object created for the first sheet, and also does something other than call SetExcelActiveSheet(objExcel) to get a reference to the sheet itself, since that's always going to write to the first sheet that appears when Excel starts up.
Let us know if you're not familiar enough with VBscript programming for COM objects to take it from there - I'm sure more than one of us here has some sample code lying around.
John H. Hedges
JH3 Software LLC
02-05-2011 05:03 AM - edited 02-05-2011 05:05 AM
Ok I took a look at that script and I'm not sure where to start. Could I impose on asking for help on this? Here's the sub that I'm using where I'd like to create one Excel file with several sheets instead of several separate Excel files:
ExportGridToExcel grdContracts, "Contract"
ExportGridToExcel grdMerchandise, "Merchandise"
ExportGridToExcel grdPrepCharge, "Prep Charge"
ExportGridToExcel grdEmblem, "Emblem"
ExportGridToExcel grdDelEnv, "Del & Env"
02-07-2011 01:02 AM
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
02-07-2011 08:56 AM
Wow thanks for the detailed response. That worked great! I can go home early today.
Is it possible to have the sheet's named? Right now it gives me the following:
Sheet7 | Sheet6 | Sheet5 | Sheet4 | Sheet3
But if possible this would be best:
Contract | Merchandise | Prep Charge | Emblem | Del & Env
But you proly knew I was going to ask that.
02-07-2011 10:52 AM
Here's what it is OOTB:
'Ensure that Excel remains visible if we switch to the Active Sheet
'Set the SpreedSheet name, if specified
If SheetName <> "" Then
'Sheet name can be no more than 31 characters
objExcel.Sheets(1).Name = SheetName
So after you create each sheet, probably place your input parameter sheet name into the correct Sheet (1 based, so 7, 6, 5,4, 3, 2,1 ....) objExcel.Sheets(7).Name = Sheet7Name
02-07-2011 10:56 AM
It's not renaming the sheets? That's what this last line is for:
If SheetName <> "" Then objSheet.Name = SheetName
So if it's not doing that... are you sure you copied the whole thing out of the code box above, including that last line? That's the only explanation I can think of.
I mean, if worse came to worst you could always try referencing the sheets by name, after they're all in place, as in:
objExcel.Workbooks(1).Sheets("Sheet3").Name = "Contracts"
...but that would be a rather ugly band-aid solution, and it shouldn't be necessary if things are working the way they're supposed to.
02-07-2011 04:10 PM
Oh, right, I forgot that sometimes it doesn't give you "Object required" error messages for those things. They really should fix that one of these days, but then again, who am I to talk?