Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


Opening Excel File in ASP

Posted on 2003-03-11
Medium Priority
Last Modified: 2008-03-03
Im able to generate Excel File from a Recordset, its being save also onto the Destination. If I run on my machine, it runs perfectly fine.

Problem :
When I run on the Server, it doesn't run. It saves the file on the destination directorty but doesn't open.
Everything on the web server is saved on D:\inetpub\wwwroot\application

Urgent help needed :

Problem is may be with the Path :

  Option Explicit

  Class ExcelGen

    Private objSpreadsheet
    Private iColOffset
    Private iRowOffset
    Private strTmpDir

    Sub Class_Initialize()
      Set objSpreadsheet = Server.CreateObject("OWC.Spreadsheet")

      iRowOffset = 2
      iColOffset = 2

      strTmpDir = "C:\Inetpub\wwwroot\"
    End Sub

    Sub Class_Terminate()
      Set objSpreadsheet = Nothing   'Clean up

      'Remove out of date spreadsheets
    End Sub

    Public Property Let ColumnOffset(iColOff)
      If iColOff > 0 then
        iColOffset = iColOff
        iColOffset = 2
      End If
    End Property

    Public Property Let RowOffset(iRowOff)
      If iRowOff > 0 then
        iRowOffset = iRowOff
        iRowOffset = 2
      End If
    End Property

    Sub GenerateWorksheetFromRecordset(objrecordset, bolAutofitColumns)

      'Populates the Excel worksheet based on a Recordset's contents
      'Start by displaying the titles
      If objrecordset.EOF then Exit Sub

      Dim objField, iCol, iRow, iMaxColVal
      iCol = iColOffset
      iRow = iRowOffset
      objSpreadsheet.Cells(iRow-2, iCol+1).Value = "Sunday Allocation Report"
      objSpreadsheet.Cells(iRow-2, iCol+1).Font.Bold = True
      objSpreadsheet.Cells(iRow-2, iCol+1).Font.Underline = True

      For Each objField in objrecordset.Fields
        objSpreadsheet.Cells(iRow, iCol).Value = objField.Name
        objSpreadsheet.Cells(iRow, iCol).Font.Bold = True
        objSpreadsheet.Cells(iRow, iCol).Font.Underline = True
        iCol = iCol + 1
      Next 'objField

      iMaxColVal = iCol - 1

      'Display all of the data
      Do While Not objrecordset.EOF
        iRow = iRow + 1
        iCol = iColOffset

        For Each objField in objrecordset.Fields
              If IsNull(objField.Value) then
            objSpreadsheet.Cells(iRow, iCol).Value = ""
                  objSpreadsheet.Cells(iRow, iCol).Value = objField.Value
          End If

          iCol = iCol + 1
        Next 'objField


      If bolAutofitColumns then 'autofit columns
        For iCol = iColOffset to iMaxColVal
      End If
    End Sub    

    Function SaveWorksheet(strFileName)
      'Save the worksheet to a specified filename
      On Error Resume Next
      Call objSpreadsheet.ActiveSheet.Export(strFileName, 0)

      SaveWorksheet = (Err.Number = 0)
    End Function

    Sub StreamWorksheet()
      'Save the worksheet in a temporary file
      Dim strFileName, objFSO

      Set objFSO = Server.CreateObject("Scripting.FileSystemObject")
      strFileName = strTmpDir & objFSO.GetBaseName(objFSO.GetTempName) & ".xls"
      Set objFSO = Nothing

      if SaveWorksheet(strFileName) then
        Response.Redirect strFileName
      end if
    End Sub

    Private Sub CleanUpSpreadsheets()
       Dim objFS
       Dim objFolder
       Dim objFile

       set objFS = Server.CreateObject("Scripting.FileSystemObject")
       set objFolder = objFS.GetFolder(strTmpDir)
       'Loop through each file in the strTmpDir folder
       for each objFile in objFolder.Files
         'Delete Spreadsheets older than 10 minutes
         If DateDiff("n", objFile.DateLastModified, now) > 10 then
           objFS.DeleteFile strTmpDir & objFile.Name, True
         end if

       set objFolder = nothing
       set objFS = nothing
    End Sub

  End Class

<TITLE>Export To Excel File</TITLE>
<META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0">
<link href="JRLMIS3.css" type="text/css" rel="stylesheet">
<!-- #include file="adovbs.inc" -->
Response.ContentType = "application/vnd.ms-excel"

'Dim Conn, ObjCommand, ldDateFor, objRecordSet,ldDateFor, WhichDate
Session.LCID = 2057
Dim WhichDate, Conn, ObjCommand, ObjRecordSet, ldDateFor

If Len(WhichDate)=8 Then
      ldDateFor=CDate(Left(CStr(WhichDate),2) & "/" & Mid(CStr(WhichDate),3,2) & "/" & Right(CStr(WhichDate),4))
      ldDateFor=CDate(Left(CStr(WhichDate),1) & "/" & Mid(CStr(WhichDate),2,2) & "/" & Right(CStr(WhichDate),4))
End If

'Making Connection to Database & Creating the Recordset
set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open "provider=sqloledb;data source=JRLDataServer1;initial catalog=JRLEdiValidation;user id=sa;password=lincolnroad;"
set ObjCommand=server.CreateObject("ADODB.Command")
set objRecordSet = Server.CreateObject("ADODB.Recordset")
set objRecordSet.ActiveConnection = Conn
objRecordSet.CursorType = adOpenForwardOnly
objRecordSet.CursorLocation = adUseClient

With ObjCommand
      .ActiveConnection = Conn
      .CommandText = "spSundayReportExcel"
      .CommandType = adCmdStoredProc
      .Parameters.Append .CreateParameter("@DateFor", adDate, adParamInput)
end with

set objRecordSet = ObjCommand.Execute

  Dim objExcel
  Set objExcel = New ExcelGen

  objExcel.RowOffset = 3
  objExcel.ColumnOffset = 1
  'Dump the recordset contents to the spreadsheet and
  'AutoFit the columns
  objExcel.GenerateWorksheetFromRecordset objrecordset, True

  'Stream the spreadsheet to the user

  'Clean up!
  Set objExcel = Nothing

  Set objrecordset = Nothing

  Set objRecordSet = Nothing

Question by:radhakrishan1

Accepted Solution

mberumen earned 180 total points
ID: 8113386
my guess is that your problem is with your StreamWorksheet statement,

when you do it from your workstation you have direct access to the path, whereas web users do not.


I would try a different approach, perhaps a javascript section that opens the file in a new window or redirects the current page..

 Set objRecordSet = Nothing
<script language=javascript>


But then we would have problems if the file gets deleted before it has a chance to be opened by the browser...

how about deleting everything but the newly generated file?

so each user requesting a spreadsheet would delete the temp files created by other users

LVL 58

Expert Comment

ID: 9352460
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:

Accept Answer by mberumen

Please leave any comments here within the next seven days.
EE Cleanup Volunteer

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…
Kernel Data Recovery is a renowned Data Recovery solution provider which offers wide range of softwares for both enterprise and home users with its cost-effective solutions. Let's have a quick overview of the journey and data recovery tools range he…
Suggested Courses

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question