Database Search

If you can't find the information using the categories below, post a question over in our WinBatch Tech Support Forum.

TechHome

WinBatch

OLE COM ADO CDO ADSI LDAP

OLE with Excel

Samples from Users

Can't find the information you are looking for here? Then leave a message over on our WinBatch Tech Support Forum.

Using OLE Properties that Take Parameters with Excel

Keywords: 	 OLE Parameters Excel

Question:

I have been having some problems using OLE with Microsoft Excel 97. In this example, I am trying to change the Font.Size property of cell A1 in an open workbook. I'm using WinBatch 98B.
Microsoft Excel should be running with an open workbook to do this test This code will paste "TEST" into cell A1. Then it will use OLE to set the font size for the active cell (A1) to 14. Since there are no parameters to be passed, this works fine.
WinActivate ("~Microsoft Excel") sendkey ("^{HOME}") ClipPut ("TEST") sendkey ("^v") Application = ObjectAccess ("Excel.Application", @FALSE) ActiveCell = Application.Selection ActiveCellFont = ActiveCell.Font ActiveCellFont.Size = 14 Exit
This code does the same thing, except it uses the Cells method of the Application object to specify a Range, and the Select method of the Range object to make that range the active cell. This produces error# 1255 "Ole: Member not found". However, the Application object does have a Cells method. This technique works in Visual Basic.
WinActivate ("~Microsoft Excel") Application = ObjectAccess ("Excel.Application", @FALSE) Column = 1 Row = 1 ApplicationCells = Application.Cells(Row,Column) ApplicationCells.Select ClipPut ("TEST") sendkey ("^v") ActiveCell = Application.Selection ActiveCellFont = ActiveCell.Font ActiveCellFont.Size = 14 Exit
This time, instead of trying to assign the Range returned by the Cells method to a variable, we'll try to get at the Select method directly This produces error# 1255 "Ole: Member not found". This technique works in Visual Basic

	WinActivate ("~Microsoft Excel")
	Application = ObjectAccess ("Excel.Application", @FALSE)
	Column = 1
	Row = 1
	Application.Cells(Row,Column).Select
	ClipPut ("TEST")
	sendkey ("^v")
	ActiveCell = Application.Selection
	ActiveCellFont = ActiveCell.Font
	ActiveCellFont.Size = 14
	Exit

Answer:

In versions prior to Winbatch 99, we don't support properties which take parameters, which is what he's doing here:

	Application.Cells(Row,Column)

But in Winbatch 99, you can do it.

Here is a simple example with a single parameter:

    ;Start EXCEL and display it to the user
    oXL = ObjectOpen("EXCEL.application")
    oXL.Visible = @True

	 TextXL=strcat(curdir, "DELUSERS.XLS")

    ;Open the workbook 
    zWorkbooks=oXL.workbooks
	 zWorkbooks.open(myTextXL) ;where TextXL is the parameter

And here are the docs on using OLE with Named Parameters:

OLE functions support up to 20 parameters.

OLE functions support "omitted optional positional parameters", using commas as placeholders. For example:

	Application.Display("Hello", 100, 100, , 1)

OLE functions support named parameters. The syntax structure is:

	Object.Method(p1, p2 :: n3 = v3, n4 = v4)

Positional parameters (shown as p1 and p2), if any, come first, followed by a double colon ("::"), and then any named parameters (n3 and n4). Each named parameter is followed by an equals sign ("=") and then the value for the parameter (v3 and v4). Whitespace is ignored. Here are some examples:

	; 2 positional parameters
	Application.InputBox("My Prompt", "My Title")

	; 2 positional parameters and 2 named parameters
	Application.InputBox("My Prompt", "My Title" :: Left = 40, Top = 300)

	; no positional parameters and 2 named parameters
	;(note the leading colon)
	Application.InputBox(:: Prompt = "My Prompt", Title = "My Title")

OLE functions allow you to specify a type for integer parameters. Normally, when you specify an integer value as a parameter to an OLE function, WIL passes it as a type VT_I4 (4-byte integer value). If the OLE application was expecting the parameter to be a different type of numeric value, in most cases it will automatically convert it to the desired type. However, there are some OLE applications which don't do this, and return an error instead. In such cases, you can specify the type, using the following syntax:

	Object.Method(p1, t2:p2 :: n3 = v3, t4:n4 = v4)

The parameter type (shown as t2 and t4) is followed by a single colon (":"), and then the parameter itself (p2 and n4). This can be done for both positional parameters (p2) and named parameters (n4). Whitespace is ignored, and the type names are not case-sensitive. Here are the types which may be specified for integer parameters:

Type	Name		Meaning

UI1	VT_UI1	An unsigned 1-byte character. (32-bit
version only)

I2	VT_I2		A 2-byte integer value.
I4	VT_I4		A 4-byte integer value.
BOOL	VT_BOOL	A Boolean (True/False) value.

The type name that gets specified in the WIL command is the same as the equivalent OLE name, but without the leading "VT_". The default type for integer values is "I4" (VT_I4).

In addition, you can specify a date type for string parameters, for OLE functions which require a parameter of type VT_DATE:

Type	Name		Meaning

DATE	VT_DATE	A date/time string, in Ymd or YmdHms format.

Here are some examples:
	; this function requires a parameter of type VT_I2
	Application.CreateImageViewerObject(I2:1)

	; this function requires a (named) parameter of type VT_DATE
	Application.CreateLog("access.log" :: DATE:startdate="97:11:01")


Return values from OLE functions which have a return type of VT_DATE are now automatically converted to a YmdHms string in the form "YYYY:MM:DD:HH:MM:SS" (this will always contain a 4-digit year, regardless of the setting specified by IntControl 41).





Article ID:   W13676

Filename:   OLE with Parameters and Excel.txt
File Created: 1999:04:15:16:55:38
Last Updated: 1999:04:15:16:55:38