Posted by Edgar on 04/24/06 17:28

The data is updated on a weekly basis. I mentioned I have a scalar
value. But I think I can fetch one row with 3 columns. The cells I
want to update on the Excel are: C4, C7 and C10.
The integer values are used in the excel formulas.

So far, I've tried using a DTS with activex to test just one particular
cell. But the code was updating the cell below C4 which I can't figure
out why. Here is the ActiveX code I'm using:

Function Main()

Dim appExcel
Dim newBook
Dim oSheet

Dim oPackage
Dim oConn

Set appExcel = CreateObject("Excel.Application")
Set newBook = appExcel.Workbooks.add
Set oSheet = newBook.Worksheets(1)

'Specify column names.
oSheet.Range("C4").Value = "D"


DTSGlobalVariables ("FileName").Value = "C:\MyExcel.xls"

With newBook
.SaveAs DTSGlobalVariables("FileName").Value
.Save
End With

appExcel.quit

'dynamically specify the destination Excel file

set oPackage = DTSGlobalVariables.parent

' connection 2 is to the Excel file

set oConn = oPackage.connections(2)
oConn.datasource = DTSGlobalVariables("FileName").Value

set oPackage = nothing
set oConn = nothing

Main = DTSTaskExecResult_Success

End Function

Again, thank you for all your input and/or directions.

Edgar J.

• Next in forum: SELECT and regular expression
• Prev in forum: Re: Order by in a INSERT INTO..SELECT
• Thread view: Re: Exporting sql data to specific cells

[Reply to this message]