dgendrud Posted August 3, 2023 Share Posted August 3, 2023 I have been having an issue with random fatal error messages in reading an Excel spreadsheet single cell value. I'm using Microsoft Student Home & Office 2021 software, not Microsoft 365. I have tried using 2 methods to read the cells. First, using a VBA statement: $DataSym=$oExcelTRADING.Sheets($WLsheet).cells($rCnt, 1).value This works most of the time, but occasionally will fail to execute, with the following type of error message: ;COPIED ERRORS TO FIX ************************************************************************ "H:\Google Drive Folder\main\A TRADING 2023\SCRIPTS DEV\IP_TradeScript_wip_070723.au3" (664) : $DataSym=$oExcelTRADING.Sheets($WLsheet).cells($rCnt, 1).value $DataSym=$oExcelTRADING^ ERROR "H:\Google Drive Folder\main\A TRADING 2023\SCRIPTS DEV\IP_TradeScript_wip_070723.au3" (438) : $UDPortTkr = $oExcelTRADING.Sheets("IP_WL_t").cells($UDPortCtr, 1).value $UDPortTkr = $oExcelTRADING^ ERROR In trying to solve this, I changed to an Excel UDF statement: $sTkrloc = "A" & $rCnt $DataSym = _Excel_RangeRead($oExcelTRADING, $WLsheet, $sTkrloc) These statements are in a loop searching for a symbol and this statement, as compared to the VBA statement, is dramatically slower in execution. So I did not keep using this very long as it was too slow, so I'm not sure if I would get an execution error or not here. I've tried to search if there are conditions where the Excel file could be in-accessible, but found no answers. Just looking for some guidance on how to proceed to resolve this. Thanks Doug Link to comment Share on other sites More sharing options...
water Posted August 3, 2023 Share Posted August 3, 2023 Please provide more information about the error. You posted the source code lines in error but the "real" error message that follows is missing. 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...
robertocm Posted August 8, 2023 Share Posted August 8, 2023 (edited) Some ideas: Avoid "double dot" notation (see example below) Wait for Application.Ready Update AutoIt Minimize interaction with the sheet: read a whole range and then work with the data array Code example: expandcollapse popup#include <Excel.au3> #include <WinAPIFiles.au3> Global $sFilePath2 = @ScriptDir & "\Test.xlsx" Global $oMyError = ObjEvent("AutoIt.Error", "ErrFunc") ;Install a custom error handler Global $iEventError ; to be checked to know if com error occurs. Must be reset after handling. Global $Rows, $oAppl, $oWorkbook, $oSheet, $oRange $Rows = 2 ;water: force the Excel UDF to always start up a new instance by using: _Excel_Open(False, Default, Default, Default, True) $oAppl = _Excel_Open(True, False, False, Default, True) ;Global $oAppl = _Excel_Open() ;_Excel_Open(Default, Default, False, Default, Default) ;If @error Then Exit MsgBox(0, "Error", "Error _Excel_Open" & @CRLF & "@error = " & @error & ", @extended = " & @extended) ;https://www.autoitscript.com/forum/topic/185789-solved-excel_bookopen-without-wait/?do=findComment&comment=1334509 ;https://stackoverflow.com/questions/32513797/test-if-excel-has-finished-its-startup-shutdown-sequence While Not $oAppl.ready Sleep(1000) WEnd $oAppl.EnableEvents = False $oAppl.DisplayAlerts = False ;Global $oWorkbook = _Excel_BookOpen($oAppl, $sFilePath2, False, True) ;If @error Then Exit MsgBox(0, "Error", "Error _Excel_BookOpen: " & $sFilePath & @CRLF & "@error = " & @error & ", @extended = " & @extended) ;Create a new workbook with only 1 worksheet $oWorkbook = _Excel_BookNew($oAppl, 1) ;If @error Then Exit MsgBox(0, "Excel UDF: _Excel_BookNew Example 1", "Error creating new workbook." & @CRLF & "@error = " & @error & ", @extended = " & @extended) ;MsgBox(0, "Excel UDF: _Excel_BookNew Example 1", "Workbook has been created successfully with only 1 worksheets.") ;https://stackoverflow.com/questions/32513797/test-if-excel-has-finished-its-startup-shutdown-sequence While Not $oAppl.ready Sleep(1000) WEnd Sleep(3000) $oWorkbook.UpdateLinks = 2 ;xlUpdateLinksNever ;https://www.autoitscript.com/forum/topic/166043-close-the-entire-application-of-excel/?do=findComment&comment=1263179 ;water ;Seems Excel doesn't like double dot notation. "chaining" object references seems to be a problem. ;So I changed the code to only have single dot notation. Could you please test this version? ;https://www.add-in-express.com/creating-addins-blog/2013/11/05/release-excel-com-objects/ ;Global $oSheets = $oWorkbook.Sheets $oSheet = $oWorkbook.ActiveSheet ;Global $oSheet = $oWorkbook.Sheets("Main") ;MsgBox(0, "", $oSheet.Name) While Not $oAppl.ready Sleep(1000) WEnd ;http://www.siddharthrout.com/index.php/2018/02/10/find-last-row-and-last-column/ ;Find Last Row and Last Column. Siddharth Rout, Feb 2018 ;~ Global $LastRow = $oSheet.Cells(5000, 1).End(-4162).Row ;$UltimaFila = $oSheet.Cells(23, 1).End(-4121).Row ;MsgBox(0, "", $UltimaFila) ;$oWorkbook.Sheets.Item(2).Activate ;$Signal = $oSheet.Cells(21, 1).Value ;$oSheet.Range("I4").value = "ABC" ;$oSheet.Range("J25:K25").Value = 100 ;$oSheet.PrintOut ;~ Global $aResult = _Excel_RangeRead($oWorkbook, Default, $oSheet.Range("A9:E" & $LastRow), 1) ;If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead", "Error reading from workbook." & @CRLF & "@error = " & @error & ", @extended = " & @extended) Global $oPageSetup = $oSheet.PageSetup With $oPageSetup ;.PrintTitleRows = "$1:$1" .PrintTitleColumns = "" .PrintArea = "" .LeftHeader = "&D" .CenterHeader = "Example" ;.RightHeader = "&P of &N" ;.LeftFooter = "&F {&A}" .CenterFooter = "" .RightFooter = "" .LeftMargin = 28 .RightMargin = 28 .TopMargin = 28 .BottomMargin = 28 .HeaderMargin = 15 .FooterMargin = 15 .PrintHeadings = False .PrintGridlines = False .PrintComments = -4142 .CenterHorizontally = False .CenterVertically = False .Orientation = 1 .Draft = False .FirstPageNumber = -4105 .Order = 1 .BlackAndWhite = False .Zoom = False ;100 .FitToPagesWide = 1 .FitToPagesTall = 1 EndWith Global $aArray1D[2] = ["Factura", "EUR"] $oRange = $oSheet.Range("A1:B1") If $iEventError Then Consolewrite("+--> Error: $oSheet.Range('A1:B1')" & @CRLF) $iEventError = 0 ; Reset after displaying a COM Error occurred EndIf With $oRange .HorizontalAlignment = -4108 .value = $aArray1D ;.Font.Bold = True ;.AutoFilter EndWith While Not $oAppl.ready Sleep(1000) WEnd While Not $oAppl.ready Sleep(1000) WEnd ;$oAppl.ActiveWindow.DisplayZeros = False $oAppl.ActiveWindow.DisplayGridlines = False $oSheet.Columns("A:B").ColumnWidth = 10 ;$oSheet.Columns(3).ColumnWidth = 10 If $iEventError Then Consolewrite("+--> Error ColumnWidth" & @CRLF) $iEventError = 0 ; Reset after displaying a COM Error occurred EndIf ;$oSheet.Range("A2:B" & $Rows).HorizontalAlignment = -4108 ;$oSheet.Range("B2:B" & $Rows).NumberFormat = "dd/mm;@" ;$oSheet.Range("C2:C" & $Rows).NumberFormat = "#.##0,00" ;$oSheet.Range("H2:H" & $Rows).WrapText = True ;$oSheet.Range("H2:H" & $Rows).Font.Size = 8 ;$oSheet.Range("A2:M" & $Rows).VerticalAlignment = -4160 $oSheet.Range("A2:A" & $Rows).NumberFormat = "@" ;$oSheet.Cells(1, 1).Resize(1, 7).Font.Bold = True ;$oSheet.Cells(1, 1).Resize($Rows, 1).Font.Bold = True ;$oSheet.Cells(1, 1).Resize(1, 3).AutoFilter While Not $oAppl.ready Sleep(1000) WEnd ;water, Aug 2016 ;https://www.autoitscript.com/forum/topic/184041-excel-formatting-cellsborders/?do=findComment&comment=1321855 ;XlListObjectSourceType Enumeration - https://msdn.microsoft.com/en-us/library/ff820815(v=office.14).aspx Global $xlSrcRange = 1 With $oSheet .ListObjects.Add($xlSrcRange, .Range("A1:B" & $Rows), Default, $xlYes).Name = "TB_Datos" .ListObjects("TB_Datos").TableStyle = "TableStyleMedium6" .ListObjects("TB_Datos").ShowTotals = True EndWith _Excel_BookSaveAs($oWorkbook, $sFilePath2, $xlOpenXMLWorkbook, True) ;$xlOpenXMLWorkbook 51 ;$xlExcel8 56 ;https://stackoverflow.com/questions/32513797/test-if-excel-has-finished-its-startup-shutdown-sequence While Not $oAppl.ready Sleep(1000) WEnd _Excel_BookClose($oWorkbook, False) ;If @error Then Exit MsgBox(0, "Error", "Error _Excel_BookClose: " & $sFilePath & @CRLF & "@error = " & @error & ", @extended = " & @extended) While _WinAPI_FileInUse($sFilePath2) Sleep(1000) Wend While _WinAPI_FileInUse($sFilePath2) Sleep(1000) Wend Sleep(3000) $oAppl.EnableEvents = True $oAppl.DisplayAlerts = True _Excel_Close($oAppl, False, True) ;If @error Then Exit MsgBox(0, "Error", "Error _Excel_Close" & @CRLF & "@error = " & @error & ", @extended = " & @extended) Sleep(5000) ;deixar tempo para cerrar proceso ;Chequear si quedou cerrado Excel Global $aProcesses = ProcessList("Excel.exe") ;_ArrayDisplay($aProcesses) If $aProcesses[0][0] > 0 Then ;https://www.autoitscript.com/forum/topic/166043-close-the-entire-application-of-excel/?do=findComment&comment=1263191 ;@water, thanks for your help so far, at least we pinned down that it's not a UDF bug. :) ;For now I will use a crude workaround by closing the most recent Excel.exe instance: ProcessClose($aProcesses[$aProcesses[0][0]][1]) Sleep(500) ;just to allow some time for the process to definitely close (if it does close) EndIf ;This is a custom error handler Func ErrFunc() Local $HexNumber = Hex($oMyError.number, 8) ;~ MsgBox(0, "", "We intercepted a COM Error !" & @CRLF & _ ;~ "Number is: " & $HexNumber & @CRLF & _ ;~ "WinDescription is: " & $oMyError.windescription) ConsoleWrite("-> We intercepted a COM Error !" & @CRLF & _ "-> err.number is: " & @TAB & $HexNumber & @CRLF & _ "-> err.source: " & @TAB & $oMyError.source & @CRLF & _ "-> err.windescription: " & @TAB & $oMyError.windescription & _ "-> err.scriptline is: " & @TAB & $oMyError.scriptline & @CRLF) $iEventError = 1 ; Use to check when a COM Error occurs $sTxt = "Err Excel" Consolewrite($sTxt & @CRLF) ;_FileWriteLog($hFile, $sTxt) EndFunc ;==>ErrFunc Edited August 8, 2023 by robertocm Link to comment Share on other sites More sharing options...
dgendrud Posted August 12, 2023 Author Share Posted August 12, 2023 Thanks for the comments on this! This is not happening very often so I have not gotten back to working on it yet. I will try all suggestions from robertocm soon. 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