Hi
I am sure if this is possible.. I am trying to call an
ActiveX script based on the number of sheets in an Excel
File. The stpEnterLoop.ExecutionStatus =
DTSStepExecStat_Waiting
doesn't seem to be working within an Excel sheet loop..
Is there anything i am doing wrong or it is not possible
at all to execute another task from a loop in a dts
package.

Thanks for your help
Thanks
L



Option Explicit

Function Main()

Dim sActualLocationOfData
Dim Excel_Application
Dim Excel_WorkBook
Dim Excel_WorkSheet
Dim oPkg
Dim oConn
Dim bfound
Dim iSheetCounter
Dim SheetName

dim pkg
dim conTextFile
dim stpEnterLoop
dim stpEndLoop

set pkg = DTSGlobalVariables.Parent

sActualLocationOfData = "=Sales" & CStr
(DTSGlobalVariables("SheetNumber").Value) & "!" &
DTSGlobalVariables("DataLocation").Value

Set Excel_Application = CreateObject
("Excel.Application")

Set Excel_WorkBook =
Excel_Application.Workbooks.Open(DTSGlobalVariable s
("FileLocation").Value)


iSheetCounter = Excel_WorkBook.WorkSheets.Count

set stpEnterLoop = pkg.Steps
("DTSStep_DTSActiveScriptTask_3")

set stpEndLoop = pkg.Steps
("DTSStep_DTSActiveScriptTask_2")

if iSheetCounter > 0 then
For Each Excel_WorkSheet in
Excel_Workbook.WorkSheets

sActualLocationOfData = "="
&Excel_WorkSheet.Name & "!" & DTSGlobalVariables
("DataLocation").Value

msgbox sActualLocationOfData

stpEnterLoop.DisableStep = False

stpEnterLoop.ExecutionStatus =
DTSStepExecStat_Waiting

stpEndloop.DisableStep = True

Next

stpEndLoop.DisableStep = False
stpEndLoop.ExecutionStatus =
DTSStepExecStat_Waiting
stpEnterLoop.DisableStep = True
End If

Excel_WorkBook.Save

Excel_WorkBook.Close
Set Excel_WorkBook = Nothing
Excel_Application.Quit
Set Excel_Application = Nothing

Main = DTSTaskExecResult_Success
End Function