Jump to content
Sign in to follow this  
aslani

ExcelCOM_UDF

Recommended Posts

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 by aslani
  • Like 1

[font="Georgia"]Chances are, I'm wrong.[/font]HotKey trouble?Stringregexp GuideAutoIT Current Version

Share this post


Link to post
Share on other sites
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 by PsaltyDS
  • Like 1

Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

Share this post


Link to post
Share on other sites
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. <_<

  • Like 1

[font="Georgia"]Chances are, I'm wrong.[/font]HotKey trouble?Stringregexp GuideAutoIT Current Version

Share this post


Link to post
Share on other sites
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:

;===============================================================================
;
; 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 by Locodarwin
  • Like 1

(Yet Another) ExcelCOM UDF"A human being should be able to change a diaper, plan an invasion, butcher a hog, conn a ship, design a building, write a sonnet, balance accounts, build a wall, set a bone, comfort the dying, take orders, give orders, cooperate, act alone, solve equations, analyze a new problem, pitch manure, program a computer, cook a tasty meal, fight efficiently, die gallantly...[indent]...specialization is for insects." - R. A. Heinlein[/indent]

Share this post


Link to post
Share on other sites
mawaissharif

Please Give a example please thanks in advance

 

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:
 

;===============================================================================
;
; 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

 

Share this post


Link to post
Share on other sites
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.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×