Jump to content

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
Link to comment
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
Link to comment
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.

Link to comment
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
Link to comment
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.

Link to comment
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
 Share

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...