SQL Server 2000 DTS Package Active X Script Question

I'm trying to incorporate a piece of code that I got from a different post on this site.  It deletes rows from a worksheet within an excel workbook.  The piece of code that is giving me problems is:
line 10:  SetxlWb=xlApp.Workbooks.Open(DTSGlobalVariables("\\corp\report.xls").Value)
It says:
Error Source : Microsoft Excel
Error Description: Error Code 0
Error Source: Microsoft Excel
Error Description:  " Cannot be found.  Check the spelling of the file name and verify that the file location is correct.
 When I copy the path and put it into explorer the file opens right up.  Does anyone see something below that I'm doing incorrectly?  Thanks much for your help.  


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

Function Main()

Dim xlApp, xlWb, xlWs, pkg
Set pkg = DTSGlobalVariables.Parent

Set xlApp = CreateObject("Excel.Application")
SetxlWb=xlApp.Workbooks.Open(DTSGlobalVariables("\\corp\report.xls").Value)
Set xlWs = xlWb.Worksheets("New_Table")
xlWs.Rows.Delete
xlWb.Close True
set xlWs = Nothing
Set xlWb = Nothing
xlApp.Quit
Set xlApp = Nothing

Set pkg = Nothing

End Function
rsmucklesAsked:
Who is Participating?
 
frankyteeCommented:
>SetxlWb=xlApp.Workbooks.Open(DTSGlobalVariables("\\corp\report.xls").Value)
it looks like you are trying to pass the value of the variable rather than the variable itself.
either hardcode it
SetxlWb=xlApp.Workbooks.Open("\\corp\report.xls")

or if you want to pass the value from a DTS global variable, create the DTS variable say
"myExcelFile"
and give it the value "\\corp\report.xls"
or if you want to assign the variable from a parameter in your active x script:
DTSGlobalVariables("myExcelFile").Value = someParameter etc

then in your code:
SetxlWb=xlApp.Workbooks.Open(DTSGlobalVariables("myExcelFile").Value)
etc
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.