gogomarkni

get excel data from merged cells

15 posts in this topic

#1 ·  Posted (edited)

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

Share this post


Link to post
Share on other sites



What do you get when you run your example code?


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - 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

OpenBook change to example.xlsx,

I hope the result is showed :

E1,D2,E3,E4,E5,D6,E7,E8,E9,E10

 

result.PNG

Share this post


Link to post
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 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - 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

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")

 

Share this post


Link to post
Share on other sites

thanks,

I explain more ,

my code want to auto read  E1:E10 (example),

when reading in merged cells , the value maybe is null, 

how to quickly get the presented data from leftmost cell of the merged area?

 

Share this post


Link to post
Share on other sites

#7 ·  Posted (edited)

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

Share this post


Link to post
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 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - 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

The attached file is just a small example ,real file is from user, I cannot know where will be merged cells, another words, when read a empty cell, must read leftmost cell of merged cells area

Share this post


Link to post
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 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - 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

#11 ·  Posted (edited)

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 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - 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

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

Share this post


Link to post
Share on other sites

#13 ·  Posted (edited)

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 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - 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

yes, I also work for me , thanks.

 

Share this post


Link to post
Share on other sites

Glad the problem could be solved :)


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - 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