DTS package-Excel creation - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

DTS package-Excel creation

Author

Topic

barretld
Starting Member

22 Posts

Posted - 2005-04-22 : 10:06:16

Why would this code not create an excel file? I have excel installed on my pc. Trying to create an excel file from a dts package within SQL Server 2000 Enterprise Ed. The code is:

Function Main()
sExcelFile = "Test.xls"
sExcelPath = "C:\"
Set appExcel = CreateObject("Excel.Application")
Set wbkNew = appExcel.Workbooks.Open (sExcelPath & sExcelFile)
wbkNew.SaveAs sExcelPath & sExcelFile
wbkNew.Close
Set wbkNew = Nothing
Main = DTSTaskExecResult_Success
End Function

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2005-04-22 : 15:13:05

You need to add the workbook to the workbooks collection (rather than open an existing workbook).
Also, it's a good idea to declare your variables (and use option explicit) and don't forget to destroy any objects you create:



Function Main()
Dim sExcelFile
Dim sExcelPath
Dim appExcel
Dim wbkNew
sExcelFile = "Test.xls"
sExcelPath = "C:\"
Set appExcel = CreateObject("Excel.Application")
Set wbkNew = appExcel.Workbooks.Add
wbkNew.SaveAs sExcelPath & sExcelFile
wbkNew.Close
Set wbkNew = Nothing
Set appExcel = Nothing
Main = DTSTaskExecResult_Success
End Function

What's the requirement? Will you ever have to schedule this? Automating office applications on a server can be a major headache!

Mark

barretld
Starting Member

22 Posts

Posted - 2005-04-22 : 16:15:15

Thanks for your reply Mark. I'll give this a shot.

barretld
Starting Member

22 Posts

Posted - 2005-04-22 : 16:25:15

Adding the app is a good idea(my mistake), this works and accomplishes what I wanted to do-thanks!
What's the requirement? Will you ever have to schedule this? Automating office applications on a server can be a major headache!

Just a step in the process(dts creates data to place into an excel application). This will be automated on a server-how could this cause a headache(and how could this be checked)? Mark do you know of any good books that go into vbscript for dts packages?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources