Bowmore Posted January 31, 2008 Posted January 31, 2008 Minor Bug ReportIn the function _ExcelFindInRange() the variable $sFound is declared as local but in the actual body of the function $oFound is used and $sFound is not referenced. "Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the universe trying to build bigger and better idiots. So far, the universe is winning."- Rick Cook
Dizzy Posted February 1, 2008 Posted February 1, 2008 I've the same problme with _ExcelFindInRange(). I did'nt get the results in array[x][x]. I want to search for a number with _ExcelFindInRange() and replace it with a another. Can you check this and give us a short example? Thanks! Dizzy
Bowmore Posted February 1, 2008 Posted February 1, 2008 I've the same problme with _ExcelFindInRange(). I did'nt get the results in array[x][x]. I want to search for a number with _ExcelFindInRange() and replace it with a another. Can you check this and give us a short example? Thanks! DizzyQuick fix is to replace $sFound with $oFound in the _ExcelFindInRange() function in the udf. "Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the universe trying to build bigger and better idiots. So far, the universe is winning."- Rick Cook
Dizzy Posted February 2, 2008 Posted February 2, 2008 Quick fix is to replace $sFound with $oFound in the _ExcelFindInRange() function in the udf. Hi Bowmore, yes, i was on the same way, but it doesn't work for me. I try this code and want to change the found cell to "FOUND" Can you have a look? #include <array.au3> #include <ExcelCOM_UDF.au3> Dim $array[40][40] $oExcel = _ExcelBookNew() For $n = 1 To 20 _ExcelWriteCell($oExcel, ($n * 10) + 1 & ".2.3.4", $n) Next ; Syntax: _ExcelFindInRange($oExcel, $sFindWhat, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1, _ ; $iDataType = 0, $iWholeOrPart = 1, $iSearchOrder = 1, $iSearchDirection = 1, $fMatchCase = False, _ ; $fMatchFormat = False) ; Return Value(s): On Success - Returns a two dimensional array with addresses of matching cells. If no matches found, returns null string ; $array[0][0] - The number of found cells ; $array[x][0] - The address of found cell x in A1 format ; $array[x][1] - The address of found cell x in R1C1 format ; $array[x][2] - The row of found cell x as an integer ; $array[x][3] - The column of found cell x as an integer $avResult = _ExcelFindInRange($oExcel, "101.2.3.4", "A1:A255") _ExcelWriteCell($oExcel, "Array Info start",4, 4) _ExcelWriteCell($oExcel, $array[0][0], 5, 4) _ExcelWriteCell($oExcel, $array[1][0], 6, 4) _ExcelWriteCell($oExcel, $array[1][1], 7, 4) _ExcelWriteCell($oExcel, $array[1][2], 8, 4) _ExcelWriteCell($oExcel, $array[1][3], 9, 4) _ExcelWriteCell($oExcel, "Array Info end",10, 4) MsgBox(64,"Info ", "Array return ok?") _ExcelBookClose($oExcel) Thx. Dizzy
Dizzy Posted February 2, 2008 Posted February 2, 2008 grrr - STUPID .... i have to open the workbook first .... OK - now it works. Dizzy
Locodarwin Posted February 3, 2008 Author Posted February 3, 2008 @Bowmore: You're right that the local variable is defined incorrectly due to a typo. I'll correct that for the next version. However, the "problem" is completely benign, which is probably why no one has reported it as a bug until now. Thanks for the report! -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]
laffo16 Posted February 5, 2008 Posted February 5, 2008 i was wonderng if anyone can help me with this issue, in the following code: Send("{PAUSE}") $file_path = "C:\Documents and Settings\dean\Desktop\GOLD\blah.xls" Global $oExcel = _ExcelBookOpen ($file_path, 1) _ExcelBookClose($oExcel, 1) Send("{PAUSE}") i want to be able to open excel doc's after the _ExcelBookClose($oExcel, 1), so i pause the script to attempt to do this, but i dont understand when on the 2nd Pause when i open the doc i get a transparrent looking window with no cell's. i will take screen shots if need be, but i was hoping someone may know how to fix this. (using excel 2000) For reasons which i dont understand, $oExcel.Quit wasnt closing EXCEL.EXE properly, EXCEL.EXE remained open and could be seen from the task manager, only after terminating the process manually or quiting autoit was I able to load up and view another spread sheet correctly. If anyone one else has the same problem, you can fix this by getting the ProcessList("EXCEL.EXE") before and after the new excel book is opened, determin the PID of the new excel book process, and processclose it once finished.
sensalim Posted February 8, 2008 Posted February 8, 2008 (edited) Your UDF is really helpful... thank you so much.But how do I saveas dbf4? I tried$oExcel.ActiveWorkBook.SaveAs (@ScriptDir & "\Orders.dbf", "xlDBF4")but I think I need the const value, not "xlDBF4"Help...?EDIT2:Ah sweet jebus... here it is, it's 11.xlAddIn = 18 xlCSV = 6 xlCSVMac = 22 xlCSVMSDOS = 24 xlCSVWindows = 23 xlCurrentPlatformText = -4158 xlDBF2 = 7 xlDBF3 = 8 xlDBF4 = 11 xlDIF = 9 xlExcel2 = 16 xlExcel2FarEast = 27 xlExcel3 = 29 xlExcel4 = 33 xlExcel4Workbook = 35 xlExcel5 = 39 xlExcel7 = 39 xlExcel9795 = 43 xlHtml = 44 xlIntlAddIn = 26 xlIntlMacro = 25 xlSYLK = 2 xlTemplate = 17 xlTextMac = 19 xlTextMSDOS = 21 xlTextPrinter = 36 xlTextWindows = 20 xlUnicodeText = 42 xlWebArchive = 45 xlWJ2WD1 = 14from: http://www.webservertalk.com/message1438263.html Edited February 8, 2008 by sensalim
sensalim Posted February 9, 2008 Posted February 9, 2008 Doooomed! this saveas to dbf4 does not work on Excel 2007! Argh stupid microsofttttttttttttttttttttttttt!
Leagnus Posted February 25, 2008 Posted February 25, 2008 Thanks a lot for such amazing UDF! It works good for me and saves a lot of my time! How to set xlNone color of a cell (after it has been colored)? What should i put here instead of 16777215: local $FFile = iniRead(@ScriptDir & "\ColorSet.ini", "Main", "XlsFile", "") local $vSheet = iniRead(@ScriptDir & "\ColorSet.ini", "Main", "SheetNo", "") $vSheet = Abs ($vSheet) ; strange, isn't it? #include <ExcelCOM_UDF.au3> Local $oExcel = _ExcelBookOpen($FFile, 1) local $used=_ExcelSheetUsedRangeGet($oExcel, $vSheet) _ExcelCellColorSet($oExcel, 1, 1, $used[3], $used[2], 255, "16777215")
Leagnus Posted February 26, 2008 Posted February 26, 2008 Aha, $oExcel.Activesheet.Range("A1:J80").ClearFormats works fine
DjDeep00 Posted February 29, 2008 Posted February 29, 2008 With the following code...I am still getting the Excel alerts... $ObjExcel=_ExcelBookOpen($XML_File,0) _ExcelBookSaveAs($ObjExcel, $CSV_File, "csv",0,1) _ExcelBookClose($ObjExcel) Please let me know what I am doing wrong.
Locodarwin Posted February 29, 2008 Author Posted February 29, 2008 (edited) Yes. It's an unfortunate by-product of trying to reset the DisplayAlerts property back to default after the Save method in the _ExcelClose() function. Doing so changes the properties of the document, and therefore the document's "Saved" flag is unset again just before the Quit method is called. I'm still at odds as to how I will resolve the issue. I'm probably going to end up not resetting the DisplayAlerts back to True, though I know for many people that might cause a problem. The good news is that you can do this yourself and bypass the UDF's way of doing it. Try this bit of code as a replacement for yours: $ObjExcel=_ExcelBookOpen($XML_File,0) _ExcelBookSaveAs($ObjExcel, $CSV_File, "csv",0,1) $ObjExcel.Application.DisplayAlerts = 0 $ObjExcel.ActiveWorkBook.Save $ObjExcel.Quit Be advised that the state of DisplayAlerts is saved with the document - meaning, the next time you open it, via script or by hand, you won't receive alerts when you attempt to overwrite, etc. -S Edited February 29, 2008 by Locodarwin (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 February 29, 2008 Author Posted February 29, 2008 Actually, I've just decided how I'll do it. I'll set the Saved property itself back to True after setting the DisplayAlerts property to default. While it's not a solution I like, it's one that'll work for everyone. I'll make that change in an upcoming update. -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]
LostOneInLine Posted March 5, 2008 Posted March 5, 2008 Quick question: After copying a row in excel (array), I want to paste certain cells in certain areas of a different program. I've tried all kinds of things to no avail. I'm not sure how to reference a particular cell of the array that has been copied. (such as copying line 12 [$12:$12], switching programs, and pasting cell G [$G$12]. Great post btw. Very clean and simply laid out script.
sensalim Posted March 6, 2008 Posted March 6, 2008 Any workaround for saving to dbf4 when using office 2007? Like carry over office 2003 dll file(s) or something? I don't know. Help?
testmeout Posted March 7, 2008 Posted March 7, 2008 Love the UDF found a bug though In the function _ExcelCellMerge you have If $fDoMerge <> False Or $fDoMerge <> True Then Return SetError(4, 0, 0) which makes it throw an error for me so I changed it to If $fDoMerge <> False AND $fDoMerge <> True Then Return SetError(4, 0, 0) and now works great Thanks
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