Open Excel files and display how many time this Word appears

[danish_draj]

Hi everybody,

I am trying to open a number of Excel files inside a folder and then search for a string inside every file. If the word is found the result needs to be display in a Messagebox to show that how many time this word had appear.

Thank you in advance

Edited January 29, 2019 by danish_draj

[Subz]

Look at the _FileListToArrayRec and _Excel_RangeFind for searching and returning the number of times a word is found.

Edited January 29, 2019 by Subz

[danish_draj]

Hi Subz

This is what i've done so far with Excel_rangeFind coding
but i keep getting this error
can you look it up for me?

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

; Create application object and open an example workbook
;Local $oExcel = _Excel_Open("dsefesfs.xls")
;If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeFind Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)

Local $oWorkbook =_Excel_BookOpen(0, @DocumentsCommonDir & "\dsefesfs.xls")

If @error Then
    MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeFind Example", "Error opening workbook '" & @DocumentsCommonDir & "\dsefesfs.xls'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    ;_Excel_Close($oExcel)
    Exit
EndIf


Local $aResult = _Excel_RangeFind($oWorkbook, "IR Download")
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeFind Example 3", "Error searching the range." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeFind Example 3", "Find all occurrences of string 'IR Download' in the comments." & @CRLF & "Data successfully searched.")
_ArrayDisplay($aResult, "Excel UDF: _Excel_RangeFind Example 3", "", 0, "|", "Sheet|Name|Cell|Value|Formula|Comment")

1 hour ago, Subz said:

Look at the _FileListToArrayRec and _Excel_RangeFind for searching and returning the number of times a word is found.

 

Edited January 29, 2019 by danish_draj
Forgot to show the Error

[danish_draj]

This is the Error
really am Sorry forget to show the error

 

[Subz]

Why have you commented the top 4 lines and also removed the $oExcel from the _Excel_BookOpen function?

Edited January 29, 2019 by Subz

[danish_draj]

Hi Sub

I commented the top 4 is ignore it during compiling the program.

i Notice what u said abt remove the $oExcel. So i did it back now Instead of getting 1 error i got 2 error

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

; Create application object and open an example workbook
Local $oExcel = _Excel_Open()
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeFind Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
Local $oWorkbook = _Excel_BookOpen(_Excel_Open(), @DocumentsCommonDir & "\dsefesfs.xls")
If @error Then
    MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeFind Example", "Error opening workbook " & @DocumentsCommonDir & "\dsefesfs.xls'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    ;_Excel_Close($oExcel)
    Exit
EndIf

; Find all occurrences of string "test" in the comments
Local $aResult = _Excel_RangeFind($oWorkbook, "IR Download", Default, $xlComments)
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeFind Example 3", "Error searching the range." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeFind Example 3", "Find all occurrences of string 'IR Download' in the comments." & @CRLF & "Data successfully searched.")
_ArrayDisplay($aResult, "Excel UDF: _Excel_RangeFind Example 3", "", 0, "|", "Sheet|Name|Cell|Value|Formula|Comment")

 

However my Excel application is open everytime this error is showing.

[Subz]

Is the document in @CommonFilesDir or @MyDocumentsDir?

Also the following:

Local $oWorkbook = _Excel_BookOpen(_Excel_Open(), @DocumentsCommonDir & "\dsefesfs.xls")

should be

#include <Array.au3>
#include <Excel.au3>
Local $sWorkbook = "C:\Documents\dsefesfs.xls"
If FileExists($sWorkbook) = 0 Then Exit MsgBox(4096, "Error", $sWorkbook & " - does not exist.")
Local $oExcel = _Excel_Open()
    If @error Then Exit
Local $oWorkbook =_Excel_BookOpen($oExcel, $sWorkbook)
    If @error Then Exit
Local $aResult = _Excel_RangeFind($oWorkbook, "IR Download")
    If @error Then Exit
_ArrayDisplay($aResult)
If IsArray($aResult) Then MsgBox(4096, "Results", "Number of instances found: " & UBound($aResult) - 1)

 

Edited January 29, 2019 by Subz

[danish_draj]

HI subz

I've take your coding and try it out
there's no error this time instead, it doesnt come up anything

 

Edited January 29, 2019 by danish_draj

[Subz]

Please post your code, see the example I posted above for an example of how it should look

[danish_draj]

Hi Subz 

the coding u posted
Shows that my file doesnt exist
 

[Subz]

Updated and tested the code above, I accidentally just copied your code and changed one line without realizing you had modified other lines which were incorrect.

[danish_draj]

I wasnt aware of that
my apology to u

[Subz]

Is the document in C:\Documents\? or C:\Users\<UserName>\Documents?  Just change the $sWorkbook value "C:\Documents\dsefesfs.xls" to the correct location, I had a feeling that C:\Documents wasn't the path which is why I placed a file check before running the remaining code.

[danish_draj]

I copy Paste that directory of the file, which can be found in "properties"

It still says the file doesnt exist

 

Edited January 29, 2019 by danish_draj

[Subz]

Well the answer is fairly obvious isn't it?  When I asked you where the document was and you said C:\Documents not C:\Users\User\Documents!

So change the path:

Local $sWorkbook = "C:\Documents\dsefesfs.xls"

To

Local $sWorkbook = "C:\Users\User\Documents\dsefesfs.xls"

 

[danish_draj]

hi Subz
i did try like wat u said
it works
thx alot man!!
Glad to know someone in the internet actually helps you
Internet Wasnt a  bad place after all
<3

[Subz]

No problem, I'd suggest reading the Autoit help file thoroughly, it has a lot of information and useful examples.