<% ' Selected constants from adovbs.inc Const adOpenStatic = 3 Const adLockPessimistic = 2 Dim cnnExcel Dim rstExcel Dim I Dim iCols Group = Request.QueryString("Group") ' This is all standard ADO except for the connection string. ' You can also use a DSN instead, but so it'll run out of the ' box on your machine I'm using the string instead. Set cnnExcel = Server.CreateObject("ADODB.Connection") cnnExcel.Open "DBQ=" & Server.MapPath("members.xls") & ";" & _ "DRIVER={Microsoft Excel Driver (*.xls)};" ' Same as any other data source. ' FYI: TestData is my named range in the Excel file Set rstExcel = Server.CreateObject("ADODB.Recordset") If Group = "GroupA" Then rstExcel.Open "SELECT * FROM GroupA;", cnnExcel, _ adOpenStatic, adLockPessimistic Category = "A Category" End If If Group = "GroupA_MS" Then rstExcel.Open "SELECT * FROM GroupA_MS;", cnnExcel, _ adOpenStatic, adLockPessimistic Category = "A Category with Monitoring Stopped" End If If Group = "GroupB" Then rstExcel.Open "SELECT * FROM GroupB;", cnnExcel, _ adOpenStatic, adLockPessimistic Category = "B Category" End If If Group = "GroupC" Then rstExcel.Open "SELECT * FROM GroupC;", cnnExcel, _ adOpenStatic, adLockPessimistic Category = "C Category" End If If Group = "Provisional" Then rstExcel.Open "SELECT * FROM Provisional;", cnnExcel, _ adOpenStatic, adLockPessimistic Category = "Provisional" End If If Group = "Pro_UP" Then rstExcel.Open "SELECT * FROM Pro_UP;", cnnExcel, _ adOpenStatic, adLockPessimistic Category = "Under Probation Provisional" End If If Group = "Pro_PB" Then rstExcel.Open "SELECT * FROM Pro_PB;", cnnExcel, _ adOpenStatic, adLockPessimistic Category = "Performing Badly Provisional" End If If Group = "Pro_MS" Then rstExcel.Open "SELECT * FROM Pro_MS;", cnnExcel, _ adOpenStatic, adLockPessimistic Category = "Monitoring Stopped Provisional" End If If Group = "Striken" Then rstExcel.Open "SELECT * FROM Striken;", cnnExcel, _ adOpenStatic, adLockPessimistic Category = "Striken Out" End If If Group = "StrikenViolations" Then rstExcel.Open "SELECT * FROM StrikenViolations;", cnnExcel, _ adOpenStatic, adLockPessimistic Category = "Striken Out" End If ' Get a count of the fields and subtract one since we start ' counting from 0. iCols = rstExcel.Fields.Count %>

Back

List of <%=Category%> Members

<% ' Show the names that are contained in the first row ' of the named range. Make sure you include them in ' your range when you create it. ' Response.Write "
" For I = 0 To iCols - 1 Response.Write "" & vbCrLf Next 'I %> <% rstExcel.MoveFirst ' Loop through the data rows showing data in an HTML table. Do While Not rstExcel.EOF Response.Write "" & vbCrLf For I = 0 To iCols - 1 Response.Write "" & vbCrLf Next 'I Response.Write "" & vbCrLf rstExcel.MoveNext Loop %>
" Response.Write rstExcel.Fields.Item(I).Name Response.Write "
" Response.Write rstExcel.Fields.Item(I).Value Response.Write "
<% rstExcel.Close Set rstExcel = Nothing cnnExcel.Close Set cnnExcel = Nothing %>