Parsing an excel file

emozley · Jan 23, 2007

Hi,

I have a spreadsheet that has two bits of information that always appear in columns A and B but the row can vary.

Basically I want to loop through the file until the contents of A<i>n</i>='Total Ed Mozley' and then display the contents of B<i>n</i>.

So far I've got the following but have no idea if I'm even on the right track! Any help much appreciated.

<%

Dim oWorkbook As Excel.Workbook
Dim oWorksheet As Excel.Worksheet

Set oWorkbook = oExcelApp.Workbooks.Open(dataPath)
Set oWorksheet = oExcelApp.Worksheets("MyWorksheet")

For i=1 to rowCount

If (oWorksheet.Cells.Item(i, 1).Value)="Total Ed Mozley" Then
Response.Write(oWorksheet.Cells.Item(i, 2).Value)
End If

Next

Set oWorksheet = Nothing
Set oWorkbook = Nothing
Set oExcelApp = Nothing

%>

emozley · Jan 23, 2007

This is my latest effort... it does work but the only problem now is that if you refresh too quickly you get an unspecified error. I think this is because the file is taking a while to close after it is accessed but am not sure. Any ideas?

<% Option Explicit %>
<html>
<body>
<%
Dim objConn, objRS, strSQL
Dim x, curValue

Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open "DRIVER={Microsoft Excel Driver (*.xls)}; IMEX=1; HDR=NO; "&_
"Excel 8.0; DBQ=" & Server.MapPath("excelfile.xls") & "; "
strSQL = "SELECT * FROM A1:B10000"
Set objRS=objConn.Execute(strSQL)

Do While Not objRS.EOF
If objRS.Fields(0).Value="Total Ed Mozley" Then
Response.Write(objRS.Fields(1).Value)
End If
objRS.MoveNext
Loop
objRS.Close

objConn.Close
Set objRS=Nothing
Set objConn=Nothing
%>
</body>
</html>

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Parsing an excel file

emozley

Technical User

emozley

Technical User

Similar threads

Part and Inventory Search

Sponsor