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.




MS Sql Server LazyDBA home page