Hi Folks

Stumped here.....
I am able to call and execute an Excel Macro from a DTS ActiveX task
object. However, I get a different output on the Excel sheet when I do
it this way, compared to if I ran the macro directly in Excel. I can't
seem to find out why. I have attached the code snippet below.

I basically need to search for an item description in column B, then
insert a new column called "Storage 1". The macro works flawlessly
when executed in the Excel environment but not from the DTS Package;
even if the DTS shows a successful completion of all tasks. The output
shows no new columns and with "Storage 1" under column B contents.

There is only 1 macro for the excel file and it is sitting in module1.
Can anyone throw me a bone?



Sub transform_macro()

'
' Macro1 Macro
' Macro recorded 2/10/2006 by Javathehutt
'

'
'format columns

Columns("B:B").Select
Selection.Insert Shift:=xlToRight

ActiveCell.FormulaR1C1 = _
"=IF(ISNUMBER(SEARCH(""Storage1"",RC[1])),""True"", """")"

Range("B1").Select
Selection.AutoFill Destination:=Columns("B"), Type:=xlFillDefault
ActiveWindow.SmallScroll Down:=-27

Columns("B:B").Select
Selection.Interior.ColorIndex = 0
Range("B1").Select
Selection.Interior.ColorIndex = 15
Range("B2").Select

'add column name event type

Range("B1").Select
Selection.ClearContents
ActiveCell.FormulaR1C1 = "Storage 1"
Range("B2").Select
Columns("B:B").Select


End Sub

Here is the ActiveX component FYI. This is run everyday.

'************************************************* *********************
' Visual Basic ActiveX Script
'************************************************* ***********************

Function Main()

'References to Excel
dim xl_app
dim xl_Spreadsheet

SET xl_app = CREATEOBJECT("Excel.Application")

SET xl_spreadsheet =
xl_app.Workbooks.Open("\\myfile\myExcel.xls")

'Run the macro



xl_app.Run("transform_macro")

xl_spreadsheet.Save

xl_spreadsheet.Close

xl_app.Quit
set xl_app = Nothing

Main = DTSTaskExecResult_Success

End Function