Showing results for 
Search instead for 
Do you mean 
Community Home Request Access Read Blogs Share Your Ideas Search Community View My Settings
Reply
New Member
Posts: 46
Registered: ‎01-11-2010
Accepted Solution

ExportGridToExcel Function

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?

Thanks,

Ademar.

Copper Super Contributor
Posts: 33
Registered: ‎11-26-2009

Re: ExportGridToExcel Function

[ Edited ]

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

New Member
Posts: 46
Registered: ‎01-11-2010

Re: ExportGridToExcel Function

[ Edited ]

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:

 

Sub btnExcelClick(Sender)
    ExportGridToExcel grdContracts, "Contract"
    ExportGridToExcel grdMerchandise, "Merchandise"
    ExportGridToExcel grdPrepCharge, "Prep Charge"
    ExportGridToExcel grdEmblem, "Emblem"
    ExportGridToExcel grdDelEnv, "Del & Env"
End Sub

Copper Super Contributor
Posts: 33
Registered: ‎11-26-2009

Re: ExportGridToExcel Function

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

New Member
Posts: 46
Registered: ‎01-11-2010

Re: ExportGridToExcel Function

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.

 

Grazie mille!

Silver Contributor
Posts: 835
Registered: ‎03-24-2009

Re: ExportGridToExcel Function

Here's what it is OOTB:

 

       'Ensure that Excel remains visible if we switch to the Active Sheet
       SetExcelActiveSheet(objExcel)
       'Set the SpreedSheet name, if specified
       If SheetName <> "" Then
          'Sheet name can be no more than 31 characters
          objExcel.Sheets(1).Name = SheetName
       End If

 

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

RJ Samp
Copper Super Contributor
Posts: 33
Registered: ‎11-26-2009

Re: ExportGridToExcel Function

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.

 

John H.

New Member
Posts: 46
Registered: ‎01-11-2010

Re: ExportGridToExcel Function

Wow sorry guys my bad. I missed this part:

 

Beneath that (on line 25), add "objSheet", like this:
  Dim objRS, objSheet

Thank you very much!

Copper Super Contributor
Posts: 33
Registered: ‎11-26-2009

Re: ExportGridToExcel Function

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?