Reading Excel content

[Valnurat]

I'm trying to read the content of my Excel. I have merge 2 standard exampel with a small modification.

Exampel #1 is FileOpenDialog.au3

Exampel #2 is _ExcelReadSheetToArray.au3

The excel document is a usercreation form and alot of the cells is predefined by use of a combobox.

The outcome is that I only get a message telling me "Press OK to Save File and Exit".

Do I miss something here?

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

Local $Openmessage = "Please, open Excel template for One4Al."
Local $var = FileOpenDialog($Openmessage, @DesktopDir & "\", "Images (*.xlsx;*.xls)", 1)

If @error Then
    MsgBox(4096, "", "No File(s) chosen")
Else
    $oExcel = StringReplace($oExcel, "|", @CRLF)
    MsgBox(4096, "", "You chose " & $oExcel)
    Local $aArray = _ExcelReadSheetToArray($oExcel) ;Using Default Parameters
    _ArrayDisplay($aArray, "Array using Default Parameters")

    $aArray = _ExcelReadSheetToArray($oExcel, 2) ;Starting on the 2nd Row
    _ArrayDisplay($aArray, "Starting on the 2nd Row")

    $aArray = _ExcelReadSheetToArray($oExcel, 1, 2) ;Starting on the 2nd Column
    _ArrayDisplay($aArray, "Starting on the 2nd Column")

    $aArray = _ExcelReadSheetToArray($oExcel, 1, 1, 5) ;Read 5 Rows
    _ArrayDisplay($aArray, "Read 5 Rows")

    $aArray = _ExcelReadSheetToArray($oExcel, 1, 1, 0, 2) ;Read 2 Columns
    _ArrayDisplay($aArray, "Read 2 Columns")

    $aArray = _ExcelReadSheetToArray($oExcel, 2, 3, 4, 5) ;Starting on the 2nd Row, 3rd Column, Read 4 Rows and 5 Columns
    _ArrayDisplay($aArray, "Starting on the 2nd Row, 3rd Column, Read 4 Rows and 5 Columns")

    $aArray = _ExcelReadSheetToArray($oExcel, 1, 1, 0, 0, True) ;Using Default Parameters, except Shifting Column (True)
    _ArrayDisplay($aArray, "Array with Column shifting")

    MsgBox(0, "Exiting", "Press OK to Save File and Exit")
    _ExcelBookSaveAs($oExcel, @TempDir & "\Temp.xls", "xls", 0, 1) ; Now we save it into the temp directory; overwrite existing file if necessary
    _ExcelBookClose($oExcel) ; And finally we close out
EndIf

[Coder07]

i dont know why u used stringreplace thing but first of all you must open excel sheet:

 

expandcollapse popup

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


Local $Openmessage = "Please, open Excel template for One4Al."
Local $var = FileOpenDialog($Openmessage, @DesktopDir & "\", "Images (*.xlsx;*.xls)", 1)


If @error Then
    MsgBox(4096, "", "No File(s) chosen")
Else
$oExcel = _ExcelBookOpen($var)
    Local $aArray = _ExcelReadSheetToArray($oExcel) ;Using Default Parameters
    _ArrayDisplay($aArray, "Array using Default Parameters")


    $aArray = _ExcelReadSheetToArray($oExcel, 2) ;Starting on the 2nd Row
    _ArrayDisplay($aArray, "Starting on the 2nd Row")


    $aArray = _ExcelReadSheetToArray($oExcel, 1, 2) ;Starting on the 2nd Column
    _ArrayDisplay($aArray, "Starting on the 2nd Column")


    $aArray = _ExcelReadSheetToArray($oExcel, 1, 1, 5) ;Read 5 Rows
    _ArrayDisplay($aArray, "Read 5 Rows")


    $aArray = _ExcelReadSheetToArray($oExcel, 1, 1, 0, 2) ;Read 2 Columns
    _ArrayDisplay($aArray, "Read 2 Columns")


    $aArray = _ExcelReadSheetToArray($oExcel, 2, 3, 4, 5) ;Starting on the 2nd Row, 3rd Column, Read 4 Rows and 5 Columns
    _ArrayDisplay($aArray, "Starting on the 2nd Row, 3rd Column, Read 4 Rows and 5 Columns")


    $aArray = _ExcelReadSheetToArray($oExcel, 1, 1, 0, 0, True) ;Using Default Parameters, except Shifting Column (True)
    _ArrayDisplay($aArray, "Array with Column shifting")


EndIf

 

[Valnurat]

It is from the exampel. Have not modify it to the end.

But if I do this it excel opens and notthing happens.

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

Local $Openmessage = "Please, open Excel template for One4Al."
Local $oExcel = FileOpenDialog($Openmessage, @DesktopDir & "\", "Images (*.xlsx;*.xls)", 1)

If @error Then
    MsgBox(4096, "", "No File(s) chosen")
Else
    $oExcel = _ExcelBookOpen($oExcel)

    Local $aArray = _ExcelReadSheetToArray($oExcel) ;Using Default Parameters
    _ArrayDisplay($aArray, "Array using Default Parameters")

    $aArray = _ExcelReadSheetToArray($oExcel, 2) ;Starting on the 2nd Row
    _ArrayDisplay($aArray, "Starting on the 2nd Row")

    $aArray = _ExcelReadSheetToArray($oExcel, 1, 2) ;Starting on the 2nd Column
    _ArrayDisplay($aArray, "Starting on the 2nd Column")

    $aArray = _ExcelReadSheetToArray($oExcel, 1, 1, 5) ;Read 5 Rows
    _ArrayDisplay($aArray, "Read 5 Rows")

    $aArray = _ExcelReadSheetToArray($oExcel, 1, 1, 0, 2) ;Read 2 Columns
    _ArrayDisplay($aArray, "Read 2 Columns")

    $aArray = _ExcelReadSheetToArray($oExcel, 2, 3, 4, 5) ;Starting on the 2nd Row, 3rd Column, Read 4 Rows and 5 Columns
    _ArrayDisplay($aArray, "Starting on the 2nd Row, 3rd Column, Read 4 Rows and 5 Columns")

    $aArray = _ExcelReadSheetToArray($oExcel, 1, 1, 0, 0, True) ;Using Default Parameters, except Shifting Column (True)
    _ArrayDisplay($aArray, "Array with Column shifting")
EndIf

[water]

As an Info: The Excel UDF doesn't work well with xlsx files. If you run into problems you can try my new (alpha) version of a complete rewrite of the UDF.

[water]

Check for errors after each _Excel* function.

[Valnurat]

Errorcode for all is:

1 - Specified object does not exist

[Valnurat]

If I save the excel to *.xls I still have the problem.

The exampel file runs without any problems.

[water]

It looks like _ExcelBookOpen was not successful.

[Valnurat]

It seems that _ExcelReadCell works, but not _ExcelReadSheetToArray.

I'm trying to find a solution to read my excel from 5,6 to 25,43.

How can I do that?

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

Local $Openmessage = "Please, open Excel template for One4Al."
Local $sFilePath1 = FileOpenDialog($Openmessage, @DesktopDir & "\", "Images (*.xlsx;*.xls)", 1)
If @error Then
    MsgBox(4096, "", "No File(s) chosen")
Else
    Local $oExcel = _ExcelBookOpen($sFilePath1)
    Local $sCellValue = _ExcelReadCell($oExcel, 13, 5)
    MsgBox(0, "", "The Cell Value is: " & @CRLF & $sCellValue, 2)
    Local $aArray = _ExcelReadSheetToArray($oExcel) ;Using Default Parameters
    MsgBox(4096, "", @error)
EndIf

[water]

You could give my ExcelEX UDF (still an alpha version) a try. Function _Excel_RangeRead should work.

[Valnurat]

You could give my ExcelEX UDF (still an alpha version) a try. Function _Excel_RangeRead should work.

 

I have downloaded it, but how do I import it to my script?

[water]

Unzip the archive and place Excel Rewrite.au3 in the directory where your script is stored. Then inlcude it like any other UDF using

#include <ExcelRewrite.au3>

[Valnurat]

 

Unzip the archive and place Excel Rewrite.au3 in the directory where your script is stored. Then inlcude it like any other UDF using

#include <ExcelRewrite.au3>

Ahh, I get it.

I have save the Excel Rewrite.au3 file in this location:

C:Program Files (x86)AutoIt3Include

And I save all the _Excel_*

C:Program Files (x86)AutoIt3ExamplesHelpfile

I guess, if I want to find out how the _Excel_RangeRead works, I need to run the exampel file, right?

So I did that, but I get an error

I have attach the error.

[water]

The Excel Rewrite needs to be run with one of the 3.3.9.x beta versions of AutoIt.

[Valnurat]

Downloaded the beta version and the exampel works.

In the exampel 2 of the _Excel_RangeRead.au3 the cells from A1:E4 is being displayed, but how do I just get access to E4?

Local $aResult = _Excel_RangeRead($oAppl, $oWorkbook, Default, "A1:E4")

And what is the "default" parameter?

I need to run though all cells from start to end, because the excel doc I have is like this:

Col1          Col2          Col3          Col4          Ect
Row1                    User1         User2         User3         Ect
Row2      FirstName     James         Peter
Row3      LastName      Bond          Pan
Ect

So if Row2 contains data from Col2, then I do what I have to do.

Is this making any sense?

Edited May 29, 2013 by Valnurat

[water]

The parameter that is set to "Default" defines which worksheet of the workbook to process. Default means: process the active worksheet (the one displayed on the screen).

The functions support normal Excel range definitions (A1 or R1C1). Use "E4". But you get better performance when reading the whole sheet to an array and then processing the array.

Edited May 30, 2013 by water

[water]

The function returns a two dimenisonal array. Dimension 1 is the rows, Dimension 2 the columns. The index for both dimensions starts with 0.

Run the example script _Excer_RangeRead.au3 and you will see what I mean.