gogomarkni Posted May 25, 2015 Share Posted May 25, 2015 (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 May 25, 2015 by gogomarkni Link to comment Share on other sites More sharing options...
water Posted May 25, 2015 Share Posted May 25, 2015 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 - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
gogomarkni Posted May 25, 2015 Author Share Posted May 25, 2015 OpenBook change to example.xlsx,I hope the result is showed :E1,D2,E3,E4,E5,D6,E7,E8,E9,E10 Link to comment Share on other sites More sharing options...
water Posted May 25, 2015 Share Posted May 25, 2015 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 - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
l3ill Posted May 25, 2015 Share Posted May 25, 2015 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") My Contributions... SnippetBrowser NewSciTE PathFinder Text File Manipulation FTP Connection Tester / INI File - Read, Write, Save & Load Example Link to comment Share on other sites More sharing options...
gogomarkni Posted May 25, 2015 Author Share Posted May 25, 2015 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? Link to comment Share on other sites More sharing options...
l3ill Posted May 25, 2015 Share Posted May 25, 2015 (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 May 25, 2015 by l3ill udated example My Contributions... SnippetBrowser NewSciTE PathFinder Text File Manipulation FTP Connection Tester / INI File - Read, Write, Save & Load Example Link to comment Share on other sites More sharing options...
water Posted May 25, 2015 Share Posted May 25, 2015 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 - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
gogomarkni Posted May 25, 2015 Author Share Posted May 25, 2015 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 Link to comment Share on other sites More sharing options...
water Posted May 25, 2015 Share Posted May 25, 2015 #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 - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
water Posted May 25, 2015 Share Posted May 25, 2015 (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 SubTaken from https://stackoverflow.com/questions/2951070/vba-excel-get-start-range-and-end-range-of-a-vertically-merged-cell Edited May 25, 2015 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 - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
gogomarkni Posted May 26, 2015 Author Share Posted May 26, 2015 hi water,$aResult[$i - 1] = .Range("E" & $i).MergeCells.Cells(1, 1).Value ; Read the leftmost cell of the merged areathe 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 More sharing options...
water Posted May 26, 2015 Share Posted May 26, 2015 (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 May 26, 2015 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 - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
gogomarkni Posted May 26, 2015 Author Share Posted May 26, 2015 yes, I also work for me , thanks. Link to comment Share on other sites More sharing options...
water Posted May 26, 2015 Share Posted May 26, 2015 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 - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now