?
Solved

How to use DTS to export a recordset to Excel ?

Posted on 2005-05-03
8
Medium Priority
?
594 Views
Last Modified: 2012-08-13
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
0
Comment
Question by:zimmer9
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 13

Expert Comment

by:ispaleny
ID: 13917574
DTS uses very different code. Run Import/Export Wizard and select Save DTS package, VB file on the last page.
0
 
LVL 34

Expert Comment

by:arbert
ID: 13917584
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
 
LVL 13

Expert Comment

by:ispaleny
ID: 13917846
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 29

Expert Comment

by:leonstryker
ID: 13920742
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
 

Author Comment

by:zimmer9
ID: 13920946
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
 
LVL 29

Expert Comment

by:leonstryker
ID: 13921019
What is your Excel version? Try .Range("A2").CopyFromRecordset rstQueryFS

Leon
0
 

Author Comment

by:zimmer9
ID: 13921094
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
 
LVL 29

Accepted Solution

by:
leonstryker earned 2000 total points
ID: 13921251
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Suggested Courses

850 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