Jump to content
Sign in to follow this  
danish_draj

Open Excel files and display how many time this Word appears

Recommended Posts

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

Share this post


Link to post
Share on other sites

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

Edited by Subz

Share this post


Link to post
Share on other sites

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 by danish_draj
Forgot to show the Error

Share this post


Link to post
Share on other sites

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

Edited by Subz

Share this post


Link to post
Share on other sites

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")

5c4fe9ba09573_data2.PNG.fc99b108458633e132296320a7179372.PNG

 

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

HI subz

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

 

Edited by danish_draj

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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

It still says the file doesnt exist

 

subz xecel.PNG

Edited by danish_draj

Share this post


Link to post
Share on other sites

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!:blink:

So change the path:

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

To

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

 

Share this post


Link to post
Share on other sites

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

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  

  • Recently Browsing   0 members

    No registered users viewing this page.

×
×
  • Create New...