BigDaddyO Posted August 17, 2015 Share Posted August 17, 2015 I have a script that pull data out of each spreadsheet in a folder "These are error logs in .xlsx format"It worked fine on my sample folder which only had 3 spreadsheets but when I'm running them on the full list (currently 123) I get an error on _Excel_BookOpen. oddly it's not always on the same file. I just ran it 5 times and each time it was a different spreadsheet. Error is always the same.$oExcel.Windows($oWorkbook.Name).Visible = $bVisible$oExcel.Windows($oWorkbook.Name)^ ERRORI tried adding the default Com error handler from the help file but when I did that, I actually got an AutoIT has stopped responding message.This is my script to pull the data:For $i = 1 To UBound($aSpreadsheets) - 1 ConsoleWrite("Excel file: " & $aSpreadsheets[0] & "\" & $aSpreadsheets[$i] & @CRLF) $oExcel = _Excel_Open(False, False, False) If @error Then If MsgBox(262161, "Error", "Unable to open the following report" & @CRLF & $aSpreadsheets[0] & "\" & $aSpreadsheets[$i] & @CRLF & "Click OK to skip and continue, or Cancel to stop") = 2 Then Return SetError(1) Else $oExcelBook = _Excel_BookOpen($oExcel, $aSpreadsheets[0] & "\" & $aSpreadsheets[$i], True, False) if $i = 1 Then $aData = _Excel_RangeRead($oExcelBook, Default, $oExcelBook.ActiveSheet.Usedrange.Columns($Columns), 1, True) _ArrayDelete($aData, 0) ;Removes the header row Else $aDataB = _Excel_RangeRead($oExcelBook, Default, $oExcelBook.ActiveSheet.Usedrange.Columns($Columns), 1, True) _ArrayDelete($aDataB, 0) ;Removes the header row EndIf If @error Then MsgBox(0, "Excel Error", "Error = " & @error & @CRLF & "Extended = " & @extended) _Excel_Close($oExcel, False) SetError(1) Return EndIf _Excel_BookClose($oExcelBook, False) _Excel_Close($oExcel, False) If $i > 1 Then _ArrayConcatenate($aData, $aDataB) EndIf NextI'm using v3.3.14.1Any ideas?Thanks,Mike Link to comment Share on other sites More sharing options...
water Posted August 17, 2015 Share Posted August 17, 2015 Move _Excel_Open out of the loop:$oExcel = _Excel_Open(False, False, False) If @error Then If MsgBox(262161, "Error", "Unable to open the following report" & @CRLF & $aSpreadsheets[0] & "\" & $aSpreadsheets[$i] & @CRLF & "Click OK to skip and continue, or Cancel to stop") = 2 Then Return SetError(1) EndIf For $i = 1 To UBound($aSpreadsheets) - 1 ConsoleWrite("Excel file: " & $aSpreadsheets[0] & "\" & $aSpreadsheets[$i] & @CRLF) $oExcelBook = _Excel_BookOpen($oExcel, $aSpreadsheets[0] & "\" & $aSpreadsheets[$i], True, False) If $i = 1 Then $aData = _Excel_RangeRead($oExcelBook, Default, $oExcelBook.ActiveSheet.Usedrange.Columns($Columns), 1, True) _ArrayDelete($aData, 0) ;Removes the header row Else $aDataB = _Excel_RangeRead($oExcelBook, Default, $oExcelBook.ActiveSheet.Usedrange.Columns($Columns), 1, True) _ArrayDelete($aDataB, 0) ;Removes the header row EndIf If @error Then MsgBox(0, "Excel Error", "Error = " & @error & @CRLF & "Extended = " & @extended) _Excel_Close($oExcel, False) SetError(1) Return EndIf _Excel_BookClose($oExcelBook, False) If $i > 1 Then _ArrayConcatenate($aData, $aDataB) Next _Excel_Close($oExcel, False) 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...
BigDaddyO Posted August 17, 2015 Author Share Posted August 17, 2015 (edited) just finished trying that and I also moved that one error trap up where it should have been but i'm still getting the same error on random spreadsheets.expandcollapse popupFunc _GetDataFromSpreadsheets($aSpreadsheets, $Columns) GUICtrlSetData($hPickListProgress, 1) $oExcel = _Excel_Open(False, False, False) If @error Then MsgBox(0, "Error", "Unable to open Excel") Return SetError(1) EndIf For $i = 1 To UBound($aSpreadsheets) - 1 ConsoleWrite("Excel file: " & $aSpreadsheets[0] & "\" & $aSpreadsheets[$i] & @CRLF) $oExcelBook = _Excel_BookOpen($oExcel, $aSpreadsheets[0] & "\" & $aSpreadsheets[$i], True) If @error Then MsgBox(0, "Excel Error", "Error = " & @error & @CRLF & "Extended = " & @extended) _Excel_BookClose($oExcelBook, False) _Excel_Close($oExcel, False) SetError(1) Return EndIf if $i = 1 Then $aData = _Excel_RangeRead($oExcelBook, Default, $oExcelBook.ActiveSheet.Usedrange.Columns($Columns), 1, True) _ArrayDelete($aData, 0) ;Removes the header row Else $aDataB = _Excel_RangeRead($oExcelBook, Default, $oExcelBook.ActiveSheet.Usedrange.Columns($Columns), 1, True) _ArrayDelete($aDataB, 0) ;Removes the header row EndIf _Excel_BookClose($oExcelBook, False) If $i > 1 Then _ArrayConcatenate($aData, $aDataB) $iPct = Round(100 * ($i / UBound($aSpreadsheets) ), 0) ;Calculate the current percentage complete for this file rounded to the nearest 10 "-1" GUICtrlSetData($hPickListProgress, $iPct) Next _Excel_Close($oExcel, False) ConsoleWrite("Total records = " & UBound($aData) & @CRLF) Return $aData EndFunc Edited August 17, 2015 by BigDaddyO Link to comment Share on other sites More sharing options...
BigDaddyO Posted August 17, 2015 Author Share Posted August 17, 2015 In an attempt to look closer at the random spreadsheets that were failing I switched it to display the spreadsheets while running$oExcel = _Excel_Open(True, False, True)Using it like this, it processed all spreadsheets. I tried (False, False, True) and (True, False, False) and got the error message with those.Not sure why (True, False, True) is the magic scenario but I can't exactly leave it like that. Thanks,Mike Link to comment Share on other sites More sharing options...
BigDaddyO Posted August 17, 2015 Author Share Posted August 17, 2015 Got it working:it appears to be the $bUpdateLinks option. if I set it to 0 it works. not sure why it doesn't like Default "sometimes".$oExcelBook = _Excel_BookOpen($oExcel, $aSpreadsheets[0] & "\" & $aSpreadsheets[$i], True, False, Default, Default, 0)None of these spreadsheets have any links either. Mike Link to comment Share on other sites More sharing options...
water Posted August 17, 2015 Share Posted August 17, 2015 Strange, but great you could solve your problem 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