• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1157
  • Last Modified:

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?
0
bsnyder
Asked:
bsnyder
  • 2
1 Solution
 
JWTCommented:
Well... this one is tough!

You are going to need to set up an Active X script.  Somewhere near the beginning of your DTS package put an Active X task.  Under the Tasks toolbar it sits at the top of the second column.

Make sure you already have your filename set up.  You can do it dynamically using global variables (best), or hard code it into your script (not very flexable).

Next you need to use the create object function in Vb Script.  Try this code:

Dim XLWB
Set XLWB = CreateObject("Excel.Application")

'--- Open the EXCEL Spread sheet ------------------------
call XlWB.Workbooks.Open("C:\TMP\EXCEL\SIMPLE.XLS",0)
XLWB.Sheets(XlSheet).Activate

After this it gets more difficult.  I do not know exactly how to access the Excel methods you might need, but try something like this:

   XlWB.Sheets("Tech").Select
   XlWB.ActiveSheet.Unprotect
   XlWB.Sheets("Apps").Select
   XlWB.ActiveSheet.Unprotect

You are going to have to play around with this to get what you want, but this will get you started.  You may also have to refer to the sheets by number - eg

XlWB.Sheets(1).Select

Good Luck

0
 
bsnyderAuthor Commented:
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?
0
 
CleanupPingCommented:
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.
0
 
bsnyderAuthor Commented:
I gave up on turning protection on/off, so moved all to a SQL Table and exprt back out from there.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now