NassauSky Posted February 17, 2023 Posted February 17, 2023 (edited) 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 Posted February 17, 2023 Posted February 17, 2023 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? NassauSky 1 We ought not to misbehave, but we should look as though we could.
ajag Posted February 17, 2023 Posted February 17, 2023 Where do you close your Excel/Workbook after processing? Rule #1: Always do a backup Rule #2: Always do a backup (backup of rule #1)
water Posted February 18, 2023 Posted February 18, 2023 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
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