Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > ASP.NET and ASP > ASP 3 Classic ASP Active Server Pages 3.0 > Classic ASP Databases
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Classic ASP Databases Discuss using ASP 3 to work with data in databases, including ASP Database Setup issues from the old P2P forum on this specific subtopic. See also the book forum Beginning ASP.NET Databases for questions specific to that book. NOT for ASP.NET 1.0, 1.1, or 2.0.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Classic ASP Databases section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old November 27th, 2003, 07:18 PM
Registered User
 
Join Date: Nov 2003
Location: c, c, .
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default Exporting access database to Excel .xls

Hi, guys.:)

Nice to meet you. First time be here.
Please take a look at following code.

Code:
<%@ Language="VBScript" %>
<% Option Explicit %>

<%
Dim objConnection
Dim objRecords
Dim objExcel
Dim strQuery
Dim i

Set objConnection = Server.CreateObject("ADODB.Connection")
objConnection.Open "DRIVER={Microsoft Access Driver (*.mdb)};DBQ= " & Server.MapPath("tech_re.mdb")

Set objRecords = Server.CreateObject("ADODB.Recordset")
strQuery = "SELECT * FROM tblFAQ"
objRecords.Open strQuery, objConnection

Set objExcel = Server.CreateObject("Excel.Application")
objExcel.Workbooks.Open "DRIVER={Microsoft Excel Driver (*.xls)};DBQ= " & Server.MapPath("excelface.xls")

'Defines the first row
i = 3

'Creates the column description
objExcel.ActiveSheet.Range("A" & i).Value = "ID"
objExcel.ActiveSheet.Range("B" & i).Value = "Category"
objExcel.ActiveSheet.Range("C" & i).Value = "Description"
objExcel.ActiveSheet.Range("C" & i).Value = "Document ID"
i = i + 1

'Fills columns for each recordset
While not objRecords.EOF
    objExcel.ActiveSheet.Range("A" & i).Value = objRecords("ID")
    objExcel.ActiveSheet.Range("B" & i).Value = objRecords("Category")
    objExcel.ActiveSheet.Range("C" & i).Value = objRecords("Description")
    objExcel.ActiveSheet.Range("D" & i).Value = objRecords("Doc_ID")
    objRecords.MoveNext
    i = i + 1
Wend

'Saves file and close Excel
objExcel.ActiveWorkbook.SaveAs("excelface (" & Date & ").xls")
objExcel.ActiveWorkbook.Close
objExcel.Workbooks.Close
objExcel.Quit
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>
<head>
    <title>Create a pricelist with ASP</title>
</head>
<body>
    Pricelist has been created successfully.
</body>
</html>
I had written this code to export data from access database to excel .xls file. Problem arises from the following line of the code.
Code:
Set objExcel = Server.CreateObject("Excel.Application")
I am getting error like not validated excel object on the server something like that.

Reply With Quote
  #2 (permalink)  
Old November 27th, 2003, 08:12 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: High Wycombe, UK, United Kingdom.
Posts: 344
Thanks: 0
Thanked 1 Time in 1 Post
Default

You may need to declare your variables AS something see the examples at :

http://www.greggriffiths.org/webdev/both/excel/
Reply With Quote
  #3 (permalink)  
Old November 28th, 2003, 07:09 PM
planoie's Avatar
Friend of Wrox
Points: 16,481, Level: 55
Points: 16,481, Level: 55 Points: 16,481, Level: 55 Points: 16,481, Level: 55
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Aug 2003
Location: Clifton Park, New York, USA.
Posts: 5,407
Thanks: 0
Thanked 16 Times in 16 Posts
Default

If you would like to have the output show up in the browser (in IE) or load up into Excel without having to save the file on the server, you can output the rows and columns as a clean HTML table. Change the content type to "application/ms-excel" and the browser will call Excel to load it up. When you browse this in IE excel will load right up within IE. Also, you can add standard HTML formatting to the cells and they will show up in Excel.

Peter
------------------------------------------------------
Work smarter, not harder.
Reply With Quote
  #4 (permalink)  
Old December 2nd, 2003, 02:58 PM
Registered User
 
Join Date: Nov 2003
Location: c, c, .
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I have found the working code finally.


code:--------------------------------------------------------------------------------
<html>
<title>CodeAve.com(Create Excel on Server)</title>
<body bgcolor="#FFFFFF">
<%
' Name of the access db being queried
accessdb="tech_re"

' Connection string to the access db
cn="DRIVER={Microsoft Access Driver (*.mdb)};"
cn=cn & "DBQ=" & server.mappath(accessdb)

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

' Query the states table from the tech_re db
sql = "select * from tblFAQ "

' Execute the sql
rs.Open sql, cn

' Move to the first record
rs.MoveFirst

' Name for the ouput document
file_being_created= "state.xls"

' create a file system object
set fso = createobject("scripting.filesystemobject")

' create the text file - true will overwrite any previous files
' Writes the db output to a .xls file in the same directory
Set act = fso.CreateTextFile(server.mappath(file_being_creat ed), true)

' All non repetitive html on top goes here


act.WriteLine("<html><body>")
act.WriteLine("<table border=""1"">")
act.WriteLine("<tr><center>North American</center></tr>")
act.WriteLine("<tr><center>Price List</center></tr>")
act.WriteLine("<tr>")
act.WriteLine("<th nowrap>ID</th>")
act.WriteLine("<th nowrap>Category</th>")
act.WriteLine("<th nowrap>Description</th>")
act.WriteLine("<th nowrap>Doc_ID</th>")
act.WriteLine("</tr>")

' For net loop to create seven word documents from the record set
' change this to "do while not rs.eof" to output all the records
' and the corresponding next should be changed to loop also
While not rs.EOF


Act.WriteLine("<tr>")
act.WriteLine("<td align=""right"">" & rs("ID") & "</td>" )
act.WriteLine("<td align=""right"">" & rs("Category") & "</td>" )
act.WriteLine("<td align=""right"">" & rs("Description") & "</td>")
act.WriteLine("<td align=""right"">" & rs("Doc_ID") & "</td>")
act.WriteLine("</tr>")

' move to the next record
rs.movenext

' return to the top of the for - next loop
' change this to "loop" to output all the records
' and the corresponding for statement above should be changed also
wend

' All non repetitive html on top goes here
act.WriteLine("</table></body></html>")

' close the object (excel)
act.close

' Writes a link to the newly created excel in the browser
response.write "<a href='state.xls'>Price List</a> (.xls) has been created on " & now() & "<br>"
%>
</body>
</html>
--------------------------------------------------------------------------------

This writes on .xls file.
But problem is when I wanted to write specific data into cells in specific location, how can I modify above code to do that?
For example, like this


code:--------------------------------------------------------------------------------act.WriteLine(A1:E3)= "Congraturations"--------------------------------------------------------------------------------

This code doesn't work yet.

Reply With Quote
  #5 (permalink)  
Old December 2nd, 2003, 06:46 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: High Wycombe, UK, United Kingdom.
Posts: 344
Thanks: 0
Thanked 1 Time in 1 Post
Default

check out http://www.greggriffiths.org/webdev/both/excel/
Reply With Quote
  #6 (permalink)  
Old December 2nd, 2003, 07:42 PM
Registered User
 
Join Date: Nov 2003
Location: c, c, .
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Sorry, I checked out the site. But there isn't the method I was looking for.
As you can see above, I want the way not using Active X method.

Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Exporting From Access To Excel jimnich Access VBA 17 February 8th, 2008 07:23 AM
Wrong Colors in Excel when exporting from Access zimtkorn.simon Access VBA 5 April 19th, 2007 01:22 PM
Exporting form Access Database to Excel asters VB.NET 2002/2003 Basics 5 April 13th, 2007 02:23 PM
Exporting to Excel from Access - field headings no chimp Access VBA 1 July 9th, 2004 11:54 AM
exporting a workbook from excel to access zisko3 Access 1 February 3rd, 2004 12:05 PM



All times are GMT -4. The time now is 07:52 PM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.