Jump to content

Search the Community

Showing results for tags 'IsCellMerged'.

  • Search By Tags

    Type tags separated by commas.
  • Search By Author

Content Type


Forums

  • General
    • Announcements and Site News
    • Administration
  • AutoIt v3
    • AutoIt Help and Support
    • AutoIt Technical Discussion
    • AutoIt Example Scripts
  • Scripting and Development
    • Developer General Discussion
    • Language Specific Discussion
  • IT Administration
    • Operating System Deployment
    • Windows Client
    • Windows Server
    • Office

Categories

  • AutoIt Team
    • Beta
    • MVP
  • AutoIt
    • Automation
    • Databases and web connections
    • Data compression
    • Encryption and hash
    • Games
    • GUI Additions
    • Hardware
    • Information gathering
    • Internet protocol suite
    • Maths
    • Media
    • PDF
    • Security
    • Social Media and other Website API
    • Windows
  • Scripting and Development
  • IT Administration
    • Operating System Deployment
    • Windows Client
    • Windows Server
    • Office

Find results in...

Find results that contain...


Date Created

  • Start

    End


Last Updated

  • Start

    End


Filter by number of...

Joined

  • Start

    End


Group


Member Title


Location


WWW


Interests

Found 1 result

  1. Context: I have an autoit-Excel application that basically creates a complex Gantt Chart of scheduled versus actual data (movements). The cells having a fixed width of 20 pixels, one column representing 15 minutes, over 24 hours the page requires about 100 columns including titles.One vertical line is one hour. Each bock of data contains a descriptive text that is written in the first cell of the block. Blocks can span several hours. Once completed, the whole thing is saved to Web in html format - company Intranet. While the Excel format allows writing outside the boundaries of a cell, the text remains visible, once saved to html format, the cell content is cut off at the end of the single table cell as below example, above is merged and below not So I solve the issue by merging the cell blocks. This is all working alright except that at certain times (the Gantt is very complex) the application runs into a merged cell conflict resulting in an annoying but unavoidable (!?) Excel popup requiring user interaction. The only action is to accept the conflict by pressing OK. I know that for this reason it is not recommended to merge cells in scripted Excels (or in macros) but I don't have any other option. Workarounds: I solved the issue with a time-consuming workaround. Using _Excel_IsCellMerged I verify cell by cell if any of the cells to be merged are already part of a merged area. I noticed that checking for a full range of cells that contain for example one cell that is part of another merged area returns False (anyone can confirm that I am right?), which must be a bug in the function. But with very large data sheet, the systematic check takes valuable time. ; #FUNCTION# ==================================================================================================== ; Function: _Excel_IsCellMerged ; Description: Merge/UnMerge cell(s) in a range. ; Syntax: _Excel_IsCellMerged($oExcel, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1, $CellByCell = False) ; Parameter(s): $oExcel - An Excel object opened by a preceding call to _ExcelBookOpen() or _ExcelBookNew() ; $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) ; $CellByCell - Verify cell by cell if any cell is part of a merged area, only possible with R1C1 ranges, default = False ; 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 - $CellByCell onbly allowed with R1C1 range ; Author(s): GreenCan ; Note(s): $CellByCell can be slow; especially if checking a large range of cells ; ; #FUNCTION# ==================================================================================================== Func _Excel_IsCellMerged($oExcel, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1, $CellByCell = False) If Not IsObj($oExcel) Then Return SetError(1, 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) If $CellByCell Then For $i = $sRangeOrRowStart To $iRowEnd For $ii = $iColStart To $iColEnd If $oExcel.Activesheet.Range($oExcel.Cells($i, $ii), $oExcel.Cells($i, $ii)).MergeCells Then If Not @Compiled Then ConsoleWrite ("@@ Debug(" & @ScriptLineNumber & ") : ExcelCOM3_UDF " & _Excel_ColumnNumberToLetter($ii) & $i & " merge True" & @CR) Return True EndIf Next Next Return False Else If $oExcel.Activesheet.Range($oExcel.Cells($sRangeOrRowStart, $iColStart), $oExcel.Cells($iRowEnd, $iColEnd)).MergeCells Then Return True Else Return False EndIf EndIf Else If $CellByCell Then Return SetError(4, 1, 0) If $oExcel.Activesheet.Range($sRangeOrRowStart).MergeCells Then Return True Else Return False EndIf EndIf Return 1 EndFunc ;==>_Excel_IsCellMerged Another method that I tried without much satisfaction is to start a parallel process waiting for the popup to appear and sending an {ENTER} key working around the issue. But this is an esthetic nightmare Global $sWinTitle = IniReadSection($inifile,"Microsoft Excel") Global $sWinText = IniReadSection($inifile,"The selection contains multiple data values") Global $hWnd, $iPID While 1 ; watch the popup $hWnd = WinGetHandle($sWinTitle, $sWinText) If $hWnd <> "" Then $iPID = WinGetProcess($sWinTitle) ProcessSetPriority($iPID, 0) WinActivate($sWinTitle, $sWinText) Send("{ENTER}") EndIf Sleep(3000) WEnd I searched around MSDN and other sites but I could not find a way to get rid of the popup, by setting the response 'OK' by default. Cry for Help: Long explanation for a short question: Did anyone had the same issue and solved it differently? Thanks GreenCan
×
×
  • Create New...