Searching an Excel file

[Mighty]

Hi Folks,

I have an ASP page which opens an Excel document in the background and substitutes in data read from a DB depending on options selected by the user and then prints out the Excel file.

When substituting in the data, I basically loop through the rows in the Excel file adding in data until I hit a certain text string and then I stop.

So what I need to do is when I open the Excel document, I need to check to make sure the string exists before I continue. So I found the following code in the VBScript forum:

Set c = objXL.Worksheets(1).Columns("A:A").Find("aArray(0)")
If c Is Nothing Then
  MsgBox "Not Found"
End If

However, when I use this in my ASP program nothing happens and the program seems to sit in an infinite loop or something.

Any ideas?

Mighty

 

[Mighty]

I should have pointed out that it works fine when the string does exists. The problem only arises when the string doesn't exist.

Mighty

 

[Abraxus]

One of the things I've found most fascinating in my journies through ASP is the possibility to open an Excel file as a recordset. This is based on some code that I picked up a while back when searching for ASP/Excel functions, modified a bit for what I think you're looking to do:

Dim rs,sql,i,boolFound
sql = "SELECT * FROM [Sheet1$];"
[COLOR=green]'Where Sheet1 is the name of the sheet you're trying to open[/color]
boolFound = false
if runsql(sql,rs) then
    Do While Not rs.EOF
        Response.Write "<tr>" & chr(13)
        For I = 0 To rs.Fields.Count - 1
            If instr(rs.Fields.Item(I).Value,aArray(0))>0 Then boolFound=true End If
        Next
        Response.Write "</tr>" & chr(13)
        rs.MoveNext
    Loop
	rs.Close
end if
Set rs = Nothing

[COLOR=green]'If boolFound is true then you're ok![/color]

Function runSQL(SQL,rs)
	on error resume next
	Dim myRs
	Set myRs = CreateObject("ADODB.Recordset")
	myRs.Open SQL,"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("myfile.xls") & ";Extended Properties=""Excel 8.0;IMEX=1;""", 1, 3
	set rs = myRs
	if err then
		runSQL = false
		response.write err.description
	else
		runSQL = true
	end if
End Function

Hope this helps!