ExcelCOM_UDF

[aslani]

I just need a clarification of its useage. It states;

Syntax: _ExcelCellMerge($oExcel, $fDoMerge, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1)

But in the description it says;

; Parameter(s): $oExcel - An Excel object opened by a preceding call to _ExcelBookOpen() or _ExcelBookNew()

; $fWrapText - Perform word wrap on the cells in the range (True or False) (default=False)

; $sRangeOrRowStart - Either an A1 range, or an integer row number to start from if using R1C1

; $iColStart - The starting column for the number format(left) (default=1)

; $iRowEnd - The ending row for the number format (bottom) (default=1)

; $iColEnd - The ending column for the number format (right) (default=1)

My problem is, I get error on this line of the UDF;

$oExcel.Activesheet.Range($sRangeOrRowStart).MergeCells = $fDoMerge

When I look at the parameter descriptions again, its showing $fWrapText rather than $fDoMerge like it has in the Syntax.

So I'm stuck on how to make this work. What exactly goes in $fDoMerge?

EDIT: Btw, I'm trying to merge D9-G9

Thanks in advance.

Edited November 8, 2007 by aslani

[PsaltyDS]

I just need a clarification of its useage. It states;

But in the description it says;

My problem is, I get error on this line of the UDF;

$oExcel.Activesheet.Range($sRangeOrRowStart).MergeCells = $fDoMerge

When I look at the parameter descriptions again, its showing $fWrapText rather than $fDoMerge like it has in the Syntax.

So I'm stuck on how to make this work. What exactly goes in $fDoMerge?

EDIT: Btw, I'm trying to merge D9-G9

Thanks in advance.

Only Locodarwin could tell us what he was thinking, but I suspect there was at one time some confusion between the following:

.Range.Merge() method

Merge method as it applies to the Range object.

Creates a merged cell from the specified Range object.

expression.Merge(Across)

expression = Required. An expression that returns the Range object.

Across = Optional Variant. True to merge cells in each row of the specified range as separate merged cells. The default value is False.

Remarks

The value of a merged range is specified in the cell of the range's upper-left corner.

.Range.MergeCells property

MergeCells Property

True if the range or style contains merged cells. Read/write Variant.

Remarks

When you select a range that contains merged cells, the resulting selection may be different from the intended selection. Use the Address property to check the address of the selected range.

In the case of the method, $fWrapText may have represented the 'Across' option. But the function as we have it sets the MergeCells property instead. So, again subject to correction by Locodarwin or anyone else who actually know what they're talking about, I think $fWrapText is a legacy artifact and you should be setting $fDoMerge to True for merge, or False to un-merge the range.

Not tested. But I will if I get time on a box with MS Excel.

Edited November 9, 2007 by PsaltyDS

[aslani]

... you should be setting $fDoMerge to True for merge, or False to un-merge the range.

Oh wow thanks! That helped.

_ExcelCellMerge($oExcel, "True", "D9:G9")

That merged it.

[Locodarwin]

Sorry for entering this thread a little after-the-fact.

Actually Psalty is right. The _ExcelCellMerge() header was flubbed in the last uploaded version of the UDF. I've since fixed it, and of course in my ever-so-busy life I've not had time to finish and upload the changes.

The $fDoMerge flag is for creating or reversing a merge operation. To create a merge on the range you've specified, specify True (or a number greater than 0) for the $fDoMerge flag. But what if you want to "de-merge" cells? Specify the range to de-merge, and use False (or 0) for the $fDoMerge flag. The cells in the selected range will have any merge properties removed.

Here's the updated function:

expandcollapse popup

;===============================================================================
;
; Description:      Merge/UnMerge cell(s) in a range.
; Syntax:           _ExcelCellMerge($oExcel, $fDoMerge, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1)
; Parameter(s):     $oExcel - An Excel object opened by a preceding call to _ExcelBookOpen() or _ExcelBookNew()
;                   $fDoMerge - Flag, True performs a merge on the range, False reverses an existing merge in the range (True or False) (default=False)
;                   $sRangeOrRowStart - Either an A1 range, or an integer row number to start from if using R1C1
;                   $iColStart - The starting column for the number format(left) (default=1)
;                   $iRowEnd - The ending row for the number format (bottom) (default=1)
;                   $iColEnd - The ending column for the number format (right) (default=1)
; Requirement(s):   None
; Return Value(s):  On Success - Returns 1
;                   On Failure - Returns 0 and sets @error on errors:
;                       @error=1 - Specified object does not exist
;                       @error=2 - Starting row or column invalid
;                           @extended=0 - Starting row invalid
;                           @extended=1 - Starting column invalid
;                       @error=3 - Ending row or column invalid
;                           @extended=0 - Ending row invalid
;                           @extended=1 - Ending column invalid
;                       @error=4 - $fDoMerge is not set as True or False
; Author(s):        SEO <locodarwin at yahoo dot com>
; Note(s):          None
;
;===============================================================================
Func _ExcelCellMerge($oExcel, $fDoMerge, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1)
    If NOT IsObj($oExcel) Then Return SetError(1, 0, 0)
    If $fDoMerge <> False Or $fDoMerge <> True Then Return SetError(4, 0, 0)
    If NOT StringRegExp($sRangeOrRowStart, "[A-Z,a-z]", 0) Then
        If $sRangeOrRowStart < 1 Then Return SetError(2, 0, 0)
        If $iColStart < 1 Then Return SetError(2, 1, 0)
        If $iRowEnd < $sRangeOrRowStart Then Return SetError(3, 0, 0)
        If $iColEnd < $iColStart Then Return SetError(3, 1, 0)
        $oExcel.Activesheet.Range($oExcel.Cells($sRangeOrRowStart, $iColStart), $oExcel.Cells($iRowEnd, $iColEnd)).MergeCells = $fDoMerge
    Else
        $oExcel.Activesheet.Range($sRangeOrRowStart).MergeCells = $fDoMerge
    EndIf
    Return 1
EndFunc ;==>_ExcelCellMerge

As you can see, there is almost a method to the madness. In my work this is almost, but not quite always, the case.

Good luck with your Excel endeavors!

-S

Edited November 9, 2007 by Locodarwin

[aslani]

No problem Mr. 'darwin, your UDF helped me in so so many ways. Thank you.

[water]

You noticed that the last post is nearly 7 years old?

AutoIt and the Excel UDF have changed a lot since.

Please open a new thread describing your problen and, if needed, link to this thread.