Excel Export-1. OLE/COM tip

From LDD..
http://www-10.lotus.com/ldd/46dom.nsf/DateAllFlatweb/dc09168723293c9885256b9e004dd58b?OpenDocument

Here's an OLE/COM tip for you all: How to get around the ":=" that VB uses to set parameters in Lotus NotesPosted by Brandt S Fundak on 17.Apr.02 at 10:10 AM using a Web browserCategory: Domino Designer -- LotusScriptRelease: All ReleasesPlatform: All Platforms

There have been a few questions about how to get around parameters in Visual Basic methods and properties (set through recording macros in excel, word and powerpoint, among others) that are set with the ":=" operator in the past few weeks, so I thought I would post this tip on how to get around it. Maybe this will help someone when using the forum search.We've all heard that most of the stuff that you can do in Visual Basic we can do in LotusScript, but are often stymied by the ":=" operator, which LotusScript does not recognize. It is not that LotusScript can not use those parameters, but we must know HOW to tell LotusScript what the parameters are.Since LotusScript will not recognize the ":=" operator, we must use the only method of passing parameters that LotusScript knows: Parentheses "()"So let's say you have a method in Excel that has parameters. Let's say we use the Add method of the worksheets object in Excel. If we look at the help file on the Add method we find this syntax:expression.Add(Before, After, Count, Type)Of course, this is syntax is something that Notes can understand. However, when we go into the help example to see how we set our parameters, we are confronted with this:This example adds a new worksheet after the last worksheet in the active workbook.Worksheets.Add.Move after:=Worksheets(Worksheets.Count)Now we have a quandry. Let's say I want to follow the example and have the after parameter put my new worksheet after all existing sheets. LotusScript doesn't understand ":=" and if I follow the syntax as listed above, like so:expression.Add(After)it still drops the sheet BEFORE my existing sheets.We're really confronted by two problems here. First, we have to figure out how to set the After parameter. Since it is a variant object that is supposed to represent an existing sheet, we can easily set the after parameter the same way we set xlsheet objects in LotusScript:Dim after as variant...set after = xlapp.workbooks(1).worksheets("existingsheet")Now we have our after worksheet set, but when I use the line:xlapp.workbooks(1).worksheets.Add(after)it STILL puts the worksheet before the existing sheets. this is because the "before" parameter is not optional. Instead of the above you must instead use this:xlapp.workbooks(1).worksheets.Add(NULL,after)This line will correctly set the sheet. Passing NULL to the parameter will notify the program that there is no "Before" parameter. [Note: You do not have to name the parameters "before" or "after". They can be anything you want...they just have to be in the right location in the parameter listing.]The above translation of VB's ":=" operator works for any OLE method and property that has parameters. If you see ":=" in a recorded macro's code, find out what data type you are working with (the Object Browser help file is really helpful here) and then modify the code to set the variable. Unless the value is a constant (in which case you would have to pass the actual constant value, and not the constant) this will work.A good way to think of it is that when you see ":=" it's usually the equivalent of the LotusScript keyword "Set." So if you see the ":=" operator, just figure out how you can change the line to reflect the "Set" keyword instead, like in the example above.For reference, here are some of my other posts on the subject in the last couple of weeks (as well as one from about a year ago.)sample script for Excel:http://www.notes.net/46dom.nsf/55c38d716d632d9b8525689b005ba1c0/9dd101c8a69d20ec852569af0065ad88?OpenDocument&Highlight=0,excel,brandtan explanation of the above script (most of the information duplicated here):http://www.notes.net/46dom.nsf/ShowMyTopicsAllFlatweb/ffaaf962e0e171ba85256b9000665cec?OpenDocumenta discussion on how to use the above techniques in powerpoint:http://www.notes.net/46dom.nsf/ShowMyTopicsAllFlatweb/c2d7c421890934ea85256b9c00534e4e?OpenDocumentI hope someone finds this helpful.brandt