Solved

SQL Server 2000 DTS Package Active X Script Question

Posted on 2008-06-25
1
504 Views
Last Modified: 2013-11-30
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
0
Comment
Question by:rsmuckles
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 19

Accepted Solution

by:
frankytee earned 500 total points
ID: 21870916
>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

Featured Post

Why You Need a DevOps Toolchain

IT needs to deliver services with more agility and velocity. IT must roll out application features and innovations faster to keep up with customer demands, which is where a DevOps toolchain steps in. View the infographic to see why you need a DevOps toolchain.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question