Jump to content
Sign in to follow this  
broliukaz

_excelreadsheettoarray read not enough

Recommended Posts

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? :(

Share this post


Link to post
Share on other sites
water

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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-12-03 - Version 1.4.11.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-10-31 - Version 1.3.4.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
broliukaz

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

@error = 1

@extended = 0

Share this post


Link to post
Share on other sites
water

@error = 1 means: Specified object does not exist

What do you get when you run:

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

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-12-03 - Version 1.4.11.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-10-31 - Version 1.3.4.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
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).

Share this post


Link to post
Share on other sites
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?


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-12-03 - Version 1.4.11.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-10-31 - Version 1.3.4.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
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?

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-12-03 - Version 1.4.11.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-10-31 - Version 1.3.4.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
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.


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

Share this post


Link to post
Share on other sites
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 by broliukaz

Share this post


Link to post
Share on other sites
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.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-12-03 - Version 1.4.11.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-10-31 - Version 1.3.4.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
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 by broliukaz

Share this post


Link to post
Share on other sites
water

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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-12-03 - Version 1.4.11.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-10-31 - Version 1.3.4.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
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)


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

Share this post


Link to post
Share on other sites
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.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-12-03 - Version 1.4.11.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-10-31 - Version 1.3.4.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
kylomas

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


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

Share this post


Link to post
Share on other sites
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 :D

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.

Share this post


Link to post
Share on other sites
kylomas

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

 

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

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

; 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


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

Share this post


Link to post
Share on other sites
kylomas

Streamlined your function a bit...

#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

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

Share this post


Link to post
Share on other sites
broliukaz

 

Streamlined your function a bit...

#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: post-83369-0-96877800-1392644654_thumb.p

Share this post


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
Sign in to follow this  

×