Link to home
Start Free TrialLog in
Avatar of zimmer9
zimmer9Flag for United States of America

asked on

Execution of Excel on Server ?

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
      objXL.Workbooks.Add
      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) = _
              rstQueryFS.Fields(intCol).Value
           objWS.Cells.EntireColumn.AutoFit

        Next intCol
        rstQueryFS.MoveNext
        intRow = intRow + 1
      Loop
      DoCmd.Hourglass False
       
      objXL.Visible = True
ASKER CERTIFIED SOLUTION
Avatar of Brendt Hess
Brendt Hess
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial