Jump to content

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


 Share

Recommended Posts

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.

 

#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 by NassauSky
Cleaning up verbiage
Link to comment
Share on other sites

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:

#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?

We ought not to misbehave, but we should look as though we could.

Link to comment
Share on other sites

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

 

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

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...