i need your expertise help. i'm trying to export data by using dts package to an excel template. but the data must be exported to a specific cell of the excel sheet. for example, i have a tot_begin_amt and tot_end_amt and i want tot_beg_amt to export to cell a2 and tot_end_amt export to cell a3. i know i have to use ActiveX task to accomplish this. i don't the vb script to accomplish this. can you please shed some light. your help/suggestion is greatly appreciated.

In article <D88B5896-BB9E-4283-83AE-4B56FBB7A6DA (AT) microsoft (DOT) com>, Bingo wrote:
Excel has the concept of named ranges. Named Ranges appear to SQL Server as a table.

Now in your case are the results you want to use derived from the same source Query?

If they are then using DTS in the traditional sense will not work.

You are going to have to use an Active X Script task, ADO, The Excel Object Model and a bit of ingenuity.

Here is an example of finding what value is in a given cell


Function Main()

Dim e_app
Dim e_wbook
Dim e_wksheet
Dim ColCounter



Set e_app = CREATEOBJECT("Excel.Application")

Set e_wbook = e_app.Workbooks.Open(DTSGlobalVariables("gv_ExcelF ilename").Value)

Set e_wksheet = e_wbook.Worksheets(DTSGlobalVariables("gv_ExcelShe etNumber").Value)



msgbox e_wksheet.RANGE("A1:A1").Value


'Clean up

e_wbook.Save

e_wbook.Close

e_app.Quit

Set e_wbook = Nothing
Set e_app = Nothing



Main = DTSTaskExecResult_Success
End Function


Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
www.konesans.com - for all your consultancy needs