Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 12 of 12
  1. #1
    New Coder
    Join Date
    Nov 2003
    Location
    Canada
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Angry Excel validation in asp

    Hi, guys.

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

  • #2
    Senior Coder
    Join Date
    Jun 2002
    Location
    Zwolle, The Netherlands
    Posts
    1,120
    Thanks
    2
    Thanked 31 Times in 31 Posts
    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
    I am the luckiest man in the world

  • #3
    Senior Coder
    Join Date
    Jun 2002
    Location
    Zwolle, The Netherlands
    Posts
    1,120
    Thanks
    2
    Thanked 31 Times in 31 Posts

    read the posting guidelines

    Dont start a new thread if the question is practically the same as an old one. especially if glenn answered this question already
    I am the luckiest man in the world

  • #4
    Supreme Master coder! glenngv's Avatar
    Join Date
    Jun 2002
    Location
    Philippines
    Posts
    11,042
    Thanks
    0
    Thanked 251 Times in 247 Posts
    For the sake of other members, here's that thread: http://www.codingforums.com/showthre...335#post152335

  • #5
    New Coder
    Join Date
    Nov 2003
    Location
    Canada
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Glenn, I passed
    Code:
    Set objExcel = Server.CreateObject("Excel.Application")
    that problem.
    But this time it says this line
    Code:
    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

    Code:
    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.

  • #6
    Senior Coder
    Join Date
    Jun 2002
    Location
    Zwolle, The Netherlands
    Posts
    1,120
    Thanks
    2
    Thanked 31 Times in 31 Posts
    try:
    objExcel.Workbooks.Open Server.MapPath("excelface.xls")
    I am the luckiest man in the world

  • #7
    Supreme Master coder! glenngv's Avatar
    Join Date
    Jun 2002
    Location
    Philippines
    Posts
    11,042
    Thanks
    0
    Thanked 251 Times in 247 Posts
    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.

  • #8
    New Coder
    Join Date
    Nov 2003
    Location
    Canada
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts
    They are all in the same folder. They are all in the network drive

  • #9
    Supreme Master coder! glenngv's Avatar
    Join Date
    Jun 2002
    Location
    Philippines
    Posts
    11,042
    Thanks
    0
    Thanked 251 Times in 247 Posts
    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".

  • #10
    New Coder
    Join Date
    Nov 2003
    Location
    Canada
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.

  • #11
    New Coder
    Join Date
    Nov 2003
    Location
    Canada
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I wonder how I can write content on specific location of excel sheet.

  • #12
    Supreme Master coder! glenngv's Avatar
    Join Date
    Jun 2002
    Location
    Philippines
    Posts
    11,042
    Thanks
    0
    Thanked 251 Times in 247 Posts
    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>


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •