_excelreadsheettoarray read not enough

[broliukaz]

Hi,

maybe someone can help me?

I use this: 

$XlsArray = _ExcelReadSheetToArray($oxlsServices,8,2,0,8)
_ArrayDisplay($XlsArray, "Array")

to start read from 8 row, 2nd column, and read all rows till 8 column. 

But this is not working at all, the array is empty at all. If I changed to:

$XlsArray = _ExcelReadSheetToArray($oxlsServices,8,2,0,7)
_ArrayDisplay($XlsArray, "Array")

everything works fine, but there is no last column in the array. Excel file has 8 columns, starting from "B" (A column is empty).

what I'm doing wrong?

[water]

What's the value of @error and @extended after you've called the function?

[broliukaz]

What's the value of @error and @extended after you've called the function?

@error = 1

@extended = 0

[water]

@error = 1 means: Specified object does not exist

What do you get when you run:

$XlsArray = _ExcelReadSheetToArray($oxlsServices,8,2)
_ArrayDisplay($XlsArray, "Array")

[broliukaz]

 

@error = 1 means: Specified object does not exist

What do you get when you run:

$XlsArray = _ExcelReadSheetToArray($oxlsServices,8,2)
_ArrayDisplay($XlsArray, "Array")

 

I get the array with 7 columns, there is no last, 8 column. that's why I'm trying to use:

$XlsArray = _ExcelReadSheetToArray($oxlsServices,8,2,0,8)

If I add one more column and use:

$XlsArray = _ExcelReadSheetToArray($oxlsServices,8,2)

then I see the 8th column, but not the 9th (the new column).

[water]

You mean you opened the workbook and added a 9th column and when you reed the worksheet you don't get the 9th column?

[water]

I just tried and get @error = 3 (Count parameter out of range), @extended = 1 (Column count out of range).

A few questions:

• Which version of AutoIt do you run?
• Which version of Excel do you run?
• The format of the workbook is XLS or XLSX?

[kylomas]

@water - I just ran his parms against the data generated by the help file example and it works perfectly.

@broliukaz - Can you provide an example of the spreadsheet?  Also, answer water's questions.

[broliukaz]

You mean you opened the workbook and added a 9th column and when you reed the worksheet you don't get the 9th column?

Yes, exactly. But I have get the 8th column, which I have don't get before. It seems like it reads columns  like: "columns = columns - 1"

I'm using:

AutiIT version: 3.3.10.2

Excel 2013 (Windows 7 32bit)

file format is *.xls

the file sample:

https://dl.dropboxusercontent.com/u/13615378/Warranty_services.xls

Edited February 17, 2014 by broliukaz

[water]

Unfortunately Dropbox is locked here. Can you please post the file here?

The code of your script - or at least a reproducer - would be fine.

[broliukaz]

Oh, I'm sorry!

Is there any way to upload a file or picture here?

                                                                 

the function:

Func SearchForService()
         Local $oxlsServices = _ExcelBookOpen($xlsServices, $fVisible) ; Open xls file
         Sleep(100)
         Local $XlsArray = _ExcelReadSheetToArray($oxlsServices,8,2,0,8) ;make an array from excel
                 _ArrayDisplay($XlsArray, "Array")     ; display the array
         $readSN = String($readSN)
         $readSN = StringStripWS($readSN,8) ; remove spaces
         Local $snPos = _ArraySearch($XlsArray, $readSN,0,0,0,0,1) ; searching for SN

         If $snPos < 0 Then
            Local $ArrayError
            If @error = 1  Then  $ArrayError = "Array is not an array"
            If @error = 2  Then  $ArrayError = "Array is not a 1 or 2 dimensional array"
            If @error = 3  Then  $ArrayError = "Array is empty"
            If @error = 4  Then  $ArrayError = "$iStart is greater than $iEnd"
            If @error = 6  Then  $ArrayError = "Value was not found in array"
            If @error = 7  Then  $ArrayError = "Array has too many dimensions"
;~          MsgBox(0, "Not Found", $readSN & ' was not found.       $snPos: ' & $snPos & " Error: " & $ArrayError)
            GUICtrlSetData($ServiceBox,"SN: " & $readSN & ' - no service found in the "Warranty_services.xls" file')
         Else
            GUICtrlSetData($ServiceBox,"SN:"& $readSN & " 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)
         EndIf
         _ExcelBookClose($oxlsServices,0,0)
         $oxlsServices.Application.Quit
         $oxlsServices = 0
         FileClose($xlsServices)
   EndFunc

Edited February 17, 2014 by broliukaz

[water]

Sorry, but can't you upload the xls file? Click on Full Editor / Attachments.

[kylomas]

broliukaz,

Opened your spreadsheet and added a 9TH column then changed the parms to '($oExcel,8,2,0,9)' and it is returning everything starting from row 8 column 2 through the end of data (including the column I just added)

[water]

May I suggest to have a look at my rewrite of the Excel UDF (download link can be found in my signature)?

It will replace the Excel UDF in the next beta version of AutoIt.

[kylomas]

How is $readSN populated? Can you show enough code to get this to run?

[broliukaz]

Thakn you! Here is the file Warranty_services.xls

[broliukaz]

How is $readSN populated? Can you show enough code to get this to run?

This small program is near 500 lines, and more few files...  I think, you don't want to read him all

the $readSN is entered in inputbox and its working correctly. The search is working too, the only problem is the array with -1 column I dont need to search in the last column, but I need to display it, when found the $readSN number.

[kylomas]

I think, you don't want to read him all

 

Yes, it is a bit early here (6:30AM)

This is working for me (changed the code somewhat)...

expandcollapse popup

; After opening a workbook and returning its object identifier,
; Fill some cells and Read the Values into an Array, using various paramaters.

#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
         Sleep(100)
         Local $XlsArray = _ExcelReadSheetToArray($oxlsServices,8,2,0,8) ;make an array from excel
         Local $snPos = _ArraySearch($XlsArray, $str) ; searching for SN
         If $snPos < 0 Then
            Local $ArrayError
            If @error = 1  Then  $ArrayError = "Array is not an array"
            If @error = 2  Then  $ArrayError = "Array is not a 1 or 2 dimensional array"
            If @error = 3  Then  $ArrayError = "Array is empty"
            If @error = 4  Then  $ArrayError = "$iStart is greater than $iEnd"
            If @error = 6  Then  $ArrayError = "Value was not found in array"
            If @error = 7  Then  $ArrayError = "Array has too many dimensions"
;~          MsgBox(0, "Not Found", $readSN & ' was not found.       $snPos: ' & $snPos & " Error: " & $ArrayError)
            GUICtrlSetData($ServiceBox,"SN: " & $str & ' - no service found in the "Warranty_services.xls" file')
         Else
            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)
         EndIf
         _ExcelBookClose($oxlsServices,0,0)
   EndFunc

kylomas

[kylomas]

Streamlined your function a bit...

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
    Local $XlsArray = _ExcelReadSheetToArray($oxlsServices, 8, 2, 0, 8) ;make an array from excel
    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

[broliukaz]

 

Streamlined your function a bit...

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
    Local $XlsArray = _ExcelReadSheetToArray($oxlsServices, 8, 2, 0, 8) ;make an array from excel
    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

 

I try both of your scripts (copy - paste to new file, not my program) , but both shows the same error...  

Is this really working for you?

I added the screenshot: