Random fatal errors reading an Excel spreadsheet file single cell value

[dgendrud]

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

 

[water]

Please provide more information about the error.
You posted the source code lines in error but the "real" error message that follows is missing.

[robertocm]

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

[dgendrud]

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.