SQL Server Performance Forum – Threads Archive
Creating Excel Sheet
Hi there, I am trying to create a dts package which will save the results in a excel sheet. Is there a way that the dts create a new excel file itself every time it run. ThanksDanny
You can sue SP_OAxxx procedure to do that….
or
you can keep one blank excel sheet and copy it as needed using xp-cmdshell…
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS†with no warranties for accuracy.
Be aware if you are using office automation then ensure to have upto date service packs on SQL server and client side too including on Office pack. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
Thanks for your suggestions, I used a activeX script which will create a new excel sheet each time the DTS package will run. strFileName = "C:ScriptsWeeksBirthDay.xls" Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True Set objWorkbook = objExcel.Workbooks.Add()
objWorkbook.SaveAs(strFileName) objExcel.Quit Please let me know if this method have any negetive points. Thanks
Danny
If you are getting expected results with no issues, then why not. Go for it, I don’t see any specific reason. IMHO it is better to use Activex than SP_OAxx types. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
I agree with Saya, using ActiveX instead of sp_oaXX procedures…
Most of the times I don’t use either of them…
I copy one sample file and use that one to make new one using nt copy command….
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS†with no warranties for accuracy.
]]>