JohnyX Posted May 21, 2018 Author Share Posted May 21, 2018 1 hour ago, water said: Thanks for the feedback! I've already added this to the Excel wiki Now I ran into a new challenge , can you please point me in the right direction? I am trying to replace more pictures in excel as we discussed here: I am trying to use the old image positioning and ratio for the new image, to do that, I search the workbook for a specific string and I get the cell where the string is located. (Because the old image should partially cover that cell where the string is located.) Then I use $oShapes and loop through all shapes in the worksheet to find if the shape's upper left corner covers the cell where the string is located, using: $oShape.TopLeftCell.Address = "$A$1" The problem is that the string is in a merged area of cells like A1:A7 and _Excel_RangeFind(string) returns $A$1 because the cells are merged. But the old image's upper left corner covers $A$5 area of the merged cells, so I can't find the correct location of the image to use $oShape.TopLeftCell.Address = "Cell". Any ideea what can I do? Can I somehow detect and retrieve the merged area? Or can I use $oShape.TopLeftCell.Address with a range instead of a cell? Or maybe loop through all columns until I find the one with the image? If you don't understand what I mean I will came back with a script and a sample file. Thank. Link to comment Share on other sites More sharing options...
water Posted May 21, 2018 Share Posted May 21, 2018 (edited) The topleft cell of the merged area like A1:A7 is correctly returned as A1. If the text seems to cover A5 in this merged area then it is caused by formating the merged area as "centered". Unfortunately I do not know of a way to determine the exact position of a centered text in a merged area. Sorry. BTW: A screenshot would be fine Edited May 21, 2018 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...
water Posted May 21, 2018 Share Posted May 21, 2018 Another idea: To check if the returned cell is in a merged area use properties MergeCells https://msdn.microsoft.com/en-us/vba/excel-vba/articles/range-mergecells-property-excel and MergeArea https://msdn.microsoft.com/en-us/vba/excel-vba/articles/range-mergearea-property-excel 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...
JohnyX Posted May 21, 2018 Author Share Posted May 21, 2018 (edited) Thank you. The problem was that i couldn't get the correct cell/position of an image by searching for a text in that cell, because the text is in a merged area(see attached screenshoot). The search returned A1 but the image's upper left corner is in E1. Still i managed to get the correct position by looping through all columns, once i found the Row number. Local $aResult1 = _Excel_RangeFind($oWorkbook, "Text to search for.") Local $var1 = $aResult1[0][2] Local $tmp1 = "A,B,C,D,E,F,G,H" Local $Letters1 = StringSplit($tmp1, ",") For $i = 1 To $Letters1[0] For $oShape In $oWorkbook.ActiveSheet.Shapes $img1find = StringReplace($var1, StringMid($var1, 2, 1), $Letters1[$i]) If $oShape.TopLeftCell.Address = $img1find Then $iTop = $oShape.Top $iLeft = $oShape.Left $iWidth = $oShape.Width $iHeight = $oShape.Height $oShape.Delete $oExcel.Worksheets(1).Shapes.AddPicture(@DesktopDir & "\newpic.jpg", False, True, $iLeft, $iTop, $iWidth, $iHeight) ExitLoop EndIf ExitLoop Next Next Thanks for all your help again, the script is fully functional now. Edited May 21, 2018 by JohnyX Link to comment Share on other sites More sharing options...
water Posted May 21, 2018 Share Posted May 21, 2018 Great 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