...

View Full Version : Exporting access database to Excel spreadsheet



Squall Leonhart
11-24-2003, 06:09 PM
Hi, guys.

Good to see you again.
I am trying to export data from access database to excel spreadsheet.

Please take a look at following code.


<HTML>
<HEAD>
</HEAD>
<BODY LEFTMARGIN=0 MARGINWIDTH="0" MARGINHEIGHT="0">
<a href="pricelist.asp"><FONT FACE=Arial,Helvetica SIZE=2><B>Price List</B></FONT></a>
</BODY>
</HTML>

When I click link Price List, I go to following page.



<%@ 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>


This code is supposed to write data from access database to excel spreadsheet.
But it doesn't work.:confused: What should I do?

Squall Leonhart
11-24-2003, 09:13 PM
Please help me guys~:(

raf
11-24-2003, 10:36 PM
This code is supposed to write data from access database to excel spreadsheet.
But it doesn't work. What should I do?

Well, what does it do ?

Anyway, if you need a quick answer, run a search. This thread contains multiple codes to create xls-files.

http://www.codingforums.com/showthread.php?s=&threadid=12144&highlight=excel

In your case, you would just open the recordset and file, and then inside the while loop, write each record as a line to the file and save the file as an xls.

Squall Leonhart
11-24-2003, 10:44 PM
Well, what does it do ?


It doesn't write database data to excel file.
excelface.xls

Squall Leonhart
11-25-2003, 09:24 PM
Please help me, guys.

raf
11-25-2003, 11:34 PM
Euh. What extra help do you need ?

The link in my previous psot co=tains all the code you need (multiple versions). Check out whammy's code there (or below)
The part in bold need to be replaced by your own fileadress or sql or recordsetcode.

the whammy-code


<%
Dim fs, f, thefile, Conn, dbquery, rs, linevalue, LeadColumns
thefile = Server.MapPath("yourfilename.csv")
Set fs = Server.CreateObject("Scripting.FileSystemObject")
Set f = fs.OpenTextFile(thefile, 2, True)
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open 'Put your connection string here
Set rs = Server.CreateObject("ADODB.Recordset")

dbquery = "SELECT * FROM tablename ORDER BY whatever"
Set rs = Conn.Execute(dbquery)

For Each Item in rs.fields
LeadColumns = LeadColumns & """" & Replace(Item.Name,"""","") & ""","
Next
LeadColumns = Left(LeadColumns,Len(LeadColumns)-1)
f.WriteLine(LeadColumns)

Do While NOT rs.EOF
linevalue = "" 'reset linevalue to empty
For Each Item in rs.fields
linevalue = linevalue & """" & Replace(Item.Value,"""","") & ""","
Next
linevalue = Left(linevalue,Len(linevalue)-1)
f.WriteLine(linevalue)
rs.MoveNext
Loop

Set rs = Nothing
Conn.Close
Set Conn = Nothing

Set f = Nothing
Set fs = Nothing
%>


You need to ask more specific questions if you want quick and concrete help.
Like, for instance, what the problem is : do you get an error, dus the script timeout, ... etc

Squall Leonhart
11-26-2003, 12:05 AM
Thank you, Raf.
It works.
But how can I align items written in excel file to the column?

Is there any way to change the code above to align like that when it writes the data in excel file?
[

raf
11-26-2003, 01:27 AM
What do you mean by 'align' ?

whammy
11-26-2003, 01:48 AM
Squall, regarding your "Please help me, guys" messages... first of all you have to have a little patience. I've seen you post that message within an hour or two of posting your original question :(

Everyone here volunteers their time to help others, and most of us have full time jobs as programmers, so if your question doesn't get answered in a couple of hours, then you must realize we're probably way too busy to visit the forums. Your question (unless totally ambiguous) will probably get answered, because we all love to help!

Also please make sure you have fully tested something before you say it doesn't work, or at least provide the code you've used to test, and any error messages. :)

whammy
11-26-2003, 01:50 AM
Regarding that .csv code that I have used above, which raf posted, it's just a comma-delimited text file, which happens to work well in excel. It doesn't use "excel writer", but as long as your column headers and data match up, everything should align just fine.

Squall Leonhart
11-26-2003, 05:13 PM
Squall, regarding your "Please help me, guys" messages... first of all you have to have a little patience. I've seen you post that message within an hour or two of posting your original question

I really appreciate whenever you guys help me.:thumbsup:
I guess I was too enthusiastic.:p


What do you mean by 'align' ?
Raf, I mean when asp code export data from database to excel file, items contents kinda become written stick together not being separated by columns.

Squall Leonhart
11-26-2003, 07:49 PM
Right now code is writing everything in one column.
For example, there are fields ID, Category, Description, Doc_ID in Access database.
There are columns A,B, C ,D E,....so on in Excel datasheet.
Right now code is putting everything into one column A.
I wanted to write field ID to column A, Category to column B, and so on.

raf
11-26-2003, 08:11 PM
did you save the file with extension csv ? not extension xls !!

Your file will open in excell if you save it as file blablabla.cvs

cvs stands for domma seperated value. but if you save it with an xls extension, it could look like 'valueID', 'valueCategory', 'valueDescription', 'valueDoc_ID'
--> all stick to eachother whith a , in it.

The csv extension will tell excell to 'split the value by the ',' seperator'

Squall Leonhart
11-26-2003, 08:44 PM
Thank you very much.:)
It works. Yeah~:o

Squall Leonhart
11-26-2003, 09:20 PM
Hi, guys.
Sorry to bug you again.
mm.... Project specification says I was required to write data into .xls file instead of .csv
I had been going wrong way.
:eek:
Please take a look at following 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>


As you can see, what I am trying to do is writing data into excel .xls file. Code is not working. So is there any way to do this way?

Squall Leonhart
11-27-2003, 12:27 AM
I tried this way too.


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

<%

Dim sSQL
sSQL = "SELECT * FROM tblFAQ ORDER BY ID"
Dim nField
Dim oConn,oRs
set oRs = server.CreateObject("ADODB.RECORDSET")
set oConn = server.createobject("ADODB.CONNECTION")
oConn.ConnectionString = Application("ReadOnly")
Response.Buffer = TRUE
Response.ContentType = "application/vnd.ms-excel"
oConn.open "DRIVER={Microsoft Access Driver (*.mdb)};DBQ= " & Server.MapPath("tech_re.mdb")
oRs.Open sSQL,oConn,3,3
if oRs.EOF then ' Clean up ADO Objects and display error popup
oRs.Close
set oRs = nothing
oConn.Close
set oConn=nothing
DisplayErrMessage 1
else
' Create XLS File
' This code will dynamically create a table based on the number of fields returned by the SQL Query
Response.Write "<html><body>" & vbNewLine
Response.Write "<table border=" & chr(34) & "1" & chr(34) & " cellpadding=" & chr(34) & "0" & chr(34) & " cellspacing=" & chr(34) & "0" & chr(34) & "><tr>" & vbNewLine
' Create table "Header" cells
for nField = 0 to (oRs.Fields.count-1)
Response.Write "<td>" & oRs.Fields(nField).Name & "</td>"
next
Response.Write "</tr>" & vbNewLine

' Create data cells
' Formula example: <TD FORMULA="=Sum(A2:B2)">
' Number Formatting: <td STYLE="vnd.ms-excel.numberformat:$#.0000[semicolon]($#.0000)">

do until oRs.EOF
Response.Write "<tr>"
for nField = 0 to (oRs.Fields.count-1)
Response.Write "<td>" & oRs.Fields(nField).Value & "</td>"
next
Response.Write "</tr>" & vbNewLine
oRs.MoveNext
loop
Response.Write "</tr></table>" & vbNewLine
Response.Write "</body></html>" & vbNewLine
' Clean up ADO Objects
oRs.Close
set oRs = nothing
oConn.Close
set oConn=nothing
end if
end sub
sub DisplayErrMessage(nErrLvl)
dim sMsgText
select Case nErrLvl
case 0
sMsgText="Invalid Request!"
case 1
sMsgText="No data to display"
end select
' Note: The error message prompt does not have to be a javascript popup.
%>
<script language="javascript">
alert('<%= sMsgText %>')
window.close()
</script>
<%
Response.End
end sub
%>

<!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>

It doesn't create .xml file still.:( My head is spinning now:confused:

glenngv
11-27-2003, 01:48 AM
Originally posted by Squall Leonhart
It doesn't create .xml file still.:( My head is spinning now:confused:

The last code you posted is not supposed to create an xml file. It's supposed to display the contents of the xls file in the browser or prompt the File Open/Save dialog if the browser is not IE or the client machine has no Excel. The file could be saved in the client not in the server.

Where do you really want to save the excel file? in the server or the client? I think you want it saved in the server so you should use the first code you posted. You just have to make it worked. :)

Squall Leonhart
11-27-2003, 07:21 PM
So do you know what I need to fix on this part?


<%@ 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>

Squall Leonhart
11-27-2003, 07:49 PM
It seems like this part is causing errors.
Page can not be displayed


Set objExcel = Server.CreateObject("Excel.Application")


Not validating :confused:

glenngv
11-28-2003, 01:25 AM
Does the server have Excel installed?
Try:

Set objExcel = CreateObject("Excel.Application")

Note that I didn't use Server.CreateObject



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum