illostos Posted May 16, 2014 Share Posted May 16, 2014 I'm using Microsoft Excel 2010 (14.0.7116.5000 (32-Bit)) My Autoit Version is v3.3.8.1 Link to comment Share on other sites More sharing options...
water Posted May 16, 2014 Author Share Posted May 16, 2014 Can you please try the latest AutoIt beta version? It comes with the most current version of the new Excel UDF included. My UDFs and Tutorials: Spoiler UDFs: Active Directory (NEW 2024-07-28 - Version 1.6.3.0) - Download - General Help & Support - Example Scripts - Wiki ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki Task Scheduler (2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs: Excel - Example Scripts - Wiki Word - Wiki Tutorials: ADO - Wiki WebDriver - Wiki Link to comment Share on other sites More sharing options...
illostos Posted May 16, 2014 Share Posted May 16, 2014 The Beta Version worked. Thanks, I didn't know that the code was based on the newest beta Version Link to comment Share on other sites More sharing options...
Moderators JLogan3o13 Posted May 19, 2014 Moderators Share Posted May 19, 2014 (edited) I have been promising myself to look into the Excel rewrite for some time but haven't had the opportunity, so I know I am coming late to the party. I am now working on a script that requires the latest beta, so figured it was the perfect time. I am noticing one function in particular that I use a lot did not make it into the rewrite (_ExcelReadSheetToArray). Can you please explain if there is a method for doing this in the new UDF (ExcelRangeRead?)? Edited May 19, 2014 by JLogan3o13 "Profanity is the last vestige of the feeble mind. For the man who cannot express himself forcibly through intellect must do so through shock and awe" - Spencer W. Kimball How to get your question answered on this forum! Link to comment Share on other sites More sharing options...
water Posted May 19, 2014 Author Share Posted May 19, 2014 According to the help file $aAllCells = _Excel_RangeRead($oWorkbook) ; Read all used cells of the active worksheet $aAllCells = _Excel_RangeRead($oWorkbook, 2) ; Read all used cells of worksheet 2 $aAllCells = _Excel_RangeRead($oWorkbook, "Test") ; Read all used cells of worksheet named "Test" My UDFs and Tutorials: Spoiler UDFs: Active Directory (NEW 2024-07-28 - Version 1.6.3.0) - Download - General Help & Support - Example Scripts - Wiki ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki Task Scheduler (2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs: Excel - Example Scripts - Wiki Word - Wiki Tutorials: ADO - Wiki WebDriver - Wiki Link to comment Share on other sites More sharing options...
Moderators JLogan3o13 Posted May 19, 2014 Moderators Share Posted May 19, 2014 (edited) Thanks, must have skipped over that as I do not see that simple a notation in the help file. The closest I came was something like this: _Excel_RangeRead($oWorkbook, Default, $oWorkbook.ActiveSheet.UsedRange.Columns("A:F"), 1). I will try your example. Edited May 19, 2014 by JLogan3o13 "Profanity is the last vestige of the feeble mind. For the man who cannot express himself forcibly through intellect must do so through shock and awe" - Spencer W. Kimball How to get your question answered on this forum! Link to comment Share on other sites More sharing options...
KarlosTHG Posted May 27, 2014 Share Posted May 27, 2014 (edited) Hi guys, I have been using this UDF and found a problem, I am using BEx Analyzer wich is an addin for Excel, this addin connects the aplication to a SAP server with a little login dialog, In my script I need to automatize this, so I do this: -Open the Excel with the addin with its own launcher [OK] -Open the XLS file I need [OK] -Call from Autoit the Addin macro that refresh the file with the last data [OK] *At this step the login dialog appears, now the problem is that my script stops here until I close this dialog, but I need to continue the script so it can enter the credentials automatically. I know I can use another process to put the login info, but I want to keep it as simple as possible (one .exe only). I even did some Event handler in the worksheet so it trigger the macro when a cell value was 1, so I only had to write this value from Autoit, but the result is the same, the execution stops until I close the login window. How can I return to the code execution as soon as I trigger the macro? Thanks! Edited May 27, 2014 by KarlosTHG Link to comment Share on other sites More sharing options...
water Posted May 27, 2014 Author Share Posted May 27, 2014 As AutoIt does no multitasking and a Window waiting for user input is a blocking function there is nothing you can do but start a second script to handle the window before you run the macro. Please have a a look at the OutlookEX UDF where this approach is used to click away Outlook security warnings. My UDFs and Tutorials: Spoiler UDFs: Active Directory (NEW 2024-07-28 - Version 1.6.3.0) - Download - General Help & Support - Example Scripts - Wiki ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki Task Scheduler (2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs: Excel - Example Scripts - Wiki Word - Wiki Tutorials: ADO - Wiki WebDriver - Wiki Link to comment Share on other sites More sharing options...
dar100111 Posted May 27, 2014 Share Posted May 27, 2014 Hey Water! Love the Excel UDF's. Took me a second to get used to it but they work great. Where can I learn more about all the extensions (not sure if I said that the right way) like $oExcel.Columns.AutoFit ? I just saw another user's script with this example and was wondering what all you can do with the code. Wanted to get a better understanding. Thanks! Link to comment Share on other sites More sharing options...
water Posted May 27, 2014 Author Share Posted May 27, 2014 On MSDN you'll find the Excel Object Model Reference. My UDFs and Tutorials: Spoiler UDFs: Active Directory (NEW 2024-07-28 - Version 1.6.3.0) - Download - General Help & Support - Example Scripts - Wiki ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki Task Scheduler (2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs: Excel - Example Scripts - Wiki Word - Wiki Tutorials: ADO - Wiki WebDriver - Wiki Link to comment Share on other sites More sharing options...
dar100111 Posted May 27, 2014 Share Posted May 27, 2014 I consider myself pretty new to coding still but I think this will change my life. Thanks! Link to comment Share on other sites More sharing options...
Bearpocalypse Posted June 6, 2014 Share Posted June 6, 2014 Due to power issues on my desktop I built myself a new one. When I downloaded AutoIt and ran my latest project I freaked out when I got slammed with all kinds of errors. That will teach me not to pay attention to what version I'm downloading. Anyway, I have a function that writes lots of stuff based on the last empty row. You may remember from my last topic started Water. That function was _ExcelWriteCell($oExcel, $Data, $newRow, #) I have, the excelwrite command(the excel book, the data, the next empty row, number of the column to write data to). Now, do I need to convert the row to a number using _Excel_ColumnToNumber to be able to get this working again? $newRow = $oExcel.Cells.SpecialCells($xlCellTypeLastCell).Row + 1 ;This is working great still when finding the next empty row. $ACol = _Excel_ColumnToNumber('A') _Excel_RangeWrite($oBook, 'Sheet1', $Data, $ACol) This is what I have been beating around with, obviously it is wrong. Do I need to start listing the columns into arrays now or do I just suck and the answer is much easier? If I specify the cell, ie A2875 instead of $ACol, then the data gets written. Link to comment Share on other sites More sharing options...
water Posted June 6, 2014 Author Share Posted June 6, 2014 Use $iNewRow = $oExcel.Cells.SpecialCells($xlCellTypeLastCell).Row + 1 ;This is working great still when finding the next empty row. _Excel_RangeWrite($oBook, 'Sheet1', $Data, "A" & $iNewRow) because the UDF only supports "A1" notation. My UDFs and Tutorials: Spoiler UDFs: Active Directory (NEW 2024-07-28 - Version 1.6.3.0) - Download - General Help & Support - Example Scripts - Wiki ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki Task Scheduler (2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs: Excel - Example Scripts - Wiki Word - Wiki Tutorials: ADO - Wiki WebDriver - Wiki Link to comment Share on other sites More sharing options...
Bearpocalypse Posted June 6, 2014 Share Posted June 6, 2014 Derp, I didn't even think about using that. I guess that's why you are the super smart one. Many thanks! Link to comment Share on other sites More sharing options...
water Posted June 6, 2014 Author Share Posted June 6, 2014 I spent so much time in rewriting the UDF - it's easy to be smart then My UDFs and Tutorials: Spoiler UDFs: Active Directory (NEW 2024-07-28 - Version 1.6.3.0) - Download - General Help & Support - Example Scripts - Wiki ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki Task Scheduler (2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs: Excel - Example Scripts - Wiki Word - Wiki Tutorials: ADO - Wiki WebDriver - Wiki Link to comment Share on other sites More sharing options...
Recommended Posts