zimmer9
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:\LEXSPR G1.XLS")
objXL.ActiveWorkbook.SaveA s 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.A utoFit
Next intCol
rstQueryFS.MoveNext
intRow = intRow + 1
Loop
DoCmd.Hourglass False
objXL.Visible = True
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"
.ActiveConnection = cn
Set rstQueryFS = .Execute
End With
Set objXL = New Excel.Application
objXL.Workbooks.Add
strNextFile = GetNextFileName("C:\LEXSPR
objXL.ActiveWorkbook.SaveA
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).
objWS.Cells.EntireColumn.A
Next intCol
rstQueryFS.MoveNext
intRow = intRow + 1
Loop
DoCmd.Hourglass False
objXL.Visible = True
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.