Reading excel workbook doesn't seem to work after the first read

[NassauSky]

Yep, that's an odd subject and you'll understand more after trying the code below.  Basically I'm loading a spreadsheet to an array variable during some changes to the sheet but the 2nd time I run the command it does not return an array.

 

expandcollapse popup

#include <Excel.au3>

$sFullRange = "A1:L100"
$sFileOpenDialogM = @ScriptDir & "\RefQuestions.xlsx"
WinMinimizeAll ( ); or Send("#m")

_Excel_GetSheetTotals()

Func _Excel_GetSheetTotals()

    ;OPEN MAIN TEMPLATE FILE
        $prog_excel = _Excel_Open()
        If $prog_excel == 0 Then Exit
        $oWorkbookM = _Excel_BookOpen($prog_excel, $sFileOpenDialogM) ;
        If $oWorkbookM == 0 Then Msgbox("","Error", "Unable to open the specified excel file") Exit EndIf

    ;READ FULL SHEET VALUES TO ARRAY
        Local $read = _Excel_RangeRead($oWorkbookM, $oWorkbookM.Activesheet, $sFullRange, 1)
        $iIndexM = FindTotalRow($read)

    ;REMOVE BLANK ROWS FROM MAIN FILE (THEN ADD BLANK ROW)
        _Excel_RangeDelete($oWorkbookM.Activesheet, "A3:L" & ($iIndexM))
        _Excel_RangeInsert($oWorkbookM.ActiveSheet, "3:3")

    ;RE-READ FULL SHEET VALUES TO ARRAY
        Local $read = _Excel_RangeRead($oWorkbookM, Default, "A1:L4", 1)
        ConsoleWrite("$read After deleting blank rows: " & $read & @CRLF)
        _ArrayDisplay($read,"After deleting blank rows")
        $iIndexM = FindTotalRow($read)
        MsgBox(0,"Notice","Application Doesn't Reach This Point: " & $iIndexM)

EndFunc

Func FindTotalRow($read)
        Local $iColtoSrch = 0
        Local $sSearch = "TOTALS"
        Global $iIndex = _ArraySearch($read, $sSearch, 0, 0, 0, 1, 1, $iColtoSrch)
        If @error Then
                MsgBox($MB_SYSTEMMODAL, "Not Found So Exiting", '"' & $sSearch & '" was not found on column ' & $iColtoSrch & '.*')
                Exit
        EndIf
    Return $iIndex
EndFunc

 

 

 

 

 

RefQuestions.xlsx

Edited February 17, 2023 by NassauSky
Cleaning up verbiage

[mistersquirrle]

I don't have Excel to try it out, but I would suggest that you run this with some additional logging, and see if there's some errors along the way:

expandcollapse popup

#include <Excel.au3>

$sFullRange = "A1:L100"
$sFileOpenDialogM = @ScriptDir & "\RefQuestions.xlsx"
;~ WinMinimizeAll()  ; or Send("#m")

_Excel_GetSheetTotals()

Func _Excel_GetSheetTotals()

    ;OPEN MAIN TEMPLATE FILE
    $prog_excel = _Excel_Open()
    If $prog_excel == 0 Or @error Then
        ConsoleWrite('Error opening excel: ' & @error & @CRLF)
        Exit
    EndIf
    $oWorkbookM = _Excel_BookOpen($prog_excel, $sFileOpenDialogM)     ;
    If $oWorkbookM == 0 Or @error Then
        ConsoleWrite('Unable to open the specified excel file: ' & @error & @CRLF)
;~      MsgBox("", "Error", "Unable to open the specified excel file")
        Exit
    EndIf

    ;READ FULL SHEET VALUES TO ARRAY
    Local $read = _Excel_RangeRead($oWorkbookM, $oWorkbookM.Activesheet, $sFullRange, 1)
    If @error Then
        ConsoleWrite('Unable to RangeRead (' & $sFullRange & '): ' & @error & @CRLF)
        Exit
    EndIf
    $iIndexM = FindTotalRow($read)

    ;REMOVE BLANK ROWS FROM MAIN FILE (THEN ADD BLANK ROW)
    _Excel_RangeDelete($oWorkbookM.Activesheet, "A3:L" & ($iIndexM))
    If @error Then
        ConsoleWrite('Unable to RangeDelete (' & "A3:L" & ($iIndexM) & '): ' & @error & @CRLF)
        Exit
    EndIf
    _Excel_RangeInsert($oWorkbookM.ActiveSheet, "3:3")
    If @error Then
        ConsoleWrite('Unable to RangeInsert (' & "3:3" & '): ' & @error & @CRLF)
        Exit
    EndIf

    ;RE-READ FULL SHEET VALUES TO ARRAY
    Local $read = _Excel_RangeRead($oWorkbookM, Default, "A1:L4", 1)
    If @error Then
        ConsoleWrite('Unable to RangeRead (' & "A1:L4" & '): ' & @error & @CRLF)
        Exit
    EndIf
    ConsoleWrite("$read After deleting blank rows: " & $read & @CRLF)
    _ArrayDisplay($read, "After deleting blank rows")
    $iIndexM = FindTotalRow($read)
    MsgBox(0, "Notice", "Application Doesn't Reach This Point: " & $iIndexM)

EndFunc   ;==>_Excel_GetSheetTotals

Func FindTotalRow($read)
    Local $iColtoSrch = 0
    Local $sSearch = "TOTALS"
    Global $iIndex = _ArraySearch($read, $sSearch, 0, 0, 0, 1, 1, $iColtoSrch)
    If @error Then
        MsgBox($MB_SYSTEMMODAL, "Not Found So Exiting", '"' & $sSearch & '" was not found on column ' & $iColtoSrch & '.*')
        Exit
    EndIf
    Return $iIndex
EndFunc   ;==>FindTotalRow

What's the output? Any errors? If so, looking up the error meanings in the help file, do you see why it's not working?

[ajag]

Where do you close your Excel/Workbook after processing?

 

[water]

On 2/17/2023 at 2:58 AM, mistersquirrle said:

If $prog_excel == 0 Or @error Then

When checking for errors first have a look at the help file to see how the function returns errors. This is done either by setting @error and @extrended or by a special return value. When @error & @extended is used DO NOT rely on the return value.

In the line above you check the return value AND @error AND you do a strict string compare. This does not make sense for nonstring values.

Simply use:

If @error Then