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.
| Author |
Topic |
|
Maquis
Starting Member
25 Posts |
 Posted - 2005-07-18 : 12:29:25
|
| My client requires a password protected Excel spreadsheet containing data from the sql database to be ftp'd to them. I tried creating a template spreadsheet and setting the password, but then DTS wouldn't allow me to export to it (I get a "could not decrypt file" error. I didn't see an option for that in setting up the destination file from within the DTS package. Can this be done? TIA |
|
|
timmy
Master Smack Fu Yak Hacker
1242 Posts |
Posted - 2005-07-18 : 19:10:07
|
| Have you tried creating the spreadsheet, then use an ActiveX task to create a new password protected spreadsheet then copy the original contents into it?Alternately, you could do the whole thing using an ActiveX task using Excel objects. But you'll have to do all the data transformations yourself using code rather than the DTS transfer task.HTH,Tim |
 |
|
|
Maquis
Starting Member
25 Posts |
Posted - 2005-07-19 : 10:15:53
|
| I ended up dumping the data to a spreadsheet, then using Excel objects as stated above to copy the spreadsheet to a password protected sheet. Here's the snippet if anyone's interested: Dim objExcel Set objExcel = CreateObject("Excel.Application") objExcel.Workbooks.Open "d:\test_work.xls",,,,"" objExcel.Workbooks(1).SaveAs "d:\test.xls",,"test" objExcel.Workbooks.Close Set objExcel = Nothing |
|
|
|
igorblackbelt
Constraint Violating Yak Guru
407 Posts |
Posted - 2005-07-19 : 15:36:26
|
| This is the code I use to unprotect a excel file on a Activex/DTS.Function Main() Set objFSys = CreateObject("Scripting.FileSystemObject") if objfsys.fileexists("C:\Testfile.XLS") then Set xlApp = CreateObject("excel.application") xlApp.Visible = False Set xlwbs = xlApp.workbooks xlwbs.Open "C:\Testfile.XLS", , , , "Password" Set xlwb = xlwbs("File.xls") xlwb.SaveAs "C:\Testfile2.XLS", , "" xlwb.Close xlApp.Quit Set xlwb = Nothing Set xlwbs = Nothing Set xlApp = Nothing set objfstream = nothing set objfsys = nothing Main = DTSTaskExecResult_Success else Main = DTSTaskExecResult_Failure end ifEnd FunctionIgor. |
|
|
|
|
|
|
|
|
how to export to password protected spreadsheet?