Exporting access database to Excel spreadsheet [Archive]

Click to See Complete Forum and Search --> : Exporting access database to Excel spreadsheet

Squall Leonhart

11-24-2003, 12: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?

CardboardHammer

11-24-2003, 12:33 PM

Do an export to xls instead of making everything harder for yourself...

From MSDN:

Sub ExportDataToExcel(strTableName, _
strFileName As String, _
blnHasFieldNames As Boolean)
' Exports a table or query as static data to Excel.

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strTableName, strFileName, blnHasFieldNames
End Sub

Squall Leonhart

11-24-2003, 01:19 PM

Mr.hammer, thanks for reply, so I changed code like this.

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

<%
Sub ExportDataToExcel(tblFAQ, _
excelface As String, _
blnHasFieldNames As Boolean)
' Exports a table or query as static data to Excel.

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
tblFAQ, excelface, blnHasFieldNames
End Sub

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>

But still doesn't work.:( What should I do?

Squall Leonhart

11-24-2003, 03:10 PM

Please help me guys~

CardboardHammer

11-24-2003, 07:25 PM

Get rid of all that code, make (<-edit for spelling. I wan't wearing my contacts at the time) an Access object instead of an Excel object. open the correct file and

objAccess.DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strTableName, strFileName, blnHasFieldNames

And substitute appropriate values for the variables... You've got WAY more code than you need...

Squall Leonhart

11-25-2003, 11:56 AM

Hammer, so I changed the code as you said.

<%@ 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 "excelface.xls"

'Defines the first row
i = 3

objAccess.DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
tblFAQ, tech_re.mdb, ID, Category,description, doc_ID

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

Still doesn't work.:( What can I do?

CardboardHammer

11-25-2003, 02:04 PM

Try this. If it fails, give me the exact error message you get.

I've not done any ASP in a while (I use ASP.NET now), so I'm not entirely certain this is exactly right... You may have to reference a type library to use the constants... like I said, it's been a while...

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

<%

Set objAccess= Server.CreateObject("Access.Application")
objAccess.OpenCurrentDatabase "tech_re.mdb"
objAccess.DoCmd.TransferSpreadsheet acExport, , "FAQ", "FAQ.xls", True
objAccess.CloseCurrentDatabase
objAccess.Quit
Set objAccess = Nothing

%>
<!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-25-2003, 03:11 PM

Hammer, it still didn't work.
it says page cannot be displayed.

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

<%

Set objAccess= Server.CreateObject("Access.Application")
objAccess.OpenCurrentDatabase "tech_re.mdb"
objAccess.DoCmd.TransferSpreadsheet acExport, , "tblFAQ", "excelface.xls", True
objAccess.CloseCurrentDatabase
objAccess.Quit
Set objAccess = Nothing

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

CardboardHammer

11-25-2003, 03:40 PM

Comment out all the scripting and see if you can get ANYTHING to display there...

Squall Leonhart

11-25-2003, 04:18 PM

It seems there is problem in this part.

<%

'Set objAccess= Server.CreateObject("Access.Application")
'objAccess.OpenCurrentDatabase "tech_re.mdb"
'objAccess.DoCmd.TransferSpreadsheet acExport, , "tblFAQ", "excelface.xls", True
'objAccess.CloseCurrentDatabase
'objAccess.Quit
'Set objAccess = Nothing

%>

CardboardHammer

11-25-2003, 05:20 PM

Uncomment the first and last line.

Test.

If no error, then also uncomment the second line.

Test.

If no error, then also uncomment lines 4 and 5.

Test.

It could be that the constant may be the issue... If it makes it to this point without error, replace

'objAccess.DoCmd.TransferSpreadsheet acExport, , "tblFAQ", "excelface.xls", True

with

objAccess.DoCmd.TransferSpreadsheet 1, , "tblFAQ", "excelface.xls", True

and test again.

Let me know where if and where it fails through the above stages.

Squall Leonhart

11-25-2003, 05:38 PM

Hammer, when I uncommented first line

<%
Set objAccess= Server.CreateObject("Access.Application")
'objAccess.OpenCurrentDatabase "tech_re.mdb"
'objAccess.DoCmd.TransferSpreadsheet 1, , "tblFAQ", "excelface.xls", True
'objAccess.CloseCurrentDatabase
'objAccess.Quit
'Set objAccess = Nothing
%>

Still page couldn't be displayed.
:confused:

CardboardHammer

11-25-2003, 10:01 PM

Is Access installed on the server?

Squall Leonhart

11-26-2003, 01:08 PM

Yes, it's installed.
I think something with that line.
Set objAccess= Server.CreateObject("Access.Application")

Squall Leonhart

11-26-2003, 06:29 PM

I tried this code as well
Still not doing anything

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

Squall Leonhart

11-27-2003, 03:31 PM

It seems like some validation error

Squall Leonhart

12-02-2003, 12:53 PM

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_created), true)

' All non repetitive html on top goes here

act.WriteLine("<html><body>")
act.WriteLine("<table border=""1"">")
act.WriteLine("<tr><center><FOnt size=10>North American</font></center></tr>")
act.WriteLine("<tr><center><FOnt size=10>Price List</font></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.

CardboardHammer

12-04-2003, 12:52 PM

Just do math.

A1 is line one, column one.

E3 is line five, column three.

Note that A1:E3 is a range, NOT a cell.

If you want to rewrite specific locations where data might be written, use a counter and if/else logic within the data writing loop. If you want write something past the end of the data, just pad with empty entries until you get the displacement you want and write away...