asked on

ActiveX in DTS to Manipulate Excel Sheet

I have created a new DTS package in SQL 2000. I would like an ActiveX job to connect to an existing Excel spreadsheet, and if possible, remove sheet protection. The script would then update some cells, and then re-apply the protection. Is this possible. The Excel macro to do this is
Sheets("Tech").Select
ActiveSheet.Unprotect
Sheets("Apps").Select
ActiveSheet.Unprotect
Sheets("Tech").Select
Any ideas?

ASKER CERTIFIED SOLUTION

JWT

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

bsnyder

ASKER

I've made some progress on my own and here is what I have sor far. It does open the sheet, and turn off protection. I need to turn off sharing as well, but using xlsheet.Item.ProtectSharing causes an error in accessing the spreadsheet, and does not turn on / off sharing.

Function Main()
Dim xlApp
Dim xlBook
Dim xlSheet

Set xlApp = CreateObject("Excel.Application")
xlApp.Workbooks.open "sheetname.xls"
Set xlBook = xlApp.Workbooks(1)
Set xlSheet = xlBook.Worksheets("sheet1")
xlSheet.unprotect "courseware"

Set xlSheet = xlBook.Worksheets("sheet2")
xlSheet.unprotect "courseware"

xlBook.Save

xlBook.Close
xlApp.Quit

Set xlApp = Nothing
Set xlBook = Nothing
Set xlSheet = Nothing

Main = DTSTaskExecResult_Success
End Function

How can I turn off sharing?

CleanupPing

bsnyder:
This old question needs to be finalized -- accept an answer, split points, or get a refund. For information on your options, please click here-> http:/help/closing.jsp#1
EXPERTS:
Post your closing recommendations! No comment means you don't care.

bsnyder

ASKER

I gave up on turning protection on/off, so moved all to a SQL Table and exprt back out from there.