Error on exporting access to excel .xml

Dev Shed Forums Sponsor:

November 28th, 2003, 07:34 PM

Squall Leonhart

Junior Member

Join Date: Nov 2003

Posts: 5

Time spent in forums: < 1 sec
Reputation Power: 0

Error on exporting access to excel .xml

Hi, guys

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 "\\wks-it-kch\ExportExcelSample\test\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 generating error on the line

code:--------------------------------------------------------------------------------objExcel.Workbooks.Open "\\wks-it-kch\ExportExcelSample\test\excelface.xls"
--------------------------------------------------------------------------------

Error is

quote:
--------------------------------------------------------------------------------

Microsoft Excel error '800a03ec'

'\\wks-it-kch\ExportExcelSample\test\excelface.xls' could not be found. Check the spelling of the file name, and verify that the file location is correct. If you are trying to open the file from your list of most recently used files on the File menu, make sure that the file has not been renamed, moved, or deleted

--------------------------------------------------------------------------------

I checked file name and path. But nothing wrong has been found so far. 'wks-it-kch' is name of network drive I am using.
So can you guys see the reason why the file excelface.xls can't be found?

December 2nd, 2003, 01:57 PM

Squall Leonhart

Junior Member

Join Date: Nov 2003

Posts: 5

Time spent in forums: < 1 sec
Reputation Power: 0

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.

Viewing: Dev Shed Forums > Programming Languages - More > ASP Programming > Error on exporting access to excel .xml

« Previous Thread | Next Thread »

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread:

Posting Rules
You may not post new threads You may not post replies You may not post attachments You may not edit your posts vB code is On Smilies are On [IMG] code is On HTML code is Off

View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox

Forum Jump

Free IT White Papers!

How to Present Effectively Online
This white paper offers practical and actionable advice on the key steps that any presenter should consider as they plan and execute a Webinar or online meeting.

Open Source Security Myths
Open Source Software (OSS) is computer software whose source code is available to the general public with relaxed or non-existent intellectual property restrictions (or arrangement such as the public domain), and is usually developed with the input of many contributors.

Power and Cooling Capacity Management for Data Centers
This paper describes the principles for achieving power and cooling capacity management.

Scalable, Fault-Tolerant NAS for Oracle - The Next Generation
For several years NAS has been evolving as a storage alternative for Oracle databases, and for good reason: NAS is quite often the simplest, most cost-effective storage approach for Oracle. Learn about the benefits that HP's approach to scalable NAS brings to Oracle environments in this comprehensive white paper.

Understanding Web Application Security Challenges
This white paper discusses many common threats and preventive measures for Web application security, and explains what you can do to help protect your organization.

More Free IT White Papers!