Load SQL query in Excel with .asp

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

Other Development Tools

Load SQL query in Excel with .asp

Author

Topic

icecube
Starting Member

15 Posts

Posted - 2003-11-12 : 16:15:55

Hi,

I would like to load an sql-query in Excel with an .asp-page. The reason for not using DTS is because the user should be able to specify certain values in the query, e.g. date, customer, etc. I do the first part with an .htm form, which I can retrieve in the .asp - so that's no problem. I guess I have to work with .dns-file and filesystemobject here. The sql-query includes 2 different tables so the sql statement includes a join btw these tables. So the flow is:
1. .htm page with a form and input fields. (I know this part...)
2. .asp page that creates the sql statement,
3. then creates the link to excel and the .xls file
4. defines header columns in the .xls
5. loads the sql data into the .xls

Would apericiate any help on this topic.

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2003-11-12 : 18:15:54

You may want to reconsider DTS. DTS ActiveX Scripts can accept input variables using DTSGlobalVariables.

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2003-11-12 : 18:20:47

The other option is to return a CSV which will generally open in Excel.

Just set Response.ContentType = "text\csv" (i think, you might want to look up the mime type)
and response write out the text like this :

Dim strQuote
strQuote = """" 'Because that is a pain to type

Do while not objRS.EOF

response.write strQuote & objRS("field1") & strQuote & ","
response.write strQuote & objRS("field2") & strQuote

objRS.movenext

Loop

etc

Damian

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-11-12 : 20:25:58

Hold on a minute Damian...did you just use a MoveNext loop???????? Are you smoking crack or something?

Dim strQuote, data
strQuote = """" 'Because that is a pain to type

data=rs.GetString(,,strQuote & "," & strQuote, strQuote & vbCrLf & strQuote, "")
rs.Close
response.write strQuote & Left(data, Len(data)-1) ' remove trailing quotation mark

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2003-11-12 : 20:29:52

Well yeah.... that's how I would do it too.
I was trying to make it a clearer demonstration.

You don't beleive me do you ?

Oh well

Damian

SamC
White Water Yakist

3467 Posts

Posted - 2003-11-12 : 23:05:36

I found using HTML to EXCEL worked best to me. It transferrs some of the formatting, and is less problematic than CSV when fields contain unexpected commas.

Set p_objConn = Server.CreateObject("ADODB.Connection")
p_objConn.Open application("DBaddr")
Set p_objRS = Server.CreateObject("ADODB.Recordset")
Set p_objRS = p_objConn.Execute(SQLcommand)
strRS = p_objRS.GetString(, , "</td><td>", "</td></tr>" & vbcrlf & "<tr><td>", " ")
p_objRS.Close
Set p_objRS = Nothing
p_objConn.Close
Set p_objConn = Nothing

Response.Clear()
Response.AddHeader "Content-Disposition", "attachment;filename=" & CSVFileName & ".xls"
Response.ContentType = "application/vnd.ms-excel"

%>
<table border="1">
<tr><td>

<%= strRS %>
</td></tr>
</table>
</body>

Sam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources