I have an asp page which shows a formatted view (html table) of a record set generated from a query in Access via ADO. I would like to have that same recordset in Excel spreadsheet form so the user can click on a button and download it. Are there any examples on how to do that which someone can provide me? Thanks.
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!
-
Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Export recordset from query to Excel spreadsheet 2
- Thread starter djmc
- Start date
if you search this forum I'm sure you'll find an answer. This questions has been posted many times...
.....................................................................................................................
"The secret to creativity is knowing how to hide your sources."
-Albert Einstein
.....................................................................................................................
"The secret to creativity is knowing how to hide your sources."
-Albert Einstein
-
1
- #3
- Thread starter
- #4
I have read the posts on the forum regarding the conversion of a recordset to Excel spreadsheets.
Here is my code for it:
Function getExcelFile(ByRef strDbName, ByRef strDbPass, ByRef strSQL, ByRef strFileName)
Response.AddHeader "Content-Disposition", "attachment; filename=" & strFileName & ";"
Response.ContentType = "bad/type"
' Create the database connection
Set conn = Server.CreateObject("ADODB.Connection"
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=" & Server.MapPath(strDbName) & ";" _
& "Jet OLEDB
atabase Password=" & strDbPass & ";"
Set rs = Server.CreateObject("ADODB.Recordset"
rs.Open strSQL, conn
Dim i
For i = 0 to rs.Fields.Count - 1
Response.Write rs.Fields(i).Name & chr(09)
Next
' -- End the line so your row is complete; use the built-in constant 'vbnewLine'
Response.Write vbNewLine
' -- Then send the entire data set
Do While Not rs.EOF
For i = 0 to rs.Fields.Count - 1
Response.Write rs.Fields(i) & chr(09)
Next
' -- End the line so your row is complete
Response.Write vbNewLine
' -- Next record
rs.MoveNext
Loop
rs.close
conn.close
Set rs = Nothing
Set conn = Nothing
End Function
--------------------------------------
Now it works but the only problem I have with this is that the file is not a native Excel file. It is using tab delimited format right now. I want to know how I can set the file to native Excel format (without opening the tab delimited file and save as Excel worksheet). Thanks
Here is my code for it:
Function getExcelFile(ByRef strDbName, ByRef strDbPass, ByRef strSQL, ByRef strFileName)
Response.AddHeader "Content-Disposition", "attachment; filename=" & strFileName & ";"
Response.ContentType = "bad/type"
' Create the database connection
Set conn = Server.CreateObject("ADODB.Connection"
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=" & Server.MapPath(strDbName) & ";" _
& "Jet OLEDB
atabase Password=" & strDbPass & ";"Set rs = Server.CreateObject("ADODB.Recordset"
rs.Open strSQL, conn
Dim i
For i = 0 to rs.Fields.Count - 1
Response.Write rs.Fields(i).Name & chr(09)
Next
' -- End the line so your row is complete; use the built-in constant 'vbnewLine'
Response.Write vbNewLine
' -- Then send the entire data set
Do While Not rs.EOF
For i = 0 to rs.Fields.Count - 1
Response.Write rs.Fields(i) & chr(09)
Next
' -- End the line so your row is complete
Response.Write vbNewLine
' -- Next record
rs.MoveNext
Loop
rs.close
conn.close
Set rs = Nothing
Set conn = Nothing
End Function
--------------------------------------
Now it works but the only problem I have with this is that the file is not a native Excel file. It is using tab delimited format right now. I want to know how I can set the file to native Excel format (without opening the tab delimited file and save as Excel worksheet). Thanks
-
1
- #5
with objCmd
.commandText = "br_sp_threeDayReport"
.CommandType = &H0004
End With
set objrs = objcmd.execute()
recCount = 0
do while not objrs.eof
if lcase(objrs("created") <> "null" then
if lastName <> "" then
if lastName <> objrs("lastName" then strOut = strOut & "<tr></tr>" '<
end if
recCount = recCount + 1
lastName = objrs("lastName"
end if
strOut = strOut & objrs(0)
objrs.movenext
loop
if recCount > 0 then ' <'records found'
Set act = fso.CreateTextFile(server.mappath(g_filename), true)
with act
.WriteLine "<html xmlns:x=""urn:schemas-microsoft-com
ffice:excel"">"
.WriteLine "<head>"
.WriteLine "<!--[if gte mso 9]><xml>"
.WriteLine "<x:ExcelWorkbook>"
.WriteLine "<x:ExcelWorksheets>"
.WriteLine "<x:ExcelWorksheet>"
.WriteLine "<x:Name>Items Over 3 Days Old</x:Name>"
.WriteLine "<x:WorksheetOptions>"
.WriteLine "<x
ageSetup>"
.WriteLine "<x:Header>BRTT Active Requests Over 3 Days Old</x:Header>"
.WriteLine "</xageSetup>"
.WriteLine "<xrint>"
.WriteLine "<x:ValidPrinterInfo/>"
.WriteLine "</xrint>"
.WriteLine "</x:WorksheetOptions>"
.WriteLine "</x:ExcelWorksheet>"
.WriteLine "</x:ExcelWorksheets>"
.WriteLine "</x:ExcelWorkbook>"
.WriteLine "</xml>"
.WriteLine "<![endif]--> "
.WriteLine "</head>"
.WriteLine strOut '<-- this is an HTML table
end with
act.close
Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rick Cook
.commandText = "br_sp_threeDayReport"
.CommandType = &H0004
End With
set objrs = objcmd.execute()
recCount = 0
do while not objrs.eof
if lcase(objrs("created"
) <> "null" thenif lastName <> "" then
if lastName <> objrs("lastName"
then strOut = strOut & "<tr></tr>" '<end if
recCount = recCount + 1
lastName = objrs("lastName"
end if
strOut = strOut & objrs(0)
objrs.movenext
loop
if recCount > 0 then ' <'records found'
Set act = fso.CreateTextFile(server.mappath(g_filename), true)
with act
.WriteLine "<html xmlns:x=""urn:schemas-microsoft-com
ffice:excel"">".WriteLine "<head>"
.WriteLine "<!--[if gte mso 9]><xml>"
.WriteLine "<x:ExcelWorkbook>"
.WriteLine "<x:ExcelWorksheets>"
.WriteLine "<x:ExcelWorksheet>"
.WriteLine "<x:Name>Items Over 3 Days Old</x:Name>"
.WriteLine "<x:WorksheetOptions>"
.WriteLine "<x
ageSetup>".WriteLine "<x:Header>BRTT Active Requests Over 3 Days Old</x:Header>"
.WriteLine "</x
ageSetup>".WriteLine "<x
rint>".WriteLine "<x:ValidPrinterInfo/>"
.WriteLine "</x
rint>".WriteLine "</x:WorksheetOptions>"
.WriteLine "</x:ExcelWorksheet>"
.WriteLine "</x:ExcelWorksheets>"
.WriteLine "</x:ExcelWorkbook>"
.WriteLine "</xml>"
.WriteLine "<![endif]--> "
.WriteLine "</head>"
.WriteLine strOut '<-- this is an HTML table
end with
act.close
Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rick Cook
Am I missing something, or are you guys making this a lot more complicated than it needs to be?
Just add this line to the <head> section of your page:
Response.ContentType = "application/vnd.ms-excel"
Then in the <body> section of the page, layout the data from the recordset in an HTML table.
The page will open in the browser as an Excel spreadsheet. If the user wants to save it to their local machine, all they have to do is click File/Save As... and give it a path/filename.
_________
Rott Paws
...It's not a bug. It's an undocumented feature!!!
Just add this line to the <head> section of your page:
Response.ContentType = "application/vnd.ms-excel"
Then in the <body> section of the page, layout the data from the recordset in an HTML table.
The page will open in the browser as an Excel spreadsheet. If the user wants to save it to their local machine, all they have to do is click File/Save As... and give it a path/filename.
_________
Rott Paws
...It's not a bug. It's an undocumented feature!!!
Rott,
I've used my code to create tables to append to emails without ever using the browser to display it. I just depends on what you are trying to do.
Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rick Cook
I've used my code to create tables to append to emails without ever using the browser to display it. I just depends on what you are trying to do.
Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rick Cook
djmc wrote:
"...shows a formatted view (html table) of a record set..."
"...would like to have that same recordset in Excel spreadsheet form so the user can click on a button and download it."
It seems to me that the bulk of the work was already done and all that was needed was that one line of vbscript code in the head section.
_________
Rott Paws
...It's not a bug. It's an undocumented feature!!!
"...shows a formatted view (html table) of a record set..."
"...would like to have that same recordset in Excel spreadsheet form so the user can click on a button and download it."
It seems to me that the bulk of the work was already done and all that was needed was that one line of vbscript code in the head section.
_________
Rott Paws
...It's not a bug. It's an undocumented feature!!!
- Thread starter
- #9
This is what I was trying to do:
Function getExcelDownload(ByRef strDbName, ByRef strDbPass, ByRef strSQL, ByRef strFileName, ByRef strName, ByRef strHeader)
' Force download prompt
Response.AddHeader "Content-Disposition", "attachment; filename=" & strFileName & ";"
Response.ContentType = "bad/type"
' Create the database connection
Set conn = Server.CreateObject("ADODB.Connection"
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=" & Server.MapPath(strDbName) & ";" _
& "Jet OLEDBatabase Password=" & strDbPass & ";"
Set rs = Server.CreateObject("ADODB.Recordset"
rs.Open strSQL, conn
Dim i, strOut, recCount
strOut = "<table border='1'>"
strOut = strOut & "<tr>"
For i = 0 to rs.Fields.Count - 1
strOut = strOut & "<td><b>" & rs.Fields(i).Name & "</b></td>"
Next
strOut = strOut & "</tr>"
Do While Not rs.EOF
strOut = strOut & "<tr>"
For i = 0 to rs.Fields.Count - 1
strOut = strOut & "<td align='left'>" & rs.Fields(i) & "</td>"
Next
strOut = strOut & "</tr>"
recCount = recCount + 1
rs.MoveNext
Loop
strOut = strOut & "</table>"
' Close the database connection
rs.close
conn.close
Set rs = Nothing
Set conn = Nothing
' Generate XML Excel Format
If recCount > 0 Then
Response.Write "<html xmlns:x=""urn:schemas-microsoft-comffice:excel"">"
Response.Write "<head>"
Response.Write "<!--[if gte mso 9]><xml>"
Response.Write "<x:ExcelWorkbook>"
Response.Write " <x:ExcelWorksheets>"
Response.Write " <x:ExcelWorksheet>"
Response.Write " <x:Name" & strName & "</x:Name>"
Response.Write " <x:WorksheetOptions>"
Response.Write " <xageSetup>"
Response.Write " <x:Header" & strHeader & "</x:Header>"
Response.Write " </xageSetup>"
Response.Write " <xrint>"
Response.Write " <x:ValidPrinterInfo/>"
Response.Write " </xrint>"
Response.Write " </x:WorksheetOptions>"
Response.Write " </x:ExcelWorksheet>"
Response.Write " </x:ExcelWorksheets>"
Response.Write "</x:ExcelWorkbook>"
Response.Write "</xml>"
Response.Write "<![endif]--> "
Response.Write "</head>"
Response.Write strOut '<-- this is an HTML table
End If
End Function
%>
Basically I wanted an Excel spreadsheet created from a query in access and bring up the download dialog for the user to save onto the hard drive. It appears that the XML format works fine so I am keeping it in this way. Thanks.
Function getExcelDownload(ByRef strDbName, ByRef strDbPass, ByRef strSQL, ByRef strFileName, ByRef strName, ByRef strHeader)
' Force download prompt
Response.AddHeader "Content-Disposition", "attachment; filename=" & strFileName & ";"
Response.ContentType = "bad/type"
' Create the database connection
Set conn = Server.CreateObject("ADODB.Connection"
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=" & Server.MapPath(strDbName) & ";" _
& "Jet OLEDB
atabase Password=" & strDbPass & ";"Set rs = Server.CreateObject("ADODB.Recordset"
rs.Open strSQL, conn
Dim i, strOut, recCount
strOut = "<table border='1'>"
strOut = strOut & "<tr>"
For i = 0 to rs.Fields.Count - 1
strOut = strOut & "<td><b>" & rs.Fields(i).Name & "</b></td>"
Next
strOut = strOut & "</tr>"
Do While Not rs.EOF
strOut = strOut & "<tr>"
For i = 0 to rs.Fields.Count - 1
strOut = strOut & "<td align='left'>" & rs.Fields(i) & "</td>"
Next
strOut = strOut & "</tr>"
recCount = recCount + 1
rs.MoveNext
Loop
strOut = strOut & "</table>"
' Close the database connection
rs.close
conn.close
Set rs = Nothing
Set conn = Nothing
' Generate XML Excel Format
If recCount > 0 Then
Response.Write "<html xmlns:x=""urn:schemas-microsoft-com
ffice:excel"">"Response.Write "<head>"
Response.Write "<!--[if gte mso 9]><xml>"
Response.Write "<x:ExcelWorkbook>"
Response.Write " <x:ExcelWorksheets>"
Response.Write " <x:ExcelWorksheet>"
Response.Write " <x:Name" & strName & "</x:Name>"
Response.Write " <x:WorksheetOptions>"
Response.Write " <x
ageSetup>"Response.Write " <x:Header" & strHeader & "</x:Header>"
Response.Write " </x
ageSetup>"Response.Write " <x
rint>"Response.Write " <x:ValidPrinterInfo/>"
Response.Write " </x
rint>"Response.Write " </x:WorksheetOptions>"
Response.Write " </x:ExcelWorksheet>"
Response.Write " </x:ExcelWorksheets>"
Response.Write "</x:ExcelWorkbook>"
Response.Write "</xml>"
Response.Write "<![endif]--> "
Response.Write "</head>"
Response.Write strOut '<-- this is an HTML table
End If
End Function
%>
Basically I wanted an Excel spreadsheet created from a query in access and bring up the download dialog for the user to save onto the hard drive. It appears that the XML format works fine so I am keeping it in this way. Thanks.
![[peace]](/data/assets/smilies/peace.gif "[peace] [peace]")
hockeylvr
Technical User
- Nov 26, 2002
- 140
Just want to clarify as I have seen it written both ways in posts. Does this code <% Response.ContentType = "application/vnd.ms-excel" %> actually OPEN the Excel program or does it just appear in the browser in Excel format and give you the ability to "Save As" Excel?
Thanks!
Toni
Thanks!
Toni
<% Response.ContentType = "application/vnd.ms-excel" %> tells IE to use the excel plugin to display the page - it does not open the excel program.
Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rick Cook
Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rick Cook
This does work, but when you save it as an Excel workbook, unfortunately any number data types in the spreadsheet does not "act" like a number and can't be summed or treated in any way as a real number. Does anyone know how to get around this?
When I set the ContentType to excel, the numbers do act like numbers.
I tried opening a web page that does this on several computers and the number fields acted like numbers on all of them.
BTW....I haven't figured out the rhyme or reason, but on one of my computers, this actually does open the Excel application instead of opening the spreadsheet in IE.....
_________
Rott Paws
...It's not a bug. It's an undocumented feature!!!
I tried opening a web page that does this on several computers and the number fields acted like numbers on all of them.
BTW....I haven't figured out the rhyme or reason, but on one of my computers, this actually does open the Excel application instead of opening the spreadsheet in IE.....
_________
Rott Paws
...It's not a bug. It's an undocumented feature!!!
Hi all,
I am using the following code to fetch userids from a database and display it in browser in MS Excel worksheet format.
The problem is when i run the code for the first time; it does not show me the results. It works better when I run the same page again while the first instant is not closed (i.e. if I did not close the first browser window).
1. How can I get the results in the first instant of the page?
2. How can I enforce the code to display the Open or Save options dialog box.?
Can anybody help ?
My Code is given below
<%
Response.ContentType = "Application/vnd.ms-excel"
' connection code here
Set rsUsers = Server.CreateObject("ADODB.RecordSet"
sql = "SELECT UserID FROM Users"
rsUsers.Open sql,connection
Response.Write("<TABLE>"
Response.Write("<TR>"
Response.Write("<TD>User ID</TD>"
Response.Write("</TR>"
While Not rsUsers.EOF
Response.Write("<TR>"
Response.Write("<TD>" & rsUsers.Fields("UserID" & "</TD>"
rsUsers.MoveNext
Response.Write("</TR>"
Wend
Response.Write("</TABLE>" )
rsUsers.Close
Set rsUsers=Nothing
%>
I am using the following code to fetch userids from a database and display it in browser in MS Excel worksheet format.
The problem is when i run the code for the first time; it does not show me the results. It works better when I run the same page again while the first instant is not closed (i.e. if I did not close the first browser window).
1. How can I get the results in the first instant of the page?
2. How can I enforce the code to display the Open or Save options dialog box.?
Can anybody help ?
My Code is given below
<%
Response.ContentType = "Application/vnd.ms-excel"
' connection code here
Set rsUsers = Server.CreateObject("ADODB.RecordSet"
sql = "SELECT UserID FROM Users"
rsUsers.Open sql,connection
Response.Write("<TABLE>"
Response.Write("<TR>"
Response.Write("<TD>User ID</TD>"
Response.Write("</TR>"
While Not rsUsers.EOF
Response.Write("<TR>"
Response.Write("<TD>" & rsUsers.Fields("UserID"
& "</TD>" rsUsers.MoveNext
Response.Write("</TR>"
Wend
Response.Write("</TABLE>" )
rsUsers.Close
Set rsUsers=Nothing
%>
- Status
Similar threads
- Locked
- Question
- Locked
- Question
- Locked
- Question