Excel validation in asp

Squall Leonhart

11-27-2003, 09:44 PM

Hi, guys.

Take a look at the 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>

Browser said line

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

has error. I wonder how can I validate it
I looked for web sites. But no one has ever clarified this one.

Similar article
http://www.dbforums.com/t704744.html

Roelf

11-28-2003, 09:10 AM

is excel installed on the webserver?
if so, how is security set for the website (in IIS), anonymous login? Then the user IUSR_machinename is trying to start excel. Every user which tries to start excel has to run through an msi-installer before it can be used. the IUSR_machinename also. Did he do that?
its better to set a user for login so this user can run excel once after loggin in to the OS, then excel is ready to be used by the server

Roelf

11-28-2003, 09:14 AM

Dont start a new thread if the question is practically the same as an old one. especially if glenn answered this question already

glenngv

11-28-2003, 09:27 AM

For the sake of other members, here's that thread: http://www.codingforums.com/showthread.php?s=&postid=152335#post152335

Squall Leonhart

11-28-2003, 06:22 PM

Glenn, I passed
Set objExcel = Server.CreateObject("Excel.Application")

that problem.
But this time it says this line
objExcel.Workbooks.Open "DRIVER={Microsoft Excel Driver (*.xls)};DBQ= " & Server.MapPath("excelface.xls")

has the error

Microsoft Excel error '800a03ec'

'DRIVER={Microsoft Excel Driver (*.xls)};DBQ= C:\Quick_Services\QuickForms\QFAC1004\doc\ExportExcelSample\tech_re\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.

Absolutely file is in the same folder as this page and no mistake on spelling of file name.

So I changed the code to

objExcel.Workbooks.Open " \\wks-it-kch\ExportExcelSample\tech_re\excelface.xls"

it still says file excelface.xls cannot be found.
I checked by typing path into windows explorer it's valid path.
I wonder what's wrong with it.:(

Roelf

11-29-2003, 02:35 PM

try:
objExcel.Workbooks.Open Server.MapPath("excelface.xls")

glenngv

12-01-2003, 12:35 AM

Is the file really in the same folder as the asp page or in another computer in the network? You specified a network path in your 2nd code.

Squall Leonhart

12-01-2003, 07:42 PM

They are all in the same folder. They are all in the network drive
:o

glenngv

12-02-2003, 01:07 AM

So you mean the file "excelface.xls" is not in the IIS server but in another machine?

objExcel.Workbooks.Open "\\wks-it-kch\ExportExcelSample\tech_re\excelface.xls"

Since the error is "file cannot be found", check the path you specified. There must be something wrong with it. Had it been the IUSR_machinename has no permission to open the file, the error could have been something like "Access denied".

Squall Leonhart

12-02-2003, 06:21 PM

I have found the working code finally.

<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

act.WriteLine(A1:E3)= "Congraturations"
This code doesn't work yet.

Squall Leonhart

12-03-2003, 07:02 PM

I wonder how I can write content on specific location of excel sheet.
:confused:

glenngv

12-04-2003, 09:40 AM

Every Excel cell represents a <td></td>

<table border="1">
<tr><td>A1</td><td>B1</td><td>C1</td></tr>
<tr><td>A2</td><td>B2</td><td>C2</td></tr>
<tr><td>A3</td><td>B3</td><td>C3</td></tr>
</table>