Jump to content
Sign in to follow this  
mawaissharif

How to merge cells in excel using autoit

Recommended Posts

water

Can you please post the script you use?

  • Like 1

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-12-03 - Version 1.4.11.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-10-31 - Version 1.3.4.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
mawaissharif
this funcation from Locodarwin
;===============================================================================
;
; 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

Share this post


Link to post
Share on other sites
water

I see. But how do you call this function?

  • Like 1

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-12-03 - Version 1.4.11.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-10-31 - Version 1.3.4.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
mawaissharif

Sorry i don't know the way but i call using this format

_ExcelCellMerge($Excelone, True, "R1C1", 2, 4, 4)
Edited by mawaissharif

Share this post


Link to post
Share on other sites
water

I think you need to use:

_ExcelCellMerge($oExcelOne, True, 1, 2, 4, 4)

This merges the range from row 1, column 2 to row 4 colum 4.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-12-03 - Version 1.4.11.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-10-31 - Version 1.3.4.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
mawaissharif

not working ?

r i am using correct way ?

  :ermm:

Local $Excelone = _Excel_Open()
    If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeWrite Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    Local $oWorkbook = _Excel_BookNew($Excelone)
    If @error Then
        MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeWrite Example", "Error creating the new workbook." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
        _Excel_Close($Excelone)
    EndIf
    _ExcelCellMerge($oWorkbook, True, 1, 2, 4, 4)
Edited by mawaissharif

Share this post


Link to post
Share on other sites
water

What is the error returned by _ExcelCellMerge - if any?

Local $Excelone = _Excel_Open()
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeWrite Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
Local $oWorkbook = _Excel_BookNew($Excelone)
If @error Then
    MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeWrite Example", "Error creating the new workbook." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    _Excel_Close($Excelone)
EndIf
_ExcelCellMerge($oWorkbook, True, 1, 2, 4, 4)
MsgBox($MB_SYSTEMMODAL, "", "_ExcelCellMerge: @error = " & @error)

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-12-03 - Version 1.4.11.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-10-31 - Version 1.3.4.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
water

Strange.

@error = 4 means that the second parameter isn't True or False.

But your code shows True.

The code you posted is the code you execute?

  • Like 1

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-12-03 - Version 1.4.11.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-10-31 - Version 1.3.4.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
mawaissharif

Yeah i am using 

this code

Local $Excelone = _Excel_Open()
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeWrite Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
Local $oWorkbook = _Excel_BookNew($Excelone)
If @error Then
    MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeWrite Example", "Error creating the new workbook." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    _Excel_Close($Excelone)
EndIf
_ExcelCellMerge($oWorkbook, True, 1, 2, 4, 4)
MsgBox($MB_SYSTEMMODAL, "", "_ExcelCellMerge: @error = " & @error)

and i am using ms excel 2013

Share this post


Link to post
Share on other sites
water

There is nothing wrong with your code.

It's a bug in the function.

Change line

If $fDoMerge <> False Or $fDoMerge <> True Then Return SetError(4, 0, 0)

to

If $fDoMerge <> False And $fDoMerge <> True Then Return SetError(4, 0, 0)

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-12-03 - Version 1.4.11.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-10-31 - Version 1.3.4.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
mawaissharif

Now i get 

ExcelCellMerge: @error = 0

using

Local $Excelone = _Excel_Open()
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeWrite Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
Local $oWorkbook = _Excel_BookNew($Excelone)
If @error Then
    MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeWrite Example", "Error creating the new workbook." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    _Excel_Close($Excelone)
EndIf
_ExcelCellMerge($oWorkbook, True, 1, 2, 4, 4)
MsgBox($MB_SYSTEMMODAL, "", "_ExcelCellMerge: @error = " & @error)






Func _ExcelCellMerge($oExcel, $fDoMerge, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1)
    If Not IsObj($oExcel) Then Return SetError(1, 0, 0)
     If $fDoMerge <> False And $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

:x

Edited by mawaissharif

Share this post


Link to post
Share on other sites
water

@error = 0 is the expected result for the function.

Do you get the expected merged cells?

  • Like 1

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-12-03 - Version 1.4.11.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-10-31 - Version 1.3.4.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
mawaissharif

Untitled.jpg

Edited by mawaissharif

Share this post


Link to post
Share on other sites
water

Can you please attach the picture directly to your thread? The link to dropbox doesn't work here.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-12-03 - Version 1.4.11.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-10-31 - Version 1.3.4.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
mawaissharif

It works with these paramenters

_ExcelCellMerge($oWorkbook, True, 1, 1, 4, 4)

Same 2nd and 3rd

ie

1,1

thank you :-)

Share this post


Link to post
Share on other sites
water
:)
  • Like 1

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-12-03 - Version 1.4.11.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-10-31 - Version 1.3.4.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
mawaissharif

i was confused

i am using parameter in wrong way !!  o:)

_ExcelCellMerge needs Excel application object

but i was giving workbook object.  :shifty:

This work perfectly

#include <Array.au3>
#include <Excel.au3>


Local $Excelone = _Excel_Open()
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeWrite Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
Local $oWorkbook = _Excel_BookNew($Excelone)
If @error Then
    MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeWrite Example", "Error creating the new workbook." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    _Excel_Close($Excelone)
EndIf
Local $RowStartAA = 2
Local $Columstart = 4
Local $rowend = 8
Local $columend = 12
_ExcelCellMerge($Excelone, True, $RowStartAA, $Columstart, $rowend, $columend)


Func _ExcelCellMerge($oExcel, $fDoMerge, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1)
    If Not IsObj($oExcel) Then Return SetError(1, 0, 0)
    If $fDoMerge <> False And $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

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  

×