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"
.ActiveConnection = cn
Set rstQueryFS = .Execute
End With
Set objXL = New Excel.Application
objXL.Workbooks.Add
strNextFile = GetNextFileName("C:\ACSFAL
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
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).
objWS.Cells.EntireColumn.A
Next intCol
rstQueryFS.MoveNext
intRow = intRow + 1
Loop
DoCmd.Hourglass False
objXL.Visible = True
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"
.ActiveConnection = cn
Set rstQueryFS = .Execute
End With
Set objXL = New Excel.Application
objXL.Workbooks.Add
strNextFile = GetNextFileName("C:\ACSFAL
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
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).
objWS.Cells.EntireColumn.A
Next intCol
rstQueryFS.MoveNext
intRow = intRow + 1
Loop
DoCmd.Hourglass False
objXL.Visible = True
ispaleny
DTS uses very different code. Run Import/Export Wizard and select Save DTS package, VB file on the last page.
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
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
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.
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.
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).
objWS.Cells.EntireColumn.A
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.AutoFi
End with
Leon
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).
objWS.Cells.EntireColumn.A
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.AutoFi
End with
Leon
ASKER
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.AutoFi
End with
I get a run time error as follows:
Run-time error '-2147319779(8002801d')
method 'CopyFromRecordset'
of object 'Range' failed
Any suggestions ?
With objWS
.Cells(2, 1).CopyFromRecordset rstQueryFS
.Columns(15).NumberFormat = "000-00-0000"
.Columns(18).NumberFormat = "#0.000"
.Columns(24).NumberFormat = "@"
.Cells.EntireColumn.AutoFi
End with
I get a run time error as follows:
Run-time error '-2147319779(8002801d')
method 'CopyFromRecordset'
of object 'Range' failed
Any suggestions ?
What is your Excel version? Try .Range("A2").CopyFromRecor
Leon
Leon
ASKER
I have Excel Version 2002.
I tried the following:
With objWS
.Range("A2").CopyFromRecor
.Columns(15).NumberFormat = "000-00-0000"
.Columns(18).NumberFormat = "#0.000"
.Columns(24).NumberFormat = "@"
.Cells.EntireColumn.AutoFi
End With
Unfortuantely I still get the run time error as follows:
Run-time error '-2147319779(8002801d')
method 'CopyFromRecordset'
of object 'Range' failed
I tried the following:
With objWS
.Range("A2").CopyFromRecor
.Columns(15).NumberFormat = "000-00-0000"
.Columns(18).NumberFormat = "#0.000"
.Columns(24).NumberFormat = "@"
.Cells.EntireColumn.AutoFi
End With
Unfortuantely I still get the run time error as follows:
Run-time error '-2147319779(8002801d')
method 'CopyFromRecordset'
of object 'Range' failed
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.