Jump to content

get excel data from merged cells


Recommended Posts

hi ,

I want to read values of range, using _Excel_RangeRead, but some cells are merged (like example).

how can I quickly read the data  from E1:E10,

I hope the value $aresult is [E1,D2,E3,E4,E5,D6,E7,E8,E9,E10]

not [E1,Null,E3,E4,E5,Null,E7,E8,E9,E10]

how is quickly method to read the merged cells?

thanks,

 

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


; Create application object and open an example workbook
Local $oAppl = _Excel_Open()
If @error Then Exit MsgBox( 0, "","Error creating the Excel application object.")
Local $oWorkbook = _Excel_BookOpen($oAppl, @ScriptDir & "\Simple_example.xlsx")
If @error Then
    MsgBox(0, "", "Error opening workbook")
    _Excel_Close($oAppl)
    Exit
EndIf

Local $aResult = _Excel_RangeRead($oWorkbook, 1, "E1:E10", 1)

_ArrayDisplay($aResult)

 

 

example.xlsx

Edited by gogomarkni
Link to comment
Share on other sites

What do you get when you run your example code?

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

My understanding is that the value of a merged cell is only returned when you read the leftmost cell of the merged area.
So to get the value displayed in "E2" you have to read cell "D2".

 

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

Not sure what the end game is here but here is a workaround that might help:

Un Merges the cells, which is how the function reads the cells in the first place, but then takes the Cell with a value and copies it to the others.

$oWorkBook.ActiveSheet.Range("(D2:F2").Select.MergeArea.UnMerge
_Excel_RangeCopyPaste($oWorkbook.ActiveSheet, "D2", "E2:F2")

 

Link to comment
Share on other sites

if that is the only prerequisite then my workaround should do the trick.

Edit:

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


; Create application object and open an example workbook
Local $oAppl = _Excel_Open()
If @error Then Exit MsgBox( 0, "","Error creating the Excel application object.")
Local $oWorkbook = _Excel_BookOpen($oAppl, @ScriptDir & "\example.xlsx")
If @error Then
    MsgBox(0, "", "Error opening workbook")
    _Excel_Close($oAppl)
    Exit
EndIf
$oWorkBook.ActiveSheet.Range("(C1:J10)").UnMerge
_Excel_RangeCopyPaste($oWorkbook.ActiveSheet, "D2", "E2:F2")
_Excel_RangeCopyPaste($oWorkbook.ActiveSheet, "D6", "E6:F6")

Local $aResult = _Excel_RangeRead($oWorkbook, 1, "E1:E10", 1)

_ArrayDisplay($aResult)

 

Edited by l3ill
udated example
Link to comment
Share on other sites

Or read the columns D:E to an array. If a cell in column 2 of the array is empty then copy cell 1 of the respective row to column 2.
Then you have all values in column 2 for further processing.

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

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

; Create application object and open an example workbook
Local $oAppl = _Excel_Open()
If @error Then Exit MsgBox( 0, "","Error creating the Excel application object.")
Local $oWorkbook = _Excel_BookOpen($oAppl, @ScriptDir & "\example.xlsx")
If @error Then
    MsgBox(0, "", "Error opening workbook")
    _Excel_Close($oAppl)
    Exit
EndIf

Local $aResult = _Excel_RangeRead($oWorkbook, 1, "E1:E10") ; Read cells E1 to E10
With $oWorkBook.ActiveSheet
    For $i = 1 to 10 
       If .Range("E" & $i).MergeCells Then ; Is cell member of a merged area
           $aResult[$i - 1] = .Range("E" & $i).MergeCells.Cells(1, 1).Value ; Read the leftmost cell of the merged area
       EndIf
    Next
EndWith

_ArrayDisplay($aResult)

Untested.

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

Another - and maybe faster - method I found on the web is to unmerge all cells in the workbook and copy the value to all cells of the mergearea.
Then read the data and drop all changes to the workbook.
VB example that needs to be translated to Autoit:

Sub BirlesenHucreleriAyirDegerleriGeriYaz()
    Dim Hucre As Range
    Dim Aralik
    Dim icerik
    Dim mySheet As Worksheet

    For Each mySheet In Worksheets

    mySheet.Activate
    MsgBox mySheet.Name & “ yapılacak…”

    For Each Hucre In mySheet.UsedRange
        If Hucre.MergeCells Then
           Hucre.Orientation = xlHorizontal
           Aralik = Hucre.MergeArea.Address
           icerik = Hucre
           Hucre.MergeCells = False
           Range(Aralik) = icerik
        End If
    Next

 MsgBox mySheet.Name & " Bitti!!"

 Next mySheet
End Sub

Taken from https://stackoverflow.com/questions/2951070/vba-excel-get-start-range-and-end-range-of-a-vertically-merged-cell

Edited by water

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

hi water,

$aResult[$i - 1] = .Range("E" & $i).MergeCells.Cells(1, 1).Value ; Read the leftmost cell of the merged area

the result is also empty in E2 and E6,

read the leftmost cell of the merged area seems not work .

 

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

; Create application object and open an example workbook
Local $oAppl = _Excel_Open()
If @error Then Exit MsgBox( 0, "","Error creating the Excel application object.")
Local $oWorkbook = _Excel_BookOpen($oAppl, @ScriptDir & "\example.xlsx")
If @error Then
    MsgBox(0, "", "Error opening workbook")
    _Excel_Close($oAppl)
    Exit
EndIf

Local $aResult = _Excel_RangeRead($oWorkbook, 1, "E1:E10") ; Read cells E1 to E10
With $oWorkBook.ActiveSheet
    For $i = 1 to 10
       If .Range("E" & $i).MergeCells Then ; Is cell member of a merged area
           $aResult[$i - 1] = .Range("E" & $i).MergeCells.Cells(1, 1).Value ; Read the leftmost cell of the merged area
           msgbox(0,"$aResult","$i="&$i&"----"&"$aResult[$i - 1]= "&$aResult[$i - 1])
       EndIf
    Next
EndWith

_ArrayDisplay($aResult)

 

 

example.xlsx

Link to comment
Share on other sites

Sorry, was an error on my side. This works:

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

; Create application object and open an example workbook
Local $oAppl = _Excel_Open()
If @error Then Exit MsgBox( 0, "","Error creating the Excel application object.")
Local $oWorkbook = _Excel_BookOpen($oAppl, @ScriptDir & "\example.xlsx")
If @error Then
    MsgBox(0, "", "Error opening workbook")
    _Excel_Close($oAppl)
    Exit
EndIf
Local $aResult = _Excel_RangeRead($oWorkbook, 1, "E1:E10") ; Read cells E1 to E10
With $oWorkBook.ActiveSheet
    For $i = 1 to 10
       If .Range("E" & $i).MergeCells Then ; Is cell member of a merged area
           $aResult[$i - 1] = .Range("E" & $i).MergeArea.Cells(1, 1).Value ; Read the leftmost cell of the merged area
           msgbox(0,"$aResult","$i="&$i&"----"&"$aResult[$i - 1]= "&$aResult[$i - 1])
       EndIf
    Next
EndWith
_ArrayDisplay($aResult)

 

Edited by water

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

Glad the problem could be solved :)

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
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
 Share

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...