Popular Post water Posted September 8, 2012 Popular Post Share Posted September 8, 2012 (edited) This UDF is now part of AutoIt since 3.3.12.0. New versions of Microsoft Office have been released since the last changes were made to the Excel UDF. The new extensions (e.g. xlsx) are not (fully) supported, new functions are missing etc. The rewrite of the Excel UDF delivers changes in the following areas: Works with as many instances of Excel as you like - not just one Works with any Workbook - not just the active one Works with any Worksheet - not just the active one Only does what you tell it to do - no implicit "actions" Only one function to read from a cell or a range Only one function to write a string, an 1D or 2D array to a cell or a range Support for every file format Excel supports Speed enhancements when transferring data from/to an Excel sheet (20 - 100 times faster) 2014-03-22 - Beta 5 Known bugs None SCRIPT BREAKING The UDF has been renamed from "Excel Rewrite" to "Excel" - change your #include accordingly FIXED BUGS _Excel_BookOpen ignored $bVisible = True so a hidden workbook could never be shown with this function _Excel_BookSaveAs: Default changed from $xlWorkbookNormal to $xlWorkbookDefault to better handle all versions of Excel GENERAL Enhanced documentation, removed documentation bugs Preparation to add the UDF to the AutoIt source The example files (xls, jpg) where moved to subdirectory "Extras" The example scripts have been tested with Excel 2010 and AutoIt 3.3.10.2 on Windows 7.You need to run the scripts with the latest AutoIt production version (3.3.10.x)! Please test with Excel 2003 and Excel 2007 and post changes you need/want to see in the next beta version! Excel Rewrite Beta 5.zip has been removed as it is now part of AutoIt since 3.3.12.0. (627 downloads) History.txt Edited June 15, 2014 by water jaberwacky, ShawnLuo, Qwerty212 and 7 others 10 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...
BrewManNH Posted September 8, 2012 Share Posted September 8, 2012 I posted some code that gets the formula in a cell, rather than what is displayed. Example the cell formula is "=sum(A1:a5)", displayed 10, it will get the "=sum(A1:a5)" instead of the 10. If I posted any code, assume that code was written using the latest release version unless stated otherwise. Also, if it doesn't work on XP I can't help with that because I don't have access to XP, and I'm not going to.Give a programmer the correct code and he can do his work for a day. Teach a programmer to debug and he can do his work for a lifetime - by Chirag GudeHow to ask questions the smart way! I hereby grant any person the right to use any code I post, that I am the original author of, on the autoitscript.com forums, unless I've specifically stated otherwise in the code or the thread post. If you do use my code all I ask, as a courtesy, is to make note of where you got it from. Back up and restore Windows user files _Array.au3 - Modified array functions that include support for 2D arrays. - ColorChooser - An add-on for SciTE that pops up a color dialog so you can select and paste a color code into a script. - Customizable Splashscreen GUI w/Progress Bar - Create a custom "splash screen" GUI with a progress bar and custom label. - _FileGetProperty - Retrieve the properties of a file - SciTE Toolbar - A toolbar demo for use with the SciTE editor - GUIRegisterMsg demo - Demo script to show how to use the Windows messages to interact with controls and your GUI. - Latin Square password generator Link to comment Share on other sites More sharing options...
Myicq Posted September 9, 2012 Share Posted September 9, 2012 I have change a bit here and there in the Excel UDF myself. Have a few requests..Additional possibilities in the font parameters. Right now can only change bold, italic, underline. Perhaps something else than a (long) parameter list is better, different functions ? Not sure.. Font .name and .size at leastSet shading and coloring of cellsControl of borders on range / cell, both linestyle, width and colorControl of print area (mark area for printout)Run macro on excel side (execute VBA).Some examples:With Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous .ColorIndex = xlAutomatic .TintAndShade = 0 .Weight = xlThin End WithWith Selection.Font .Name = "Calibri" .Size = 14 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ThemeColor = xlThemeColorLight1 .TintAndShade = 0 .ThemeFont = xlThemeFontMinor End WithWith Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .ThemeColor = xlThemeColorLight2 .TintAndShade = 0.399975585192419 .PatternTintAndShade = 0 End WithWith Selection .HorizontalAlignment = xlLeft .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End WithWith Selection.Borders(xlEdgeLeft) .LineStyle = xlContinuous .ColorIndex = xlAutomatic .TintAndShade = 0 .Weight = xlThin End With lpxx 1 I am just a hobby programmer, and nothing great to publish right now. Link to comment Share on other sites More sharing options...
Myicq Posted September 9, 2012 Share Posted September 9, 2012 I found a resource that may come in handy for this: Excel Enumerations in a single txt formatThere is a blog on the same site that's worth a read for ExcelFreaks. I am just a hobby programmer, and nothing great to publish right now. Link to comment Share on other sites More sharing options...
water Posted September 9, 2012 Author Share Posted September 9, 2012 Thanks a lot for your post! The Excel UDF will always just contain basic functions. A function in the UDF has to do some processing because otherwise the function could be replaced by simply calling a method or accessing a property. Therefore there will be no function to run a macro (we removed the macro function from the Word UDF). You use $oExcel.Run(macroname, parameter1, ....) Maybe there wil be a function to work with ranges. The result can then be passed to other functions. Will have to think about. For the Enumeration part there might be an even simpler solution. Don't have more info at the moment. I will certainly read the blog, looks quite interesting. 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...
Spiff59 Posted September 10, 2012 Share Posted September 10, 2012 (edited) I'd posted #2199 in BugTracker a while back, but in last-minute clean-up I'd truncated the SetError() statements down to a single parameter, i.e: SetError(1) instead of SetError(1,0,0). That earned the ticket a thumbs-down. Even though it was suggested I correct and reactivate the ticket, I never did. Anyway, _ExcelSheetAddNew() causes AutoIt to crash if you specify a workbook name that already exists. When that happens, it also causes Excel to create a new and unwanted generically-named worksheet such as "Sheet1", "Sheet2", etc. I've been using the following version for a long time successfully. It adds an "@error = 2 " return, and avoids both the crash and the creation of a misnamed worksheet. ; |@error=2 - Specified sheet already exists Func _ExcelSheetAddNew($oExcel, $sName = "") If Not IsObj($oExcel) Then Return SetError(1, 0, 0) If $sName Then For $_ws In $oExcel.ActiveWorkbook.Sheets If $_ws.Name = $sName Then Return SetError(2, 0, 0) Next EndIf $oExcel.ActiveWorkBook.WorkSheets.Add().Activate() If $sName Then $oExcel.ActiveSheet.Name = $sName Return 1 EndFunc ;==>_ExcelSheetAddNew Edited September 11, 2012 by Spiff59 Link to comment Share on other sites More sharing options...
Spiff59 Posted September 10, 2012 Share Posted September 10, 2012 (edited) I've also had an issue with _ExcelBookOpen() in that if you call it to open a workbook in write-mode ($fReadOnly = False) and the file is already open by another user, _ExcelBookOpen() still returns no error, and returns an object to a read-only copy of the spreadsheet. You have no way of knowing if you can proceed with updates. I've been using the following for a couple years, that inserts a FileOpen/FileClose test, and returns a new @error=3 condition if the file is in use. The test is only performed when $fReadOnly = False. Maybe there is a better way to go about it? Regardless, when you request write access to a spreadsheet, _ExcelBookOpen() should tell you whether or not it has been granted. Func _ExcelBookOpen2($sFilePath, $fVisible = True, $fReadOnly = False, $sPassword = "", $sWritePassword = "") If Not FileExists($sFilePath) Then Return SetError(2, 0, 0) $fVisible = ($fVisible > 0) $fReadOnly = ($fReadOnly > 0) If Not $fReadOnly Then ; first check for open file - prior to launching Excel If FileOpen($sFilePath, 1) = -1 Then Return SetError(3, 0, 0) FileClose($sFilePath) EndIf Local $oExcel = ObjCreate("Excel.Application") If Not IsObj($oExcel) Then Return SetError(1, 0, 0) With $oExcel .Visible = $fVisible .WorkBooks.Open($sFilePath, Default, $fReadOnly, Default, $sPassword, $sWritePassword) If (Not $fReadOnly) And .ActiveWorkBook.Readonly Then ; second check for open file - after launching Excel $oExcel.Quit $oExcel = "" Return SetError(3, 0, 0) EndIf ; Select the first *visible* worksheet. For $i = 1 To .ActiveWorkbook.Sheets.Count If .ActiveWorkbook.Sheets($i).Visible = -1 Then ; $xlSheetVisible .ActiveWorkbook.Sheets($i).Select() ExitLoop EndIf Next EndWith Return $oExcel EndFunc ;==>_ExcelBookOpen Edit: regarding "Maybe there is a better way to go about it?". The FileOpen()/FileClose() test is much faster than actually launching an instance of Excel, but has the miniscule potential of finding a file closed, and then by the time Excel is launched, someone had opened it and you'll again be working with a read-only object and not know it. Maybe a second test should be added, testing the $fReadOnly flag, that would return the same @error=3 condition (as well as closing the instance of Excel) if the spreadsheet is not open in write-mode? I've yet to encounter that situation, where someone opened a file in the instance between the FileOpen() test and launching Excel, but it is remotely possible. Edit2: I updated the code with 2 tests to check if the file was returned in a read-only state. The second test would be the more reliable. The only reason to keep the first test would be that the FileOpen()/FileClose() method is much faster than launching Excel and opening the workbook. But, the frequency that one will request a file in write-mode and find it already open is probably low enough that we could just delete the initial FileOpen()/FileClose() test? Edited September 11, 2012 by Spiff59 Link to comment Share on other sites More sharing options...
water Posted September 10, 2012 Author Share Posted September 10, 2012 Thank you very much for your suggestions. I will test them as soon as I'm back from vacation (end of september). In the the cold and dark season I will have plenty of time to play with the UDF. 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...
Andy007 Posted September 11, 2012 Share Posted September 11, 2012 Just wanted to say "Thank you Water and the other dudes" - I am very happy to see this course of action and I greatly appreciate your efforts. <loud applause> Regards,Andy (no, the other one) Link to comment Share on other sites More sharing options...
water Posted September 11, 2012 Author Share Posted September 11, 2012 Anything special you want to see in the Excel UDF? 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...
RamadSula Posted September 13, 2012 Share Posted September 13, 2012 Water I just started using Autoit recently and wished I found your UDF before creating much of my own interface to EXCEL. Project: I am creating GUI that runs a Document Marquee that roates various Documents , i.e Word, PDF, Excel on a large display. There is one Excel document that is a maintenance log that is READ Only Request: I have an issue in which I can give focus to focus to the READ ONLY Excel Workbook. Is there a plan in your next UDF ( or does anyone know how ) to be able to Activate ( give focus to) a particular Read Only workbook without getting a "Invalid Index" I apologize for the indirect addressing , I created an editable array for management to change documents in the Marquee. Code: $logtxt = $Title[$Row][$Col] & " Attempt Active" Call("WritetoLog", $logtxt) $oExcel = ObjCreate("Excel.Application") ; Create initial Excel Object $oExcel.WorkBooks($Title[$Row][$Col]).Activate() ; Activate open workbook I use a Log system to a txt file to trap my errors instead of Popups 09/13/2012 08:52:37 maintenance crossover.xlsx [Read-Only] Attempt Active 09/13/2012 08:52:37 COM Error Hex Number is: 80020009 09/13/2012 08:52:37 COM Error Description is: Invalid index. Note: The READ-ONLY file will open but I can not switch to it via Activate() If I don't make maintenance crossover a Read-Only Activate() works just fine. Link to comment Share on other sites More sharing options...
water Posted September 13, 2012 Author Share Posted September 13, 2012 Use the Excel UDF that comes with AutoIt. Function _ExcelSheetActivate activates the desired sheet and returns an error if the sheet number is invalid (doesn't crash). 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...
RamadSula Posted September 27, 2012 Share Posted September 27, 2012 Water Thank you for the response. I didn't want to take the time to replace all my code so I examined the Excel UDF , extracted what I needed and came up with. $oExcel.WorkBooks($Title[$Row][$Col]).Sheets($Tab[$Row][$Col]).Select() I still get Invalid Index Errors on Read Only when attempting to Select a read only worksheet. 09/27/2012 13:57:49 WorkBook maintenance crossover.xlsx [Read-Only] Tab maint log Attempt 09/27/2012 13:57:49 COM Error Hex Number is: 80020009 09/27/2012 13:57:49 COM Error Description is: Invalid index. Any thoughts on what may be causing it? A thought has occurred to me while writing this plea. I made the Document Marquee yo be a scripted based GUI that can an launch a particular document, loop through all documents or configure a GUI button to document.. The launch of document was coded to act as a single loop, i.e. the document opened and was then activated. The act of activating the last open document is redundant but should not cause any errors The document opens with no error but if I activate a read only EXCEL document I get an error. Link to comment Share on other sites More sharing options...
water Posted September 27, 2012 Author Share Posted September 27, 2012 RamadSula, could you please do me a favour and open a new thread on the subject? This thread is to discuss functionality of the existing Excel UDF and how to modify it. Thanks! 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...
GMK Posted September 28, 2012 Share Posted September 28, 2012 I did a UDF for OOo/LibO Calc that includes some error checking on sheets, cells, and converting cell references, which could probably be applied here, if that would be helpful. Link to comment Share on other sites More sharing options...
GMK Posted September 28, 2012 Share Posted September 28, 2012 Also, if there is a way to make this backwards compatible with as many versions of Excel as possible, that would be helpful. I know it may seem improbable/impossible, but I still run Excel 2000 at home, while running Excel 2010 at work. I try to write AutoIT scripts that will work in both places. Link to comment Share on other sites More sharing options...
water Posted September 29, 2012 Author Share Posted September 29, 2012 If your scripts work with Excel 2000 now they should work with the brushed up version of the UDF as well. Backward compatibility is one of the main goals. 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...
RamadSula Posted September 29, 2012 Share Posted September 29, 2012 Water I will do that right now .However I think the basic Excel UDF is gonna have the same problem with trying to Activate a read only excel document. Link to comment Share on other sites More sharing options...
water Posted September 30, 2012 Author Share Posted September 30, 2012 Hi Spiff59, I've reopened ticket #2199 and implemented the solution you provided. 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 September 30, 2012 Author Share Posted September 30, 2012 I've also had an issue with _ExcelBookOpen() in that if you call it to open a workbook in write-mode ($fReadOnly = False) and the file is already open by another user, _ExcelBookOpen() still returns no error, and returns an object to a read-only copy of the spreadsheet. You have no way of knowing if you can proceed with updates. I've been using the following for a couple years, that inserts a FileOpen/FileClose test, and returns a new @error=3 condition if the file is in use. The test is only performed when $fReadOnly = False. Maybe there is a better way to go about it? Regardless, when you request write access to a spreadsheet, _ExcelBookOpen() should tell you whether or not it has been granted.I have been thinking about this problem and I think we should stick with what Excel provides. So no FileOpen/FileClose, just let Excel do the checking.If the workbook can't be opened read/write should weReturn "success" and set @extended to 1 to indicate that the workbook was opened read/only. The user then has to decide if he wants to work with the read/only versionReturn "error" and close the read/only version of the workbook (as you suggested in your example code)I would stick with the first solution because this behaviour is as it was before (backward compatibility). But the user gets additional information what happened.What do you think? 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