Stanley Lim Posted December 3, 2006 Posted December 3, 2006 Hi Pple, Here I come again to report another bug(I think)... :"> Please kindly ignore me if this has already been reported or it is intended to be implemented this way. For the function, _ExcelSheetUsedRangeGet(), I notice that the last element of the return array is always zero. Example, when I do a _ArrayDisplay() I'll get the following: [0] = D20461 [1] = R20461C4 [3] = 4 [4] = 0 Orginal Code: expandcollapse popup;=============================================================================== ; ; Description: Return the last cell of the used range in the specified worksheet. ; Syntax: $array = _ExcelSheetUsedRangeGet($oExcel, $vSheet) ; Parameter(s): $oExcel - An Excel object opened by a preceding call to _ExcelBookOpen() or _ExcelBookNew() ; $vSheet - The sheet name or number to be checked. ; Requirement(s): None ; Return Value(s): On Success - Returns an array of used range values: ; $array[0] - The last cell used, in A1 format (if 0 is returned, worksheet is blank) ; $array[1] - The last cell used, in R1C1 format ; $array[2] - The last column used, as an integer ; $array[3] - The last row used, as an integer ; On Failure - Returns 0 (as non-array numeric value) and sets @error on errors: ; @error=1 - Specified object does not exist ; @error=2 - Invalid sheet number ; @error=3 - Invalid sheet name ; Author(s): DaLiMan, SEO <locodarwin at yahoo dot com> ; Note(s): Upon return, $array[0] will equal numeric value 0 if the worksheet is blank ; ;=============================================================================== Func _ExcelSheetUsedRangeGet($oExcel, $vSheet) Local $aSendBack[4], $sTemp, $aSheetList, $fFound = 0 If NOT IsObj($oExcel) Then Return SetError(1, 0, 0) If IsNumber($vSheet) Then If $oExcel.ActiveWorkbook.Sheets.Count < $vSheet Then Return SetError(2, 0, 0) Else $aSheetList = _ExcelSheetList($oExcel) For $xx = 1 To $aSheetList[0] If $aSheetList[$xx] = $vSheet Then $fFound = 1 Next If NOT $fFound Then Return SetError(3, 0, 0) EndIf $oExcel.ActiveWorkbook.Sheets($vSheet).Select $aSendBack[0] = $oExcel.Application.Selection.SpecialCells($xlCellTypeLastCell).Address $aSendBack[1] = $oExcel.Application.Selection.SpecialCells($xlCellTypeLastCell).Address(True, True, $xlR1C1) $aSendBack[0] = StringReplace($aSendBack[0], "$", "") $sTemp = StringSplit($aSendBack[1], "C") $aSendBack[2] = Number($sTemp[2]) $aSendBack[3] = Number(StringRegExpReplace($aSendBack[0], "\a", "")) If $aSendBack[0] = "A1" And $oExcel.Activesheet.Range($aSendBack[0]).Value = "" Then $aSendBack[0] = 0 Return $aSendBack EndFunc ;==>_ExcelSheetUsedRangeGet oÝ÷ Ø*+ç-y×(u殶s` £³ÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓУ°£²FW67&Föã¢&WGW&âFRÆ7B6VÆÂöbFRW6VB&ævRâFR7V6fVBv÷&·6VWBࣲ7çF¢b33c¶'&ÒôW6VÅ6VWEW6VE&ævTvWBb33c¶ôW6VÂÂb33c·e6VWB£²&ÖWFW"2¢b33c¶ôW6VÂÒâW6VÂö&¦V7B÷VæVB'&V6VFær6ÆÂFòôW6VÄ&öö´÷Vâ÷"ôW6VÄ&öö´æWr£²b33c·e6VWBÒFR6VWBæÖR÷"çVÖ&W"Fò&R6V6¶VBࣲ&WV&VÖVçB2¢æöæP£²&WGW&âfÇVR2¢öâ7V66W72Ò&WGW&ç2â'&öbW6VB&ævRfÇVW3 £°b33c¶'&³ÒÒFRÆ7B6VÆÂW6VBÂâf÷&ÖBb2&WGW&æVBÂv÷&·6VWB2&Ææ²£°b33c¶'&³ÒÒFRÆ7B6VÆÂW6VBÂâ#3f÷&Ö@£°b33c¶'&³%ÒÒFRÆ7B6öÇVÖâW6VBÂ2âçFVvW £°b33c¶'&³5ÒÒFRÆ7B&÷rW6VBÂ2âçFVvW £²öâfÇW&RÒ&WGW&ç22æöâÖ'&çVÖW&2fÇVRæB6WG2W'&÷"öâW'&÷'3 £²W'&÷#ÓÒ7V6fVBö&¦V7BFöW2æ÷BW7@£²W'&÷#Ó"ÒçfÆB6VWBçVÖ&W £²W'&÷#Ó2ÒçfÆB6VWBæÖP£²WF÷"2¢FÆÖâÂ4TòfÇC¶Æö6öF'vâBöòF÷B6öÒfwC°£²æ÷FR2¢Wöâ&WGW&âÂb33c¶'&³ÒvÆÂWVÂçVÖW&2fÇVRbFRv÷&·6VWB2&Ææ°£°£³ÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓÓФgVæ2ôW6VÅ6VWEW6VE&ævTvWBb33c¶ôW6VÂÂb33c·e6VWB¢Æö6Âb33c¶6VæD&6µ³EÒÂb33c·5FV×Âb33c¶6VWDÆ7BÂb33c¶df÷VæBÒ¢bäõB4ö&¢b33c¶ôW6VÂFVâ&WGW&â6WDW'&÷" b4çVÖ&W"b33c·e6VWBFVà bb33c¶ôW6VÂä7FfUv÷&¶&öö²å6VWG2ä6÷VçBfÇC²b33c·e6VWBFVâ&WGW&â6WDW'&÷"" VÇ6P b33c¶6VWDÆ7BÒôW6VÅ6VWDÆ7Bb33c¶ôW6V f÷"b33c·ÒFòb33c¶6VWDÆ7E³Ð bb33c¶6VWDÆ7E²b33c·ÒÒb33c·e6VWBFVâb33c¶df÷VæBÒ æW@ bäõBb33c¶df÷VæBFVâ&WGW&â6WDW'&÷"2 VæD` b33c¶ôW6VÂä7FfUv÷&¶&öö²å6VWG2b33c·e6VWBå6VÆV7@ b33c¶6VæD&6µ³ÒÒb33c¶ôW6VÂäÆ6Föâå6VÆV7Föâå7V6Ä6VÆÇ2b33c·Ä6VÆÅGTÆ7D6VÆÂäFG&W70¢b33c¶6VæD&6µ³ÒÒb33c¶ôW6VÂäÆ6Föâå6VÆV7Föâå7V6Ä6VÆÇ2b33c·Ä6VÆÅGTÆ7D6VÆÂäFG&W72G'VRÂG'VRÂb33c·Å#3 b33c¶6VæD&6µ³ÒÒ7G&æu&WÆ6Rb33c¶6VæD&6µ³ÒÂgV÷C²b33c²gV÷C²ÂgV÷C²gV÷C² b33c·5FV×Ò7G&æu7ÆBb33c¶6VæD&6µ³ÒÂgV÷C´2gV÷C² b33c¶6VæD&6µ³%ÒÒçVÖ&W"b33c·5FV׳%Ò b33c¶6VæD&6µ³5ÒÒçVÖ&W"7G&æu&VtW&WÆ6Rb33c¶6VæD&6µ³ÒÂgV÷CµµãÓÒgV÷C²ÂgV÷C²gV÷C² bb33c¶6VæD&6µ³ÒÒgV÷C´gV÷C²æBb33c¶ôW6VÂä7FfW6VWBå&ævRb33c¶6VæD&6µ³ÒåfÇVRÒgV÷C²gV÷C²FVâb33c¶6VæD&6µ³ÒÒ¢&WGW&âb33c¶6VæD&6°¤VæDgVæ0³ÓÒfwCµôW6VÅ6VWEW6VE&ævTvW@ oÝ÷ Ù8^)Þ!ÈZjëh×6$aSendBack[3] = Number(StringRegExpReplace($aSendBack[0], "[^0-9]", "")) Hope this helps. Thanks again for his excellent UDF! I simply love it!
Locodarwin Posted December 3, 2006 Author Posted December 3, 2006 Actually, Stanley, the uncorrected code is works fine if you don't use Beta. However, your change works fine for both Beta and non-Beta, as far as I can tell, so we'll go with that. Thanks a ton! -S (Yet Another) ExcelCOM UDF"A human being should be able to change a diaper, plan an invasion, butcher a hog, conn a ship, design a building, write a sonnet, balance accounts, build a wall, set a bone, comfort the dying, take orders, give orders, cooperate, act alone, solve equations, analyze a new problem, pitch manure, program a computer, cook a tasty meal, fight efficiently, die gallantly...[indent]...specialization is for insects." - R. A. Heinlein[/indent]
Locodarwin Posted January 5, 2007 Author Posted January 5, 2007 Update: added 2 "basic usage" example scripts to first post, as attachments. -S (Yet Another) ExcelCOM UDF"A human being should be able to change a diaper, plan an invasion, butcher a hog, conn a ship, design a building, write a sonnet, balance accounts, build a wall, set a bone, comfort the dying, take orders, give orders, cooperate, act alone, solve equations, analyze a new problem, pitch manure, program a computer, cook a tasty meal, fight efficiently, die gallantly...[indent]...specialization is for insects." - R. A. Heinlein[/indent]
fisofo Posted January 6, 2007 Posted January 6, 2007 I left this topic for awhile, and now i'm back, and i just want to say it looks awesome. Rockin UDF!
nitekram Posted January 10, 2007 Posted January 10, 2007 (edited) Can you look at this post - I guess I should have posted here to start with . Should I just post the code here?Update - when I use the $formula2 it puts some quotes into the value so =SUM(B1:B16) becomes =SUM('B1:B16') and the formula does not workbut I have been able to get my $formula to work but by calling _ExcelWriteCell() instead of _ExcelWriteFormula() -EDIT 11:04 - the quotes for $formula2 might not be right - memory is fading and I am not at work to check - no I have no copy of office at home, installed anyway Edited January 10, 2007 by nitekram 2¢ All by me:"Sometimes you have to go back to where you started, to get to where you want to go." "Everybody catches up with everyone, eventually" "As you teach others, you are really teaching yourself." From my dad "Do not worry about yesterday, as the only thing that you can control is tomorrow." WIKI | Tabs; | Arrays; | Strings | Wiki Arrays | How to ask a Question | Forum Search | FAQ | Tutorials | Original FAQ | ONLINE HELP | UDF's Wiki | AutoIt PDF AutoIt Snippets | Multple Guis | Interrupting a running function | Another Send StringRegExp | StringRegExp Help | RegEXTester | REG TUTOR | Reg TUTOT 2 AutoItSetOption | Macros | AutoIt Snippets | Wrapper | Autoit Docs SCITE | SciteJump | BB | MyTopics | Programming | UDFs | AutoIt 123 | UDFs Form | UDF Learning to script | Tutorials | Documentation | IE.AU3 | Games? | FreeSoftware | Path_Online | Core Language Programming Tips Excel Changes ControlHover.UDF GDI_Plus Draw_On_Screen GDI Basics GDI_More_Basics GDI Rotate GDI Graph GDI CheckExistingItems GDI Trajectory Replace $ghGDIPDll with $__g_hGDIPDll DLL 101? Array via Object GDI Swimlane GDI Plus French 101 Site GDI Examples UEZ GDI Basic Clock GDI Detection Ternary operator
Locodarwin Posted February 12, 2007 Author Posted February 12, 2007 Updated original post. v1.32 attached.Fix: _ExcelSheetUsedRangeGet() used a regular expression that no longer worked as intended due to changes in AutoIt. Thanks to Stanley Lim for the find & fix. (Yet Another) ExcelCOM UDF"A human being should be able to change a diaper, plan an invasion, butcher a hog, conn a ship, design a building, write a sonnet, balance accounts, build a wall, set a bone, comfort the dying, take orders, give orders, cooperate, act alone, solve equations, analyze a new problem, pitch manure, program a computer, cook a tasty meal, fight efficiently, die gallantly...[indent]...specialization is for insects." - R. A. Heinlein[/indent]
BigDaddyO Posted February 12, 2007 Posted February 12, 2007 Hello, I am using your UDF to put together a complex report and I came across a small typo in the UDF. Line 2166 for the Merge Cells has "$fWrapText - Perform word wrap on the cells in the range (True or False) (default=False)" I am assuming the function heading was copied from somewhere else and didn't get updated. If I find anything else, I'll update this post. Mike
BigDaddyO Posted February 13, 2007 Posted February 13, 2007 (edited) I needed to put Borders into my report because Management likes things to look fancy. I noticed that the borders were remed out of the UDF so I wrote my own function for it that you can include in the UDF if you would like. EDIT: Updated to work with Older Office Versions expandcollapse popup;=============================================================================== ; ; Description: Create Borders around a range of cells ; Syntax: _ExcelCreateBorders($oExcel, $sBorderStyle, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1, $iEdgeLeft = 1, $iEdgeTop = 1, $iEdgeBottom = 1, $iEdgeRight = 1, $iEdgeInsideV = 0, $iEdgeInsideH = 0) ; Parameter(s): $oExcel - An Excel object opened by a preceding call to _ExcelBookOpen() or _ExcelBookNew() ; $sBorderStyle - The type of border to use, $xlThick, $xlThin, $xlDouble ; $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) ; $iEdgeLeft - Specify if the left edge of the selected cells should have a border (default=1) Yes ; $iEdgeTop - Specify if the Top edge of the selected cells should have a border (default=1) Yes ; $iEdgeBottom - Specify if the Bottom edge of the selected cells should have a border (default=1) Yes ; $iEdgeRight - Specify if the Right edge of the selected cells should have a border (default=1) Yes ; $iEdgeInsideV - Specify if the Inside Verticle edges of the selected cells should have a border (default=0) No ; $iEdgeInsideH - Specify if the Inside Horizontal edges of the selected cells should have a border (default=0) No ; 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 ; Author(s): MikeOsdx <Using Generic Excel functions from locodarwin> ; Note(s): None ; ;=============================================================================== Func _ExcelCreateBorders($oExcel, $sBorderStyle, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1, $iEdgeLeft = 1, $iEdgeTop = 1, $iEdgeBottom = 1, $iEdgeRight = 1, $iEdgeInsideV = 0, $iEdgeInsideH = 0) 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 $iEdgeLeft = 1 Then With $oExcel.Activesheet.Range($oExcel.Cells($sRangeOrRowStart, $iColStart), $oExcel.Cells($iRowEnd, $iColEnd)).Borders($xlEdgeLeft) .LineStyle = $xlContinuous .ColorIndex = $xlAutomatic .Weight = $sBorderStyle EndWith EndIf if $iEdgeTop = 1 Then With $oExcel.Activesheet.Range($oExcel.Cells($sRangeOrRowStart, $iColStart), $oExcel.Cells($iRowEnd, $iColEnd)).Borders($xlEdgeTop) .LineStyle = $xlContinuous .ColorIndex = $xlAutomatic .Weight = $sBorderStyle EndWith EndIf if $iEdgeBottom = 1 Then With $oExcel.Activesheet.Range($oExcel.Cells($sRangeOrRowStart, $iColStart), $oExcel.Cells($iRowEnd, $iColEnd)).Borders($xlEdgeBottom) .LineStyle = $xlContinuous .ColorIndex = $xlAutomatic .Weight = $sBorderStyle EndWith EndIf if $iEdgeRight = 1 Then With $oExcel.Activesheet.Range($oExcel.Cells($sRangeOrRowStart, $iColStart), $oExcel.Cells($iRowEnd, $iColEnd)).Borders($xlEdgeRight) .LineStyle = $xlContinuous .ColorIndex = $xlAutomatic .Weight = $sBorderStyle EndWith EndIf if $iEdgeInsideV = 1 Then With $oExcel.Activesheet.Range($oExcel.Cells($sRangeOrRowStart, $iColStart), $oExcel.Cells($iRowEnd, $iColEnd)).Borders($xlInsideVertical) .LineStyle = $xlContinuous .ColorIndex = $xlAutomatic .Weight = $sBorderStyle EndWith EndIf if $iEdgeInsideH = 1 Then With $oExcel.Activesheet.Range($oExcel.Cells($sRangeOrRowStart, $iColStart), $oExcel.Cells($iRowEnd, $iColEnd)).Borders($xlInsideHorizontal) .LineStyle = $xlContinuous .ColorIndex = $xlAutomatic .Weight = $sBorderStyle EndWith EndIf Else if $iEdgeLeft = 1 Then With $oExcel.Activesheet.Range($sRangeOrRowStart).Borders($xlEdgeLeft) .LineStyle = $xlContinuous .ColorIndex = $xlAutomatic .Weight = $sBorderStyle EndWith EndIf if $iEdgeTop = 1 Then with $oExcel.Activesheet.Range($sRangeOrRowStart).Borders($xlEdgeTop) .LineStyle = $xlContinuous .ColorIndex = $xlAutomatic .Weight = $sBorderStyle EndWith EndIf if $iEdgeBottom = 1 Then With $oExcel.Activesheet.Range($sRangeOrRowStart).Borders($xlEdgeBottom) .LineStyle = $xlContinuous .ColorIndex = $xlAutomatic .Weight = $sBorderStyle EndWith EndIf if $iEdgeRight = 1 Then With $oExcel.Activesheet.Range($sRangeOrRowStart).Borders($xlEdgeRight) .LineStyle = $xlContinuous .ColorIndex = $xlAutomatic .Weight = $sBorderStyle EndWith EndIf if $iEdgeInsideV = 1 Then With $oExcel.Activesheet.Range($sRangeOrRowStart).Borders($xlInsideVertical) .LineStyle = $xlContinuous .ColorIndex = $xlAutomatic .Weight = $sBorderStyle EndWith EndIf if $iEdgeInsideH = 1 Then With $oExcel.Activesheet.Range($sRangeOrRowStart).Borders($xlInsideHorizontal) .LineStyle = $xlContinuous .ColorIndex = $xlAutomatic .Weight = $sBorderStyle EndWith EndIf EndIf Return 1 EndFunc Mike Edited February 16, 2007 by MikeOsdx
BigDaddyO Posted February 16, 2007 Posted February 16, 2007 I just found out the hard way that the _ExcelCreateBorders() function I posted will only work with Office 2007. It appears that the .TintandShade is not supported. Also, the InsideHorizontal seems to be failing. I am trying to figure it out and will update when I do. Mike
Locodarwin Posted February 16, 2007 Author Posted February 16, 2007 Wow, my last post was wiped out. Twice! Thanks, Mike, for the submission. I'll include it in the upcoming version of the library once you get the 2007 parts cleared out. In the meantime, I'm looking into a different way of coding border functionality. Essentially what I'm looking to do is allow the user to select which border sides (top, left, bottom, etc.) to apply bordering properties to in a range while leaving the other border sides in said range untouched. This way we can apply different properties to different parts of the range. For example, if I want the top border of a cell to be thick, green, and dotted, I could call the function to do that, and then call the function later to apply different properties to other sides. My previous algorithm wouldn't allow for that. That, and the fact that it wasn't functioning properly anyway, is why I commented it out. So my idea will be to have a function like so... _ExcelCreateBorders($oExcel, $iBorders, $iBorderWeight, $iBorderStyle, $iBorderColor, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1) ...where $iBorder is a binary-OR type of side selection. So if... 1 = Top 2 = Bottom 4 = Left 8 = Right 16 = Interior Vertical ...then to select both the top and bottom $iBorders would be 1 (top) + 2 (bottom) = 3. To select the top and right borders, $iBorders would be 1 (top) + 8 (right) = 9. The function would then apply color, border thickness, and border line style to those borders only and leave the other borders alone. So that's the angle I'm looking for. It would improve bordering tenfold. Unfortunately I don't have much code for that yet, as I've been focusing on workbook sharing and printing, which are presenting their own problems. So if you'd like to work on that kind of function, I'd be more than happy to step aside on it, and of course your code would be included in the future library. There should be a companion function to remove all bordering from a range, as well. What says you? -S (Yet Another) ExcelCOM UDF"A human being should be able to change a diaper, plan an invasion, butcher a hog, conn a ship, design a building, write a sonnet, balance accounts, build a wall, set a bone, comfort the dying, take orders, give orders, cooperate, act alone, solve equations, analyze a new problem, pitch manure, program a computer, cook a tasty meal, fight efficiently, die gallantly...[indent]...specialization is for insects." - R. A. Heinlein[/indent]
BigDaddyO Posted February 16, 2007 Posted February 16, 2007 Locodarwin, Your Border idea sounds neat. I never looked into the colors. I don't think I will have time to re-write the Function as I always have a plate full of projects, but I look forward to seeing it when it's done. I have updated my previous Function so it now supports older versions of Excel. It seems that my Horizontal errors were being causes by a limitation of Excel COM. You can't apply more than a certian number of formats to a specific Cell without getting an error "80020009" I have not yet figured out how to duplicate my error outside my current project for further testing. I have since gotten around it by adding a border to the surrounding cells instead of directly to the heavily modified cell. Mike
MRDev Posted February 20, 2007 Posted February 20, 2007 Congratulation for the great UDF. I like it. But some functions (at least _ExcelSheetUsedRangeGet) only works for english Excel. Unfortunatly I've installed the german. Your code in _ExcelSheetUsedRangeGet: $sTemp = StringSplit($aSendBack[1], "C") $aSendBack[2] = Number($sTemp[2]) $aSendBack[3] = Number(StringRegExpReplace($aSendBack[0], "[^0-9]", "")) oÝ÷ Ù8b±Ú²}ý· +·jëx®f§¶Ô-l¶¸§Z(ÉbæuK!z· Just as an idea.
Locodarwin Posted February 20, 2007 Author Posted February 20, 2007 Yes, unfortunately I can't predict how my collection will behave on non-English installations. Sorry about that. Since your code change takes into account both German and English installations, I'll use it. That might be the only function that requires such a change, since in other situations I merely look for numbers vs. letters and not a particular letter. Thanks for the submission! -S (Yet Another) ExcelCOM UDF"A human being should be able to change a diaper, plan an invasion, butcher a hog, conn a ship, design a building, write a sonnet, balance accounts, build a wall, set a bone, comfort the dying, take orders, give orders, cooperate, act alone, solve equations, analyze a new problem, pitch manure, program a computer, cook a tasty meal, fight efficiently, die gallantly...[indent]...specialization is for insects." - R. A. Heinlein[/indent]
MRDev Posted February 22, 2007 Posted February 22, 2007 I found another function where you use the R1C1 format: _ExcelFindInRange Orig. code: $Temp1 = StringSplit($aR1C1[$xx], "C") $aFound[$xx][2] = Number($Temp1[2]) $aFound[$xx][3] = Number(StringReplace($Temp1[1], "R", "")) oÝ÷ Ù©§Â+ajëh×6 $Temp1 = StringRegExp($aR1C1[$xx], "[RZ]([^CS]*)[CS](.*)",3) $aFound[$xx][2] = Number($Temp1[1]) $aFound[$xx][3] = Number($Temp1[0])
Locodarwin Posted February 22, 2007 Author Posted February 22, 2007 Thanks, MRDev. Your changes, along with due credit, will be reflected in the next release. -S (Yet Another) ExcelCOM UDF"A human being should be able to change a diaper, plan an invasion, butcher a hog, conn a ship, design a building, write a sonnet, balance accounts, build a wall, set a bone, comfort the dying, take orders, give orders, cooperate, act alone, solve equations, analyze a new problem, pitch manure, program a computer, cook a tasty meal, fight efficiently, die gallantly...[indent]...specialization is for insects." - R. A. Heinlein[/indent]
Flandry Posted February 23, 2007 Posted February 23, 2007 Hello to everybody, I am very new to AutoIt and I need it to automate Excel. Your UDF is a great help Locodarwin! Like MRDev I also use a german Excel-Version and I need to "calculate" witht the given range of the "_ExcelFindInRange"-function. I have changed your UDF as adviced by MRDev and tried the following statement: $zellen=_ExcelFindInRange($oExcel, $suche, 1) For $nummer1=1 to $zellen[0][0] ;MsgBox(4096,"DeBug", "Anzahl = " & $nummer) MsgBox(4096,"DeBug", "Namen = " & $zellen[$nummer1][0]) Next The excel sheet has thousands of rows and 27 cells would match the search string of $suche I always get only the first cell as result when I use R1C1. When I try the A1 I get all of them. What am I doing wrong? Thank you in advance for your help!
MRDev Posted February 23, 2007 Posted February 23, 2007 Hi Flandry Have a look at the parameters default values of the function. Your call of _ExcelFindInRange will search with the following values: $sRangeOrRowStart = 1 $iColStart = 1 $iRowEnd = 1 $iColEnd = 1 This is exactly 1 cell.
Flandry Posted February 25, 2007 Posted February 25, 2007 ;-)) Thanks a lot! I have misunderstood the specification. I am afraid that my next question will follow soon;-) Best regards
litlmike Posted February 26, 2007 Posted February 26, 2007 Think I found a typo in your UDF. $sScreenTip is not found in the syntax. Also, can you give us an idea of what $sScreenTip is, and should look like?; Description: Inserts a hyperlink into the active page. ; Syntax: _ExcelHyperlinkInsert($oExcel, $sLinkText, $sAddress, $sRangeOrRow, $iColumn = 1) ; Parameter(s): $oExcel - An Excel object opened by a preceding call to _ExcelBookOpen() or _ExcelBookNew() ; $sLinkText - The text to display the hyperlink as ; $sAddress - The URL to link to, as a string ; $sScreenTip - The popup screen tip, as a string ; $sRangeOrRow - The range in A1 format, or a row number for R1C1 format ; $iColumn - The specified column number for R1C1 format (default = 1) _ArrayPermute()_ArrayUnique()Excel.au3 UDF
Locodarwin Posted February 27, 2007 Author Posted February 27, 2007 It was just missing from the Syntax comment line. Thanks for bringing it to my attention. The $sScreenTip parameter displays a text tip when the user hovers the mouse over the hyperlink. It can be any reasonably-sized string. -S (Yet Another) ExcelCOM UDF"A human being should be able to change a diaper, plan an invasion, butcher a hog, conn a ship, design a building, write a sonnet, balance accounts, build a wall, set a bone, comfort the dying, take orders, give orders, cooperate, act alone, solve equations, analyze a new problem, pitch manure, program a computer, cook a tasty meal, fight efficiently, die gallantly...[indent]...specialization is for insects." - R. A. Heinlein[/indent]
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