Join us on Facebook!

Display an Excel DB

by admin 1792 views 0 Comments and 0 Rections

Excel can act as a database. It's not the fastest or the best. However, for small data bits on a server where the chance for significant simultaneous traffic is not likely excel can perform many of the basic tasks that can be accomplished in Access or SQL Server, but on a much smaller scale. For this example, we've taken our db on the career statistics of hank Greenberg and place it insided an excel spreadsheet. We've also named the range of his statistics, including the header rows hamerin_hank. This is not to be confused with naming a sheet in an excel. You must highlight the full range of cells you want to query. From the toolbar in excel select insert -> Name -> define. Name the cell range (I would avoid spaces and special characters) and click OK. Other the directing the query to the excel driver instead of the Access data most everything is the same as displaying an Access db. an Excel in ASP)

' Import constants from specifically
'adOpenStatic and adLockPessimistic

'Name of the excel file being displayed

' Create a server connection object
Set cn = Server.CreateObject("ADODB.Connection")
cn.Open "DBQ=" & Server.MapPath(exceldb) & ";" & _
"DRIVER={Microsoft Excel Driver (*.xls)};"

' Create a server recordset object
Set rs = Server.CreateObject("ADODB.Recordset")

' Query to run against the exceldb
' hamerin_hank is the name of the
' cell range as defined in excel
sql="select * from hammerin_hank;"

' Execute the sql
rs.Open sql, cn, _
adOpenStatic, adLockPessimistic

For counter = 0 To rs.fields.count - 1 %>
' Move to the next field

' Move to the first record

' Write out the record set
do while not rs.eof %>
' Loop through all of the fileds
for counter = 0 to rs.fields.count - 1
' Move to the next field
' Move to the next record
Hank Greenberg Career Statistics

<% ' Write out the field names
response.write rs.fields.item(counter).name %>

<% ' Write out the field values
response.write rs.fields.item(counter).value %>

' Kill the recordset
Set rs = nothing
' Kill the connection
Set cn = nothing

More Posts in "Tutorials"


blog comments powered by Disqus

Feed Subscription

Get daily inboxed with latest news, reviews and tutorials. Signup now it's free :)

Planetmaks on Twitter

Follow PlanetMaks on Facebook

Recent Comments

Powered by Disqus


32Bit 64Bit 7 Admin Tool Admin Tools Admin-Tool Admin-Tools AdminTool Adobe Adware All-in-One Anti-Spyware Anti-Virus Apple Backup Tools Browser Browsers Changing Default Directory Codec Data Recovery Download Manager Downloads Drive Utilities Driver Drivers Firefox Flash Freeware Google Graphics Internet Tool Internet-Tool Messaging Microsoft Microsoft Tool Micsellonous Monitoring Multimedia Networking News NVidia Office Tool Office-Tool PC Suite Press Release Registry Samsung Security Shareware Software Softwares Tutorials Vista Windows Windows 7 Windows 8