Just at looking with what I've done before using this
the line

set oConn = oPackage.Excel_Export

I would assume that Excel_Export is the name of the step in your dts

if so then change it to:
set oConn = oPackage.connections("Excel_Export")

Nam

----- Original Message -----
From: "Phil Walter "
<mssqldba-ezmlmshield-x51269020.[Email address protected]
To: "LazyDBA Discussion" <[Email address protected]
Sent: Thursday, June 30, 2005 7:20 AM
Subject: DTS Export Dynamic Filename


> HI Gurus
>
>
>
> Could do with a little help,
>
>
>
> I am trying to export a table to excel using a DTS package and have the
> filename something like: 20050630comissions.xls with the date changing
> dynamicly.
>
>
>
> I have had a search on the internet for help and have come across a
> article which has given the following activeX Code
>
>
>
> '**********************************************************************
>
> ' Visual Basic ActiveX Script
>
> '***********************************************************************
> *
>
>
>
> 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 the column name in the Excel worksheet
>
>
>
> oSheet.Range("A1").Value = "type"
>
> oSheet.Range("B1").Value = "event_date"
>
> oSheet.Range("C1").Value = "centre_code"
>
> oSheet.Range("D1").Value = "ref"
>
> oSheet.Range("E1").Value = "learner"
>
>
>
> 'Specify the name of the new Excel file to be created
>
>
>
>
>
> DTSGlobalVariables("fileName").Value = "C:\" & Month(Now()) &
> "-" &
>
> Day(Now()) & "-" & Year(Now()) & "-" & Hour(Time()) & "-"
> &Minute(Time()) & "-" &
>
> Second(Time()) & ".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.Excel_Export
>
> oConn.datasource = DTSGlobalVariables("fileName").Value
>
>
>
> set oPackage = nothing
>
> set oConn = nothing
>
>
>
> Main = DTSTaskExecResult_Success
>
>
>
> End Function
>
>
>
> However apparently I have a syntax error, is anyone able to advise or
> give me any good sites to go too get more info.
>
>
>
> Thanks in advance
>
>
>
> Phil
>
>
>
> Phil Walter
> MI and Audit Manager
> Tribal Hubs Ltd
> First Floor
> Mansfield House
> Silver Street
> Taunton
> Somerset
> TA1 3DJ
>
> t: 01823 283 915
> m: 07747470461
> f: 01823 288124
> e: phil.[Email address protected] <mailto:phil.[Email address protected]
>
> Views: The views expressed in this electronic communication are those of
> the writer and are not, unless otherwise stated, the views of Tribal
> Hubs Ltd.
>
> Confidentiality: The addressee(s) of this electronic communication shall
> treat its contents in confidence and take all reasonable steps to ensure
> that the contents are not accessed or made available to any third party.
> No liability arising from unauthorised access to the information
> contained in this electronic communication whilst stored on any computer
> system or electronic storage media outside of its direct control
>
> Virus checking: All reasonable steps have been taken to ensure that this
> electronic communication and its attachments whether encoded, encrypted
> or otherwise supplied are free from computer virus.
>
>
>
>
>
> ---------------------------------------------------------------------
> TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
> Website : http://www.LazyDBA.com
> To unsubscribe: http://www.lazydba.com/unsubscribe.html
>
>




MS Sql Server LazyDBA home page