Setting Borders in Excel

I'm trying to do some tweaks to an Traceability Excel output using DXL.

I.m currently using an Ole Export to Excel and want to be able to put borders around particular ranges of cells. (ideally just a thick line at the top)

I can get a border around every cell in a range.

current code below:

void groupRow (int startGroupLvl, stopGroupLvl) {
 
    groupString = (startGroupLvl + 1) ":" stopGroupLvl ""
        clear args
 
        put (args, groupString)
        oleGet (objSheet, "Range", args, objRange)
        if (!null objRange) {
                oleMethod (objRange, "Select")
                oleMethod (objRange, "Group")
 
                checkRes (oleGet (objRange, cPropertyBorders, objBorders))
 
                checkRes (olePut (objBorders, cPropertyColorIndex, xlAutomatic))
 
        }
}

cheers Ben

Re: Setting Borders in Excel
Mathias Mamsch - Sun Dec 06 14:11:20 EST 2009

If I understood you right, what you want to achieve is putting the border around the "outside" of a range, instead of every cell in a range.

Checkout the "BorderAround" Method of the Range object. In VB this code works fine:

Selection.BorderAround xlContinuous, xlThin, xlColorIndexAutomatic, 0

You just need to translate to DXL calls. But this is more an MS Office programming question than a DXL question, so this forum may not be the ideal place.

Regards, Mathias

Re: Setting Borders in Excel
Ben_Sharples - Mon Dec 07 05:00:38 EST 2009

Selection.BorderAround xlContinuous, xlThin, xlColorIndexAutomatic, 0

The VBA side of things is fine. Just can't invoke in DXL.

Could just be that I am relatively new to OLE automation from DXL and translating from VBA to DXL.

tried putting the whole of your VBA code into an oleMethod call

oleMethod (objRange, "BorderAround" xlContinuous, xlThin, xlColorIndexAutomatic, 0)

which compiles fine but does nothing

Re: Setting Borders in Excel
Mathias Mamsch - Mon Dec 07 06:35:41 EST 2009

oleMethod (objRange, "BorderAround" xlContinuous, xlThin, xlColorIndexAutomatic, 0)

Hmm ... that should not 'compile' fine. OleMethod comes in 3 different flavors:
The first form compares to "var = obj.funcName(arguments)" in VB which is effectively like a function call in VB. The result in the call will be stored in 'var'. The return values of the oleMethod should be null, otherwise there was an error.
 

string oleMethod (OleAutoObj obj, string funcName, OleAutoArgs arguments, char& var)
string oleMethod (OleAutoObj obj, string funcName, OleAutoArgs arguments, bool& var)
string oleMethod (OleAutoObj obj, string funcName, OleAutoArgs arguments, int& var)
string oleMethod (OleAutoObj obj, string funcName, OleAutoArgs arguments, OleAutoObj& var)
string oleMethod (OleAutoObj obj, string funcName, OleAutoArgs arguments, string& var)

The second form is like the call of a sub in vb with no arguments: "obj.funcName arguments":

string oleMethod (OleAutoObj obj, string funcName, OleAutoArgs arguments)

The third form is like a vb sub call without arguments: "obj.funcName"

string oleMethod (OleAutoObj obj, string funcName)

So your code would roughly tranlate to:

OleAutoArgs arguments = create() 
  olePut (arguments, 1) // xlContinuous
  olePut (arguments, 1) // xlThin
  olePut (arguments, -4105 ) // xlColorIndexAutomatic
  oleMethod (objRange, "BorderAround", arguments)
delete arguments

Regards, Mathias

Re: Setting Borders in Excel
tommy3824 - Mon Dec 07 09:29:52 EST 2009

When you are getting the "Borders" object, you can pass an argument set in the OleAutoArgs variable ('args' in this case) to get which border you want to set.
10 xlEdgeRight
7 xlEdgeLeft
8 xlEdgeTop
9 xlEdgeBottom
5 xlDiagonalDown "\"
6 xlDiagonalUp "/"
 

clear args
put(args,xlEdgeTop)  // 8
checkRes (oleGet (objRange, cPropertyBorders,args, objBorders))

To get your border to show up, you need to set the "LineStyle" property. The default is xlNone. You can also set the "Weight" property to make your border thick. These are set the same way you set the "ColorIndex" property.

checkRes (olePut (objBorders, "LineStyle", xlContinuous))  // 1
checkRes (olePut (objBorders, "Weight", xlThick))  // 4

Re: Setting Borders in Excel
Ben_Sharples - Tue Dec 08 11:44:07 EST 2009

string oleMethod (OleAutoObj obj, string funcName, OleAutoArgs arguments, char& var)
string oleMethod (OleAutoObj obj, string funcName, OleAutoArgs arguments, bool& var)
string oleMethod (OleAutoObj obj, string funcName, OleAutoArgs arguments, int& var)
string oleMethod (OleAutoObj obj, string funcName, OleAutoArgs arguments, OleAutoObj& var)
string oleMethod (OleAutoObj obj, string funcName, OleAutoArgs arguments, string& var)

string oleMethod (OleAutoObj obj, string funcName, OleAutoArgs arguments)

string oleMethod (OleAutoObj obj, string funcName)

OleAutoArgs arguments = create() 
  olePut (arguments, 1) // xlContinuous
  olePut (arguments, 1) // xlThin
  olePut (arguments, -4105 ) // xlColorIndexAutomatic
  oleMethod (objRange, "BorderAround", arguments)
delete arguments