_excelreadsheettoarray read not enough

[kylomas]

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 February 17, 2014 by kylomas

[kylomas]

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

expandcollapse popup

#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 February 17, 2014 by kylomas

[broliukaz]

The value of $xlsServices is script folder with xls file. I try to copy this value and paste to explorer, it opens xls file. So this value is ok.

the $oxlsServices value is empty (not 0), it could not open the file?

console write:

Error creating array = 3

[water]

@error 3 means: Count parameter out of range

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

Please try:

expandcollapse popup

#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 February 18, 2014 by water

[broliukaz]

The same error:

Error creating array = 3

[water]

There is something really strange with your workbook

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)

[broliukaz]

 

There is something really strange with your workbook

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

and excel does not closing...

[water]

My bad

Should be:

_Excel_Close($oExcel, False)

[broliukaz]

Thanks everyone!

[kylomas]

Any idea why the code that I posted works for me...??

Running Win7, Office/XP, AutoIT 3.3.10.2

[water]

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?

[kylomas]

Correction: My Win7 box is FUBAR...running on a Vista system.  It seems that the difference must be environmental.  I was suprised as hell when the OP said that it did not work.

[water]

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.