Jump to content

Random fatal errors reading an Excel spreadsheet file single cell value


Recommended Posts

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

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

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:

#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 by robertocm
Link to comment
Share on other sites

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 account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...