Execution of Excel on Server ?

Posted on 2005-04-28
Last Modified: 2010-05-18
I run a stored procedure and then I take the output into an Excel spreadsheet via a recordset using the following code.
Of course, the Stored Procedure runs very quickly. Then it takes a couple of minutes to write out the Excel file. Is there a way to have the Excel file generated on the server. It's just a thought. Can you think of a way I can speed up the application ?

Set com = New ADODB.Command
    With com
       .CommandType = adCmdStoredProc
       .CommandText = "dbo.procSpLN"
       .Parameters.Append .CreateParameter("RptYear", adInteger, adParamInput, 4, intYearSP)
       .ActiveConnection = cn
       Set rstQueryFS = .Execute
    End With
      Set objXL = New Excel.Application
      strNextFile = GetNextFileName("C:\LEXSPRG1.XLS")
      objXL.ActiveWorkbook.SaveAs strNextFile
      Set objWS = objXL.ActiveSheet
      objXL.ActiveSheet.Protect UserInterfaceOnly:=True
      For intCol = 0 To rstQueryFS.Fields.Count - 1
        Set fld = rstQueryFS.Fields(intCol)
        objWS.Cells(1, intCol + 1) = fld.Name
      Next intCol
      intRow = 2
      Do Until rstQueryFS.EOF
        For intCol = 0 To rstQueryFS.Fields.Count - 1
           objWS.Cells(intRow, intCol + 1) = _

        Next intCol
        intRow = intRow + 1
      DoCmd.Hourglass False
      objXL.Visible = True
Question by:zimmer9
    LVL 32

    Accepted Solution

    You need to look up the CopyFromRecordset method:

    CopyFromRecordset Method
    Copies the contents of an ADO or DAO Recordset object onto a worksheet, beginning at the upper-left corner of the specified range. If the Recordset object contains fields with OLE objects in them, this method fails.

    expression.CopyFromRecordset(Data, MaxRows, MaxColumns)
    expression    Required. An expression that returns a Range object.

    Data    Required Variant. The Recordset object to copy into the range.

    MaxRows    Optional Variant. The maximum number of records to copy onto the worksheet. If this argument is omitted, all the records in the Recordset object are copied.

    MaxColumns    Optional Variant. The maximum number of fields to copy onto the worksheet. If this argument is omitted, all the fields in the Recordset object are copied.

    Copying begins at the current row of the Recordset object. After copying is completed, the EOF property of the Recordset object is True.

    This example copies the field names from a DAO Recordset object into the first row of a worksheet and formats the names as bold. The example then copies the recordset onto the worksheet, beginning at cell A2.

    For iCols = 0 to rs.Fields.Count - 1
        ws.Cells(1, iCols + 1).Value = rs.Fields(iCols).Name
    ws.Range(ws.Cells(1, 1),  _
        ws.Cells(1, rs.Fields.Count)).Font.Bold = True
    ws.Range("A2").CopyFromRecordset rs
    LVL 30

    Assisted Solution

    If you use OPENROWSET in your stored procedure you can write directly to an Excel file on the server (assuming that the driver is installed)

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
    For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
    Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
    Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

    730 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

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now