_Excel_RangeFind problems

[Valnurat 1]

Hi. 

I'm trying to find text in my Excel. I have download the Beta of Excel UDF and I'm using the _Excel_RangeFind, but I get an error at :

Local $aSalesResult = _Excel_RangeFind($oWorkbook, "*Sales company", Default, Default, $xlWhole)
 

the error is:

"C:UsersusernameDocumentsAppnameExcel Rewrite.au3" (607) : ==> Variable must be of type "Object".:

can someone advice me?

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

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


If @error Then
    MsgBox(4096, "", "No File(s) chosen")
Else
    Global $oWorkbook = _Excel_BookOpen($oAppl, $var)
    ;MsgBox(4096, "", "You chose " & $var)
    Local $aSalesResult = _Excel_RangeFind($oWorkbook, "*Sales company", Default, Default, $xlWhole)
    MsgBox(4096, "", "The Cell Value is: " & $aSalesResult & @CRLF & @error, 2)
    _Arraydisplay($aSalesResult, "Excel UDF: _Excel_RangeFind Example 4", -1, 0, "", "|", "Col|Sheet|Name|Cell|Value|Formula|Comment")
EndIf

[water 1,936]

Can you please insert "_Excel_ErrorNotify(2)" before calling _Excel_Open.

[Valnurat 1]

Can you please insert "_Excel_ErrorNotify(2)" before calling _Excel_Open.

[water 1,936]

If you use Alpha 4 version of the UDF then this message is OK because Excel isn't running when the script is started.

Do you get another message?

[Valnurat 1]

If you use Alpha 4 version of the UDF then this message is OK because Excel isn't running when the script is started.

Do you get another message?

 

This is all the messages I get.

[water 1,936]

Thanks or this long list of screenshots

Which version of Excel do you run?

Which bitness of Excel (32bit, 64bit)?

Do you run/compile the AutoIt script for 32 or 64 bit?

[Valnurat 1]

Thanks or this long list of screenshots

Which version of Excel do you run?

Which bitness of Excel (32bit, 64bit)?

Do you run/compile the AutoIt script for 32 or 64 bit?

 

No problem.

I use excel 2010 32 bit

I haven't compile it yet. I run it as a Beta run from SciTE.

My OS is Windows 7 x64 bit

[water 1,936]

Even when you run it from SciTE bitness is important. Please add "#AutoIt3Wrapper_UseX64=N" at the top of your script.

[water 1,936]

Forget my last post. I just noticed that @AutoItX64 = 0.

Will have to do further investigation ...

[water 1,936]

I just used the following script with the _Excel1.xls that comes with the UDF and it works fine.

I'm running Windows 7 64 bit, Office 2010 32 bit and Autoit 3.3.9.4

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

Local $Openmessage = "Please, open Excel template for One4Al."
Local $var = FileOpenDialog($Openmessage, @ScriptDir & "\", "Images (*.xlsx;*.xls)", 1)
If @error Then
    MsgBox(4096, "", "No File(s) chosen")
Else
    Global $oAppl = _Excel_Open()
    Global $oWorkbook = _Excel_BookOpen($oAppl, $var)
    Local $aSalesResult = _Excel_RangeFind($oWorkbook, "*Story*", Default, Default, $xlWhole)
    _Arraydisplay($aSalesResult, "Excel UDF: _Excel_RangeFind Example 4", -1, 0, "", "|", "Col|Sheet|Name|Cell|Value|Formula|Comment")
EndIf

[Valnurat 1]

Ok, but my file is *.xlsx

 

I just used the following script with the _Excel1.xls that comes with the UDF and it works fine.

I'm running Windows 7 64 bit, Office 2010 32 bit and Autoit 3.3.9.4

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

Local $Openmessage = "Please, open Excel template for One4Al."
Local $var = FileOpenDialog($Openmessage, @ScriptDir & "\", "Images (*.xlsx;*.xls)", 1)
If @error Then
    MsgBox(4096, "", "No File(s) chosen")
Else
    Global $oAppl = _Excel_Open()
    Global $oWorkbook = _Excel_BookOpen($oAppl, $var)
    Local $aSalesResult = _Excel_RangeFind($oWorkbook, "*Story*", Default, Default, $xlWhole)
    _Arraydisplay($aSalesResult, "Excel UDF: _Excel_RangeFind Example 4", -1, 0, "", "|", "Col|Sheet|Name|Cell|Value|Formula|Comment")
EndIf

 

I don't have any problems with this:

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

[water 1,936]

Which Alpha version of the Excel UDF do you use? Only the latest support keyword Default as parameter for the range.

[Valnurat 1]

Which Alpha version of the Excel UDF do you use? Only the latest support keyword Default as parameter for the range.

 

4

[water 1,936]

Ok, but my file is *.xlsx

I tested with xls and xlsx files and it was working just fine.

Will test with the specified range as soon as possible ...

[Valnurat 1]

 

I just used the following script with the _Excel1.xls that comes with the UDF and it works fine.

I'm running Windows 7 64 bit, Office 2010 32 bit and Autoit 3.3.9.4

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

Local $Openmessage = "Please, open Excel template for One4Al."
Local $var = FileOpenDialog($Openmessage, @ScriptDir & "\", "Images (*.xlsx;*.xls)", 1)
If @error Then
    MsgBox(4096, "", "No File(s) chosen")
Else
    Global $oAppl = _Excel_Open()
    Global $oWorkbook = _Excel_BookOpen($oAppl, $var)
    Local $aSalesResult = _Excel_RangeFind($oWorkbook, "*Story*", Default, Default, $xlWhole)
    _Arraydisplay($aSalesResult, "Excel UDF: _Excel_RangeFind Example 4", -1, 0, "", "|", "Col|Sheet|Name|Cell|Value|Formula|Comment")
EndIf

 

I have just run this and it is working with my *.xlsx.

Maybe it is because my search text looked like this: "*Sales company". Now I searched for "*Sales*" and the I didn't get any errors.

I got the attached screenshot.

Maybe you could tell me how I check if the highlighted, in the attach file, is true the $bSales = true?

[water 1,936]

"*Sales company" finds every cell with "Sales company" at the end. If the cell ends with "Sales company " (not the additional space) then it won't be found.

So "*Sales company*" is your best bet.

[Valnurat 1]

If I do this:

Local $aSalesResult = _Excel_RangeFind($oWorkbook, "*Sales company", Default, Default, $xlWhole)

I don't get the:

_Arraydisplay($aSalesResult, "Excel UDF: _Excel_RangeFind Example 4", -1, 0, "", "|", "Col|Sheet|Name|Cell|Value|Formula|Comment")

But if I do this:

Local $aSalesResult = _Excel_RangeFind($oWorkbook, "*Sales*", Default, Default, $xlWhole)

I get the screenshot posted in #15

Edited June 17, 2013 by Valnurat

[water 1,936]

Can you make sure that there isn't a space after "Sales Company"?

I tested and a cell with a space at the end isn't found.

[Valnurat 1]

Can you make sure that there isn't a space after "Sales Company"?

I tested and a cell with a space at the end isn't found.

 

There is no space after "Sales Company"?

I could send you the Excel file?

[water 1,936]

If you like you can send me your Excel file through private mail.