ActiveX in DTS to Manipulate Excel Sheet

Posted on 2003-03-24
Medium Priority
Last Modified: 2013-11-30
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
Any ideas?
Question by:bsnyder
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2

Accepted Solution

JWT earned 500 total points
ID: 8227512
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:

Set XLWB = CreateObject("Excel.Application")

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

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:


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


Good Luck


Author Comment

ID: 8272494
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"

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

    Main = DTSTaskExecResult_Success
End Function

How can I turn off sharing?

Expert Comment

ID: 9275560
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 
Post your closing recommendations!  No comment means you don't care.

Author Comment

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

Featured Post

Enroll in August's Course of the Month

August's CompTIA IT Fundamentals course includes 19 hours of basic computer principle modules and prepares you for the certification exam. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question