Jump to content

_excelreadsheettoarray read not enough


 Share

Go to solution Solved by water,

Recommended Posts

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

Link to comment
Share on other sites

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

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 comment
Share on other sites

@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 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 comment
Share on other sites

 

@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).

Link to comment
Share on other sites

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 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 comment
Share on other sites

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 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 comment
Share on other sites

@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

Link to comment
Share on other sites

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

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 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 comment
Share on other sites

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

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

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 comment
Share on other sites

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

Link to comment
Share on other sites

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 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 comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

 

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

Link to comment
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
 Share

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...