Sign in to follow this  
Followers 0
Valnurat

Reading Excel content

17 posts in this topic

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

Yours sincerely

Kenneth.

Share this post


Link to post
Share on other sites



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

 
#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

 

Share this post


Link to post
Share on other sites

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

Yours sincerely

Kenneth.

Share this post


Link to post
Share on other sites

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.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

Check for errors after each _Excel* function.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

Errorcode for all is:

1 - Specified object does not exist


Yours sincerely

Kenneth.

Share this post


Link to post
Share on other sites

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

The exampel file runs without any problems.


Yours sincerely

Kenneth.

Share this post


Link to post
Share on other sites

It looks like _ExcelBookOpen was not successful.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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

Yours sincerely

Kenneth.

Share this post


Link to post
Share on other sites

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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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?


Yours sincerely

Kenneth.

Share this post


Link to post
Share on other sites

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>

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

 

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.

post-62705-0-72050900-1369815005_thumb.j


Yours sincerely

Kenneth.

Share this post


Link to post
Share on other sites

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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#15 ·  Posted (edited)

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 by Valnurat

Yours sincerely

Kenneth.

Share this post


Link to post
Share on other sites

#16 ·  Posted (edited)

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 by water

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

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  
Followers 0