Resursive Excel Search

[Remo1075]

Hi everyone, I have a script that I'm using that when run on a pc creates a folder on a remote share with an Excel file under it. The Excel file contains serial numbers of all software on the computer, I need to write another sctipt that runs through the root folder and in to the subfolders, polls all Excels looking for say a cell that contains Adobe and the cell next to it contains the serial number. Get that information and write it out to a single Excel called adobe.xls. I am not an expert in scripting but think that it might be able to be done using an array? Any pointers, much appriciated.

[water]

The UDF will create a nice recursive list of Excel files for you.

Then loop through the files in the array and search for the info you need.

[Remo1075]

Thanks for the pointer..

I have started off with the code below but it seems to always return a value of 0 even though the cell has data in it.

#include <RecFileListToArray.au3>
#include <Excel.au3>
$sDir = @ScriptDir
;List all xls files to array.
$aFiles = _RecFileListToArray($sDir, "*.xls", 1, 1, 0, 2)
For $i = 1 To $aFiles[0]
$sCellValue = _ExcelReadCell($aFiles[$i], 1, 2)
MsgBox(0, "", "Cell Value is: " & @CRLF & $sCellValue, 2)
Next

[Melba23]

Remo1075,

Go and read the Help file for the _Excel* functions.

In this case it seems that you need to use _ExcelBookOpen first and then use the return value from that in _ExcelReadCell. And do not forget to use _ExcelBookClose either once you have the data from that file!

M23

[Remo1075]

Hi, thanks for your reply. I still get 0 return value in the msgbox when I add _ExcelBookOpen and _ExcelBookClose, because I'm dealing with possibly hundereds of excel sheets idealy I dont want the script to physically open an instance of Excel everytime which is what _ExcelBookOpen and _ExcelBookClose is doing. I am still facing an issue of not being able to read a cell value from an array of loaded excel sheets.

Thanks for posting..

[/i]
[i][i]#include <RecFileListToArray.au3>
#include <Excel.au3>[/i][/i]
[i][i]$sDir = @ScriptDir[/i][/i]
[i][i];List all xls files to array.
$aFiles = _RecFileListToArray($sDir, "*.xls", 1, 1, 0, 2)[/i][/i]
[i][i]For $i = 1 To $aFiles[0][/i][/i]
[i][i]_ExcelBookOpen($aFiles[$i])
Sleep(200)
$sCellValue = _ExcelReadCell($aFiles[$i], 2, 1)
_ExcelBookClose($aFiles[$i])[/i][/i]
[i][i]MsgBox(0, "", "Cell Value is: " & @CRLF & $sCellValue, 2)[/i][/i]
[i][i]Next[/i][/i]

[i]

[water]

Try this:

#include <RecFileListToArray.au3>
#include <Excel.au3>
$sDir = @ScriptDir
;List all xls files to array.
$aFiles = _RecFileListToArray($sDir, "*.xls", 1, 1, 0, 2)
For $i = 1 To $aFiles[0]
    $oExcel = _ExcelBookOpen($aFiles[$i])
    Sleep(200)
    $sCellValue = _ExcelReadCell($oExcel, 2, 1)
    _ExcelBookClose($oExcel)
    MsgBox(0, "", "Cell Value is: " & @CRLF & $sCellValue, 2)
Next

[Remo1075]

Thanks alot...Great Help