Jump to content

_excelreadsheettoarray read not enough


Go to solution Solved by water,

Recommended Posts

Yes it is...Error = 1  means that you are not passing an array to _ExcelReadSheetToArray.  Check what the value of $xlsServices is.

edit: The code is setup to read the spreadsheet from whatever directory the script is run out of.

Edited by kylomas

Forum Rules         Procedure for posting code

"I like pigs.  Dogs look up to us.  Cats look down on us.  Pigs treat us as equals."

- Sir Winston Churchill

Link to post
Share on other sites

Added some error checking to see what is failing...

#include <Excel.au3>
#include <Array.au3>
#include <MsgBoxConstants.au3>
#include <GUIConstantsEx.au3>

#AutoIt3Wrapper_Add_Constants=n

Local $xlsServices = @ScriptDir & '\warranty_services.xls'
Local $gui010 = GUICreate('', 500, 200)
Local $aSize = WinGetClientSize($gui010)
Local $readSN = GUICtrlCreateInput('', 20, 20, 400, 20)
Local $servicebox = GUICtrlCreateLabel('', 20, 50, 400, 250)
GUISetState()

While 1
    Switch GUIGetMsg()
        Case $GUI_EVENT_CLOSE
            Exit
        Case $readSN
            SearchForService(StringStripWS(GUICtrlRead($readSN), 3))
    EndSwitch
WEnd

Func SearchForService($str)
    $oxlsServices = _ExcelBookOpen($xlsServices, 0) ; Open xls file

    if $oxlsServices = 0 then
        ConsoleWrite('Error opening spreadsheet = ' & @error & @LF)
        Exit
    endif

    Local $XlsArray = _ExcelReadSheetToArray($oxlsServices, 8, 2, 0, 8) ;make an array from excel

    if @error <> 0 then
        ConsoleWrite('Error creating array = ' & @error & @LF)
        Exit
    endif

    local $snpos = _ArraySearch($XlsArray, $str) ; searching for SN
    switch @error
        case 1,2,3,4,7
            GUICtrlSetData($servicebox, "Parameter Error - See Help File for @error  = "& @error)
        case 6
            GUICtrlSetData($servicebox, "SN: " & $str & ' - no service found in the "Warranty_services.xls" file')
        case 0
            GUICtrlSetData($servicebox, "SN:" & $str & " has a service: " & @CRLF & _
                    $XlsArray[$snPos][3] & @CRLF & _
                    "PN: " & $XlsArray[$snPos][1] & @CRLF & _
                    "Warranty from: " & $XlsArray[$snPos][7] & @CRLF & _
                    "Warranty to: " & $XlsArray[$snPos][8] & @CRLF & _
                    "RF LOT NR: " & $XlsArray[$snPos][4] & @CRLF & _
                    "Project name: " & $XlsArray[$snPos][5] & @CRLF & _
                    "Contract Nr: " & $XlsArray[$snPos][6] & @CRLF)
    endswitch
    _ExcelBookClose($oxlsServices, 0, 0)
EndFunc   ;==>SearchForService
Edited by kylomas

Forum Rules         Procedure for posting code

"I like pigs.  Dogs look up to us.  Cats look down on us.  Pigs treat us as equals."

- Sir Winston Churchill

Link to post
Share on other sites

@error 3 means: Count parameter out of range

$oxlsServices is an object so comparing to 0 is not sensible.

Please try:

#include <Excel.au3>
#include <Array.au3>
#include <MsgBoxConstants.au3>
#include <GUIConstantsEx.au3>

#AutoIt3Wrapper_Add_Constants=n

Local $xlsServices = @ScriptDir & '\warranty_services.xls'
Local $gui010 = GUICreate('', 500, 200)
Local $aSize = WinGetClientSize($gui010)
Local $readSN = GUICtrlCreateInput('', 20, 20, 400, 20)
Local $servicebox = GUICtrlCreateLabel('', 20, 50, 400, 250)
GUISetState()

While 1
    Switch GUIGetMsg()
        Case $GUI_EVENT_CLOSE
            Exit
        Case $readSN
            SearchForService(StringStripWS(GUICtrlRead($readSN), 3))
    EndSwitch
WEnd

Func SearchForService($str)
    $oxlsServices = _ExcelBookOpen($xlsServices, 0) ; Open xls file
    if @error <> 0 then
        ConsoleWrite('Error opening spreadsheet = ' & @error & @LF)
        Exit
    endif

    Local $XlsArray = _ExcelReadSheetToArray($oxlsServices, 8, 2, 0, 8) ;make an array from excel
    if @error <> 0 then
        ConsoleWrite('Error creating array = ' & @error & @LF)
        Exit
    endif

    local $snpos = _ArraySearch($XlsArray, $str) ; searching for SN
    switch @error
        case 1,2,3,4,7
            GUICtrlSetData($servicebox, "Parameter Error - See Help File for @error  = "& @error)
        case 6
            GUICtrlSetData($servicebox, "SN: " & $str & ' - no service found in the "Warranty_services.xls" file')
        case 0
            GUICtrlSetData($servicebox, "SN:" & $str & " has a service: " & @CRLF & _
                    $XlsArray[$snPos][3] & @CRLF & _
                    "PN: " & $XlsArray[$snPos][1] & @CRLF & _
                    "Warranty from: " & $XlsArray[$snPos][7] & @CRLF & _
                    "Warranty to: " & $XlsArray[$snPos][8] & @CRLF & _
                    "RF LOT NR: " & $XlsArray[$snPos][4] & @CRLF & _
                    "Project name: " & $XlsArray[$snPos][5] & @CRLF & _
                    "Contract Nr: " & $XlsArray[$snPos][6] & @CRLF)
    endswitch
    _ExcelBookClose($oxlsServices, 0, 0)
EndFunc   ;==>SearchForService
Edited by water

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.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 (NEW 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 post
Share on other sites
  • Solution

There is something really strange with your workbook :huh:

I tried with the rewritten Excel UDF and this works:

#include <Excel Rewrite.au3>
#include <Array.au3>

Global $sWorkbook = "C:\temp\warranty_services.xlsx"
Global $oExcel = _Excel_Open()
If @error Then Exit ConsoleWrite('Error opening Excel = ' & @error & @LF)

Global $oWorkbook = _Excel_BookOpen($oExcel, $sWorkbook)
If @error Then Exit ConsoleWrite('Error opening Workbook = ' & @error & @LF)

Global $iRows = $oWorkbook.Activesheet.Usedrange.Rows.Count
Global $aArray = _Excel_RangeRead($oWorkbook, $oWorkbook.ActiveSheet, "B8:I" & $iRows, 1, True)
If @error Then Exit ConsoleWrite('Error reading Range = ' & @error & @LF)
_ArrayDisplay($aArray)

_Excel_Close($oWorkbook, False)
If @error Then Exit ConsoleWrite('Error closing Excel = ' & @error & @LF)

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.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 (NEW 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 post
Share on other sites

 

There is something really strange with your workbook :huh:

I tried with the rewritten Excel UDF and this works:

#include <Excel Rewrite.au3>
#include <Array.au3>

Global $sWorkbook = "C:\temp\warranty_services.xlsx"
Global $oExcel = _Excel_Open()
If @error Then Exit ConsoleWrite('Error opening Excel = ' & @error & @LF)

Global $oWorkbook = _Excel_BookOpen($oExcel, $sWorkbook)
If @error Then Exit ConsoleWrite('Error opening Workbook = ' & @error & @LF)

Global $iRows = $oWorkbook.Activesheet.Usedrange.Rows.Count
Global $aArray = _Excel_RangeRead($oWorkbook, $oWorkbook.ActiveSheet, "B8:I" & $iRows, 1, True)
If @error Then Exit ConsoleWrite('Error reading Range = ' & @error & @LF)
_ArrayDisplay($aArray)

_Excel_Close($oWorkbook, False)
If @error Then Exit ConsoleWrite('Error closing Excel = ' & @error & @LF)

Thank you water! This works for me too. But now there is another error:

Error closing Excel = 1 :D

and excel does not closing...

Link to post
Share on other sites

My bad :(

Should be:

_Excel_Close($oExcel, False)

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.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 (NEW 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 post
Share on other sites

I'm running AutoIt 3.3.10.2, Windows 7 (64 bit) and Office 2010 (32 bit).

Looks like the only difference is Office. Maybe the old version is less picky?

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.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 (NEW 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 post
Share on other sites

It's very strange but I think the problem is caused by the format of the Excel file. I was not able to deactivate the filters etc. which led me to use the rewrite of the UDF.

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.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 (NEW 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 post
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
  • Recently Browsing   0 members

    No registered users viewing this page.

×
×
  • Create New...