pro2gramer5 Posted June 22, 2016 Share Posted June 22, 2016 Hey guys, New to the forum; been doing pretty well for the last few months writing my code by reading countless wikis, help files, forums, etc. but now I'm a little stuck. I'm writing a relatively simple code (below) using For..To loops to go through multiple sheets in an excel file and read used ranges in a given column, then, writing a unique ini file based on the name of the active worksheet, and writing the array of that column to that ini file. I wouldn't have to do this if AutoIt could just read excel values without having to open the file... I have 2 problems: 1. It's writing the same array (column data of the 1st worksheet) to each ini file 2. After going through 10 or so worksheets, it generates a COM error (80020008, bad variable type), and returns @error = 3 for "_Excel_RangeRead" which means the range is invalid. Every worksheet in the excel file has the same formatting and the same amount of columns, especially the column I'm requesting to be pulled. Help would be appreciated!! expandcollapse popup#Include <MsgBoxConstants.au3> #Include <Excel.au3> #Include <WinAPIFiles.au3> Global $oMyError = ObjEvent("AutoIt.Error","MyErrFunc") ; Initialize a COM error handler Global $oExcel = _Excel_Open() If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended) Global $oWorkbook = _Excel_BookOpen ($oExcel, @ScriptDir & "\Sample Physician List.xlsx") If @error Then MsgBox($MB_SYSTEMMODAL, "Test", "Error opening workbook") _Excel_Close($oExcel) Exit EndIf Local $sSheet = _Excel_SheetList ($oWorkbook) For $i = 0 To UBound($sSheet) - 1 $sResult = _Excel_RangeRead ($oWorkbook, $sSheet[$i][0], $oWorkbook.ActiveSheet.Usedrange.Columns("E:E")) If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example 1", "Error reading from workbook." & @CRLF & "@error = " & @error & ", @extended = " & @extended & @CRLF & "Client = " & $sSheet[$i][0]) For $k = 1 To UBound($sResult) - 1 IniWriteSection ($sSheet[$i][0] & ".tmp", "Physician" & $k, $sResult[0], $sResult[$k]) Next Next MsgBox($MB_SYSTEMMODAL, "Message", "Export Complete!") Func MyErrFunc() Msgbox(0,"AutoItCOM Test","We intercepted a COM Error !" & @CRLF & @CRLF & _ "err.description is: " & @TAB & $oMyError.description & @CRLF & _ "err.windescription:" & @TAB & $oMyError.windescription & @CRLF & _ "err.number is: " & @TAB & hex($oMyError.number,8) & @CRLF & _ "err.lastdllerror is: " & @TAB & $oMyError.lastdllerror & @CRLF & _ "err.scriptline is: " & @TAB & $oMyError.scriptline & @CRLF & _ "err.source is: " & @TAB & $oMyError.source & @CRLF & _ "err.helpfile is: " & @TAB & $oMyError.helpfile & @CRLF & _ "err.helpcontext is: " & @TAB & $oMyError.helpcontext _ ) Endfunc Link to comment Share on other sites More sharing options...
pro2gramer5 Posted June 22, 2016 Author Share Posted June 22, 2016 My apologies, in tinkering with the code, I left something that was incorrect. IniwriteSection is actually Iniwrite For $k = 1 To UBound($sResult) - 1 IniWrite ($sSheet[$i][0] & ".tmp", "Physician" & $k, $sResult[0], $sResult[$k]) Next Link to comment Share on other sites More sharing options...
water Posted June 22, 2016 Share Posted June 22, 2016 Welcome to AutoIt and the forum! First question: Which version of AutoIt do you run? 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 June 22, 2016 Moderators Share Posted June 22, 2016 @pro2gramer5 welcome to the forum. In your For Loop, even though you are stating $sSheet[$i][0] as the sheet you want to read from, you are then inputting ActiveSheet.UsedRange.Columns as your range parameter. Since you are not activating the next sheet, it continues to read only from the sheet that is active when you open the workbook. "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 June 22, 2016 Share Posted June 22, 2016 33 minutes ago, pro2gramer5 said: I wouldn't have to do this if AutoIt could just read excel values without having to open the file... Second question: AutoIt (and every other language) needs to OPEN a file to read the content. Do you mean you do not want to see Excel open and DISPLAY the workbook? If yes then the solution is to open Excel and the workbook hidden. 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...
pro2gramer5 Posted June 22, 2016 Author Share Posted June 22, 2016 Thanks for the reply! @water I'm using Autoit3 (should be the latest version?). What I meant to say is that it's not like ini content where you can read values without having to open the inifile itself. The content I need will be viewed/accessed by many people and having them all open an excel file simultaneously in a shared location can (and will) be troublesome. This is why I want to create an easy way to export the contents of the excel file to several ini files. @JLogan3o13 I tried tinkering with the range parameter, but I don't think it accepts variables. Is there a way to place a variable or to have it loop through worksheets? Link to comment Share on other sites More sharing options...
water Posted June 22, 2016 Share Posted June 22, 2016 2 minutes ago, pro2gramer5 said: The content I need will be viewed/accessed by many people and having them all open an excel file simultaneously in a shared location can (and will) be troublesome. _Excel_BookOpen allows to open a workbook as read-only. So multiple users accessing the same workbook shouldn't be a problem. 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...
water Posted June 22, 2016 Share Posted June 22, 2016 All functions allow you to specify the range as an object or as an "A1"-range (example: "A1:D7"). As soon as we are sure you can't solve your problem by directly accessing the Excel workbook we will check how to solve this problem 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...
water Posted June 22, 2016 Share Posted June 22, 2016 (edited) The following example starts 2 Excel intances in the background and opens the same workbook twice in read-only mode. #include <Excel.au3> Global $sPath = @ScriptDir & "\example.xlsx" $oExcel1 = _Excel_Open(False, False, False, False, True) ; Force a new instance $oWorkbook1 = _Excel_BookOpen($oExcel1, $sPath, True) $oExcel2 = _Excel_Open(False, False, False, False, True) ; Force a new instance $oWorkbook2 = _Excel_BookOpen($oExcel2, $sPath, True) MsgBox(0, "Info", "Finished ...!") _Excel_Close($oExcel1, False) _Excel_Close($oExcel2, False) Edited June 22, 2016 by water 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...
pro2gramer5 Posted June 22, 2016 Author Share Posted June 22, 2016 @water Even in read only, I've had issues when attempting to open a shared excel file, getting issues such as "The file is locked for editing by...". I've done extensive troubleshooting such as looking at shared drive configurations, user config, excel config, etc. and can't seem to replicate the issue. Another issue is that I'm having a GUI read the content from that excel file and display it in a listview format for the user. It's already time consuming to open the large excel file, but longer if the GUI were to encounter an issue with opening the file. Do you have any ideas for the code I posted? Link to comment Share on other sites More sharing options...
Moderators JLogan3o13 Posted June 22, 2016 Moderators Share Posted June 22, 2016 @pro2gramer5 if you must do it the way you have it, you can change the active worksheet with this at the beginning of your For loop: $oWorkbook.Worksheets($sSheet[$i][0]).Activate "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 June 22, 2016 Share Posted June 22, 2016 (edited) In Excel you can allow shared access to a single workbook. Now you will get an error as soon as a users opens the workbook in write mode and a second user tries to read the workbook. How large is the Excel workbook in question? I will post some code how to access the workbook by multiple users where one of them modifies the workbook. Edited June 22, 2016 by water 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...
pro2gramer5 Posted June 22, 2016 Author Share Posted June 22, 2016 @JLogan3o13 your code worked, thanks!! I seem to have surpassed the COM error by adding in a Sleep (500) after the 2nd loop. Maybe this has something to do with the fact that _Excel_RangeRead has the automatic cleanup at the end of the function...? Either way, the code I posted reads only one column but the final product will read a lot more. We'll see how that works out. @water The excel file is about 15MB, has about 40 worksheets all containing 40 columns with a data range of 500 - 4000 rows each. I'm definitely interested in the code you speak of because we encounter this issue with other shared excel files. However, the content in this particular spreadsheet will be displayed in a custom GUI/listview according to certain filters/criteria selected by the user. Having the GUI open the excel file, read the ranges, filter by selected criteria, display them, all the while the same process is being done by multiple people, is too time consuming. It's MUCH faster to do this with iniread loops. Link to comment Share on other sites More sharing options...
water Posted June 23, 2016 Share Posted June 23, 2016 (edited) Depending on the version of Excel you run there shouldn#t be a problem with multiple users accessing the same workbook. The following example has one Excel instance open the file R/W, alter cell A1 and have another instance open the same workbook as R/O. #include <Excel.au3> Global $sPath = @ScriptDir & "\example.xlsx" $oExcel1 = _Excel_Open(Default, Default, Default, Default, True) ConsoleWrite("1: " & @error & @CRLF) $oWorkbook1 = _Excel_BookOpen($oExcel1, $sPath, False) ConsoleWrite("2: " & @error & @CRLF) _Excel_RangeWrite($oWorkbook1, Default, "***") ConsoleWrite("3: " & @error & @CRLF) $oExcel2 = _Excel_Open(Default, Default, Default, Default, True) ConsoleWrite("4: " & @error & @CRLF) $oWorkbook2 = _Excel_BookOpen($oExcel2, $sPath, True) ConsoleWrite("5: " & @error & @CRLF) $sResult = _Excel_RangeRead($oWorkbook2, Default, "A1") ConsoleWrite("6: " & @error & "-" & $sResult & @CRLF) MsgBox(0, "Info", "Finished ...!") _Excel_Close($oExcel1, False) ConsoleWrite("7: " & @error & @CRLF) _Excel_Close($oExcel2, False) ConsoleWrite("8: " & @error & @CRLF) Edited June 23, 2016 by water 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...
water Posted June 23, 2016 Share Posted June 23, 2016 This thread describes what's going on when you get the "is locked for editing" message: https://blogs.technet.microsoft.com/the_microsoft_excel_support_team_blog/2012/05/14/the-definitive-locked-file-post-updated-772014/ When do you encounter this error message? When opening or when saving the workbook? 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...
pro2gramer5 Posted June 27, 2016 Author Share Posted June 27, 2016 The error message usually comes up when attempting to open the workbook. Some users have Excel 2010 while others have 2016. All users would need to open in R/W mode. Is this possible without generating that error message? Link to comment Share on other sites More sharing options...
water Posted June 27, 2016 Share Posted June 27, 2016 As long as each user works on a different worksheet this should be possible. 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
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