Jump to content

_ExcelReadSheetToArray doesn`t create an array?

Jochem

By Jochem,
in AutoIt General Help and Support

 Share

Recommended Posts

Jochem Adventurer

Jochem

Posted
Posted

I am looking allready 1 hour to the code below, but can`t find the error.

it doens`t displays the array......

#include <File.au3>
#include <Array.au3>
#include <excel.au3>

$sFilePathmac = @ScriptDir & "mac.xls"

_macadresses()

Func _macadresses()
    $oExcelmac = _ExcelBookOpen($sFilePathmac, 0)
        If @error = 1 Then
            MsgBox(0, "Error!", "Unable to Create the Excel Object")
            Exit
        ElseIf @error = 2 Then
            MsgBox(0, "Error!", "File does not exist!")
            Exit
        EndIf
    _ExcelSheetActivate($oExcelmac, "mac")
    $aMac = _ExcelReadSheetToArray($sFilePathmac,0,0,0,0)
    _ArrayDisplay($aMac, "macadresses")
    _ExcelBookClose($oExcelmac,0,0)
EndFunc  ;==>_macadresses
Link to comment
Share on other sites
PsaltyDS Universalist

PsaltyDS

Posted
Posted (edited)

I am looking allready 1 hour to the code below, but can`t find the error.

it doens`t displays the array...... 

#include <File.au3>
#include <Array.au3>
#include <excel.au3>

$sFilePathmac = @ScriptDir & "mac.xls"

_macadresses()

Func _macadresses()
    $oExcelmac = _ExcelBookOpen($sFilePathmac, 0)
        If @error = 1 Then
            MsgBox(0, "Error!", "Unable to Create the Excel Object")
            Exit
        ElseIf @error = 2 Then
            MsgBox(0, "Error!", "File does not exist!")
            Exit
        EndIf
    _ExcelSheetActivate($oExcelmac, "mac")
    $aMac = _ExcelReadSheetToArray($sFilePathmac,0,0,0,0)
    _ArrayDisplay($aMac, "macadresses")
    _ExcelBookClose($oExcelmac,0,0)
EndFunc;==>_macadresses
To start with, macros NEVER include a trailing backslash, so your file path should be: $sFilePathmac = @ScriptDir & "\mac.xls"

You should have detected that with basic error checking, like doing "If Not FileExists($sFilePathmac) Then ..."

:P

Edited by PsaltyDS
Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law
Link to comment
Share on other sites
Jochem Adventurer

Jochem

Posted
  • Author
Posted 

If @error = 1 Then
            MsgBox(0, "Error!", "Unable to Create the Excel Object")
            Exit
        ElseIf @error = 2 Then
            MsgBox(0, "Error!", "File does not exist!")
            Exit
        EndIf

I did :P

and i tried with or without slach but it still won't work

Link to comment
Share on other sites
PsaltyDS Universalist

PsaltyDS

Posted
Posted

when I add this within the function: 

MsgBox(0,"TEST",$aMac[0][1])

then the arraylog gives me this: Subscript used with non-Array variable.

Check your parameters for _ExcelReadSheetToArray(), the row and col numbers are supposed to be 1-based. If you want to read the entire sheet, then those can be left to default values: 
$aMac = _ExcelReadSheetToArray($sFilePathmac)

; Or...

    $aMac = _ExcelReadSheetToArray($sFilePathmac, 1, 1, 0, 0)

:P

Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law
Link to comment
Share on other sites
PsaltyDS Universalist

PsaltyDS

Posted
Posted

I tried that, but doens't seem to work too.

thanx anyway.

EXACTLY what happens when you run the demo script from the 3.3.0.0 help file for _ExcelReadSheetToArray()?

The phrase "doens't seem to work too" is not providing any useful information on the issue.

:P

Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law
Link to comment
Share on other sites
Jochem Adventurer

Jochem

Posted
  • Author
Posted

The _display array doesn't work, it won`t show up any array, I think because the _ExcelReadSheetToArray() isn`t creating an array.

The example creates a new excelbook with random numbers, but the arraydisplay doesn't show anything.

with a simple message box like above I get this "Subscript used with non-Array variable".

Link to comment
Share on other sites
PsaltyDS Universalist

PsaltyDS

Posted
Posted (edited)

The _display array doesn't work, it won`t show up any array, I think because the _ExcelReadSheetToArray() isn`t creating an array.

The example creates a new excelbook with random numbers, but the arraydisplay doesn't show anything.

with a simple message box like above I get this "Subscript used with non-Array variable".

Update your error checking to include displaying the @error and @extended values. From the help file under _ExcelReadSheetToArray():

Return Value

Success: Returns a 2D array with the specified cell contents by [$row][$col]

Failure: Returns 0 and sets @error on errors:

@error=1: Specified object does not exist

@error=2: Start parameter out of range

@extended=0: Row out of range

@extended=1: Column out of range

@error=3: Count parameter out of range

@extended=0: Row count out of range

@extended=1: Column count out of range

:P

P.S. What version of Excel are you running?

Edited by PsaltyDS
Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law
Link to comment
Share on other sites
Jochem Adventurer

Jochem

Posted
  • Author
Posted 

$sFilePathmac = @ScriptDir & "\mac.xls"
If Not FileExists($sFilePathmac) Then
    MsgBox(0, "Error!", "file does not excist")
    Exit
EndIf
    
_macadresses()

Func _macadresses()
    $oExcelmac = _ExcelBookOpen($sFilePathmac, 0)
        If @error = 1 Then
            MsgBox(0, "Error!", "Unable to Create the Excel Object")
            Exit
        ElseIf @error = 2 Then
            MsgBox(0, "Error!", "File does not exist!")
            Exit
        EndIf
    _ExcelSheetActivate($oExcelmac, "mac")
    $aMac = _ExcelReadSheetToArray($sFilePathmac)
    _ArrayDisplay($aMac, "macadresses")
        If @error = 1 Then
            MsgBox(0, "Error!", "Specified object does not exist")
            Exit
        ElseIf @error = 2 Then
            MsgBox(0, "Error!", "Start parameter out of range")
            Exit
        ElseIf @extended=0 Then
            MsgBox(0, "Error!", "Row out of range")
            Exit
        ElseIf @extended=1 Then
            MsgBox(0, "Error!", "Column out of range")
            Exit
        ElseIf @error=3 Then
            MsgBox(0, "Error!", "Count parameter out of range")
            Exit
        ElseIf @extended=0 Then
            MsgBox(0, "Error!", "Row count out of range")
            Exit
        ElseIf @extended=1 Then
            MsgBox(0, "Error!", "Column count out of range")
            Exit
        EndIf
    _ExcelBookClose($oExcelmac,0,0)
EndFunc  ;==>_macadresses

now I get the following error: Specified object does not exist (@error = 1). But it excists!

I am using excel 2007, but savved it as excel 2003.

Link to comment
Share on other sites
PsaltyDS Universalist

PsaltyDS

Posted
Posted (edited)

CODE
$sFilePathmac = @ScriptDir & "\mac.xls"

If Not FileExists($sFilePathmac) Then

MsgBox(0, "Error!", "file does not excist")

Exit

EndIf

_macadresses()

Func _macadresses()

$oExcelmac = _ExcelBookOpen($sFilePathmac, 0)

If @error = 1 Then

MsgBox(0, "Error!", "Unable to Create the Excel Object")

Exit

ElseIf @error = 2 Then

MsgBox(0, "Error!", "File does not exist!")

Exit

EndIf

_ExcelSheetActivate($oExcelmac, "mac")

$aMac = _ExcelReadSheetToArray($sFilePathmac)

_ArrayDisplay($aMac, "macadresses")

If @error = 1 Then

MsgBox(0, "Error!", "Specified object does not exist")

Exit

ElseIf @error = 2 Then

MsgBox(0, "Error!", "Start parameter out of range")

Exit

ElseIf @extended=0 Then

MsgBox(0, "Error!", "Row out of range")

Exit

ElseIf @extended=1 Then

MsgBox(0, "Error!", "Column out of range")

Exit

ElseIf @error=3 Then

MsgBox(0, "Error!", "Count parameter out of range")

Exit

ElseIf @extended=0 Then

MsgBox(0, "Error!", "Row count out of range")

Exit

ElseIf @extended=1 Then

MsgBox(0, "Error!", "Column count out of range")

Exit

EndIf

_ExcelBookClose($oExcelmac,0,0)

EndFunc ;==>_macadresses

now I get the following error: Specified object does not exist (@error = 1). But it excists!

I am using excel 2007, but savved it as excel 2003.

OK, now I feel stupid for missing the obvious! :P

In your code, you did not pass the excel object to the function in the first parameter. It was like that earlier in the topic and I missed it (even quoted it without correcting it).

$aMac = _ExcelReadSheetToArray($oExcelmac)

The function does not pull the data from the file. It pulls from the $oExcel.ActiveSheet object passed to it.

This, however, does not explain how the example script fails. No matter how far off your script was, the example script in the help file works. Yet you insist it fails for you.

:unsure:

P.S. Run this short version of the example script, which will display any errors received: 

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

Global $oExcel = _ExcelBookNew();Create new book, make it visible
Global $iErrSav = @error
Global $iExtSav = @extended
If Not IsObj($oExcel) Then
    MsgBox(16, "Error", "Object creation failed:  @error = " & $iErrSav & ", @extended = " & $iExtSav)
EndIf

; We can fill-up some cells using a simple loop and random Numbers
For $y = 1 To 10;Start on Column 1
    For $x = 1 To 15
        _ExcelWriteCell($oExcel, Round(Random(1000, 10000), 0), $x, $y);Some random numbers to file
    Next
Next

Global $aArray = _ExcelReadSheetToArray($oExcel);Using Default Parameters
$iErrSav = @error
$iExtSav = @extended
If Not IsArray($aArray) Then
    MsgBox(16, "Error", "Function failed:  @error = " & $iErrSav & ", @extended = " & $iExtSav)
EndIf

_ArrayDisplay($aArray, "Array using Default Parameters")

:D

Edited by PsaltyDS
Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law
Link to comment
Share on other sites
99ojo Universalist

99ojo

Posted
Posted (edited)

Hi,

I'm having the same problems like Jochem. I'm using MS Office 2003 SP3 german language, AutoIT 3.3.0.0. The example script fills excel sheet, but doesn't display any _arraydisplay function call. Only the save and exit msgbox appears.

Calling PsaltyDS's short code code displays error: @error = 2 and @extended = 0.

Function _ExcelReadArray works as expected.

;-((

Stefan

Edited by 99ojo
Link to comment
Share on other sites
Jochem Adventurer

Jochem

Posted
  • Author
Posted (edited)

Yess that's it! :P

Thanx

except

with your example file I get "Function failed: @error = 1 , @extended = 2".

and now I get "Start parameter out of range"

(even with $aMac = _ExcelReadSheetToArray($oExcelmac,1,1))

Edited by Jochem
Link to comment
Share on other sites
PsaltyDS Universalist

PsaltyDS

Posted
Posted

Hi,

I'm having the same problems like Jochem. I'm using MS Office 2003 SP3 german language, AutoIT 3.3.0.0. The example script fills excel sheet, but doesn't display any _arraydisplay function call. Only the save and exit msgbox appears.

Calling PsaltyDS's short code code displays error: @error = 2 and @extended = 0.

Function _ExcelReadArray works as expected.

;-((

Stefan

BugTrac #850: _ExcelReadSheetToArray doesn't work with German Excel 2003

This bug was reported and then fixed a long time ago in the original ExcelCOM_UDF, but the fix got lost moving it to the included Excel.au3. The fix will be in the next Beta (3.3.1.0 maybe coming out soon). You can also make the change shown in the bug report to your copy of Excel.au3.

Yess that's it! :unsure:

Thanx

except

with your example file I get "Function failed: @error = 1 , @extended = 2".

and now I get "Start parameter out of range"

(even with $aMac = _ExcelReadSheetToArray($oExcelmac,1,1))

Are you using a non-English version? If so, the above bug fix may apply.

:P

Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law
Link to comment
Share on other sites
PsaltyDS Universalist

PsaltyDS

Posted
Posted

yes I have an dutch excel 2007.

Did you try the bug fix above? I believe German and Dutch were the specific two non-English tests we got on the original patch.

:P

Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law
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
Go to topic listing

  • Recently Browsing   0 members

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