How to use DTS to export a recordset to Excel ?

I created an Access Application in which I run a Stored Procedure as follows:

Do you know how I would modify this code to use DTS to export the recordset to Excel ?

Set com = New ADODB.Command
    With com
       .CommandType = adCmdStoredProc
       .CommandText = "dbo.procFlACS"
       .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:\ACSFALL1.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
           If intCol = 15 Then
              objWS.Cells(intRow, intCol + 1).NumberFormat = "000-00-0000"
           End If
           If intCol = 18 Then
              objWS.Cells(intRow, intCol + 1).NumberFormat = "#0.000"
           End If
           If intCol = 24 Then
              objWS.Cells(intRow, intCol + 1).NumberFormat = "@"
           End If
           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
zimmer9Asked:
Who is Participating?
 
ispalenyCommented:
DTS uses very different code. Run Import/Export Wizard and select Save DTS package, VB file on the last page.
0
 
arbertCommented:
Fist, I would go into enterprise manger, right click on your database, choose all tasks, Export Data.

Then, choose your database as the source, click next, choose Excel as the destination.  Give it a file name.  Click next.

Choose, "Use Query to Specify the data to transfer".  This is where you paste your proc in.

Continue through the wizard and save your package.

At the end, you can go into the package, edit it, add a dynamic properties task so you can pass the parm to the query and change it each time.

Brett
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
ispalenyCommented:
DTS in MSSQL 2000 is a bunch of independent parts:
DTS ActiveX engine ... major program
DTS runtime utility ... commandline
SQL Server Agent ... scheduler
msdb SQL Server respository ... relational log and package storage
DTS package OLE files ... packages in files

You must choose, which are necessary for your Excel export.

If you want to save the file locally or over network, you need to run SQL Server Agent under Windows user account with appropriate rights, and give users right to schedule or enable task in the SQL Server Agent.

Or run DTS locally, then you need to install at least ActiveX part of DTS on all exporting clients.
0
 
leonstrykerCommented:
BTW, if you are using Excel 2000 or higher replace:

    Do Until rstQueryFS.EOF
        For intCol = 0 To rstQueryFS.Fields.Count - 1
           If intCol = 15 Then
              objWS.Cells(intRow, intCol + 1).NumberFormat = "000-00-0000"
           End If
           If intCol = 18 Then
              objWS.Cells(intRow, intCol + 1).NumberFormat = "#0.000"
           End If
           If intCol = 24 Then
              objWS.Cells(intRow, intCol + 1).NumberFormat = "@"
           End If
           objWS.Cells(intRow, intCol + 1) = _
              rstQueryFS.Fields(intCol).Value
           objWS.Cells.EntireColumn.AutoFit
        Next intCol
        rstQueryFS.MoveNext
        intRow = intRow + 1
      Loop

With this:
 
    With objWS
        .Cells(2, 1).CopyFromRecordset rstQueryFS
        .Columns(15).NumberFormat = "000-00-0000"
        .Columns(18).NumberFormat = "#0.000"
        .Columns(24).NumberFormat = "@"
        .Cells.EntireColumn.AutoFit
    End with

Leon
0
 
zimmer9Author Commented:
I tried using the following:

With objWS
        .Cells(2, 1).CopyFromRecordset rstQueryFS
        .Columns(15).NumberFormat = "000-00-0000"
        .Columns(18).NumberFormat = "#0.000"
        .Columns(24).NumberFormat = "@"
        .Cells.EntireColumn.AutoFit
End with

I get a run time error as follows:

Run-time error '-2147319779(8002801d')
method 'CopyFromRecordset'
of object 'Range' failed

Any suggestions ?
0
 
leonstrykerCommented:
What is your Excel version? Try .Range("A2").CopyFromRecordset rstQueryFS

Leon
0
 
zimmer9Author Commented:
I have Excel Version 2002.

I tried the following:

With objWS
        .Range("A2").CopyFromRecordset rstQueryFS
        .Columns(15).NumberFormat = "000-00-0000"
        .Columns(18).NumberFormat = "#0.000"
        .Columns(24).NumberFormat = "@"
        .Cells.EntireColumn.AutoFit
End With

Unfortuantely I still get the run time error as follows:

Run-time error '-2147319779(8002801d')
method 'CopyFromRecordset'
of object 'Range' failed
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.