Hello,

I am trying to use Excel to keep track of a database (I know, not
exactly what it is designed for). Anyway, this DB will be accessed by
the user using an HTML interfac. My question is, is there a way to call
up the DB showing only the entries that meet a user-defined criteria? I
asume this will involve auto filter on the selected columns of
information.

Please let me know if this is vague and further information is
required.

Thank you,
Jeremy


--
jeremy.watson
------------------------------------------------------------------------
jeremy.watson's Profile: http://www.excelforum.com/member.php...o&userid=23784
View this thread: http://www.excelforum.com/showthread...hreadid=374422

Jeremy,

This is too big a topic to give you an exhaustive answer, and most people
here work on the Excel interface rather than the web tier, but in concept
you can...

Use ADO to return data from the db file held in a closed Excel workbook to
your web interface as a recordset
This should allow you to use SQL queries to select the subset of data that
you want. e.g.

I have a file saved with the top row containing column names, one of which
is called ItemValue, with values underneath.

Option Explicit
Option Private Module

'add a reference to Ms ActiveX Data Objects library to your project

Sub GetRs()
'all these would be variant in asp
Dim rsData As ADODB.Recordset
Dim strPath As String
Dim strFile As String
Dim strCon As String
Dim strSQL As String
Dim con1 As ADODB.Connection

'replace with createobject in asp code
Set rsData = New ADODB.Recordset
Set con1 = New ADODB.Connection
strPath = "C:\My Documents\My Excel Files\Temp\TestRS.xls"

'your asp page would theoretically create the SQL string that is used here
strSQL = "SELECT TOP 10 ItemValue FROM [Sheet1$] ORDER BY ItemValue DESC"

strCon = "DRIVER={Microsoft Excel Driver (*.xls)};ReadOnly=1;DBQ=" & strPath
con1.Open strCon
rsData.Open strSQL, con1, adOpenStatic, adLockReadOnly, adCmdText
Debug.Print rsData.RecordCount 'shows you got data back
'DO SOMETHING WITH THE VALUES YOU HAVE RETRIEVED
Set rsData = Nothing
Set con1 = Nothing
End Sub

Robin Hammond
www.enhanceddatasystems.com

"jeremy.watson"
wrote in message
news:jeremy.watson.1pnvue_1117148705.2549@excelfor um-nospam.com...

Hello,

I am trying to use Excel to keep track of a database (I know, not
exactly what it is designed for). Anyway, this DB will be accessed by
the user using an HTML interfac. My question is, is there a way to call
up the DB showing only the entries that meet a user-defined criteria? I
asume this will involve auto filter on the selected columns of
information.

Please let me know if this is vague and further information is
required.

Thank you,
Jeremy


--
jeremy.watson
------------------------------------------------------------------------
jeremy.watson's Profile:
http://www.excelforum.com/member.php...o&userid=23784
View this thread: http://www.excelforum.com/showthread...hreadid=374422