ActiveX...Help Please

  • Erin Onze

    SSC Enthusiast

    Points: 184

    I'm trying to create an ActiveX script to alter an Excel file and the task keeps bombing at CreateObject("Excel.Application").  Does Excel need to be installed on the server for this CreateObject to work?  Excel is not installed on the server this DTS resides on.

    TIA!

    Erin

  • John Rowan

    SSC Guru

    Points: 56440

    While I do not have specific experience with ActiveX and Excel, I would think that you need Excel installed on your server if you expect ActiveX to be able to create an Excel object.  When you call the CreateOjbect method, it will try to access Excel in order to find out how to create the object.  If it cannot find the Excel components, it will fail.  Is this a development server?  If so, just insall Excel and find out....If not, wait for another response from someone who may have more experience relating to ActiveX and Excel.

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

  • Mixa Nguyen

    Valued Member

    Points: 73

    I've encounter the same problem. So, i installed Excel on our test server and it works just fine. However, I still do not know the impact of installing Excel on SQL server. Some other methods that mentioned on previous posts refering to register .dlls insteadl of a full Excel installation. But MS does not "support" this method.

     

  • PhilM99

    SSCrazy

    Points: 2214

    The CreateObject function reads the registry of the machine on which it is running to find the classid and file location of the dll of the specified class name.

    So you would have had to install Excel to have the dll available and the registry entries made.

    Microsoft does not support the use of workstation products like Excel and Word in a server environment. That does not mean it won't work, it's just that they won't help you with any problems resulting from using the server like a workstation and perhaps hanging it up due to single-threading etc., or asking a user who is not even there to click OK.

    There are so many reasons CreateObject will fail that one could write a book about it. Most are permissions related...CreateObject needs to have permission to create objects.

    In Office 2003 and beyond, Word and Excel can read and write XML-based copies of their data. Although I have not done it with Excel, I have had much success writing small programs to inject data into Word templates, directly from SQL Server queries with the 'FOR XML RAW, ELEMENTS' clause. This does not require installing the desktop Office programs on a server, a practice which is not permitted in many production shops.

    You might want to revisit your strategy rather than spend time on your technical issue here!

  • Michael E. Baker

    Say Hey Kid

    Points: 676

    This sub works for me and we don't have Office installed on the server. Maybe it's rights as PhilM99 suggests.

    Sub CreateFile(strFileName))

    Dim myObject, myWorkBook

    Set myObject = CreateObject("Excel.Application")

    myObject.visible = True

    myObject.DisplayAlerts = False

    Set myWorkBook = myObject.WorkBooks.Add

    myWorkbook.Worksheets(1).Name = "Sheet1"

    myWorkbook.Worksheets(2).Delete

    myWorkbook.Worksheets(3).Delete

    myWorkbook.SaveAs strFileName

    myObject.Quit

    Set myWorkbook = Nothing

    Set myObject = Nothing

    End Sub

  • Antares686

    SSC Guru

    Points: 125444

    You to my knowledge have to have Excel installed and fully for proper support. Make sure your SQL agent service account has rights to use Excel, do not use Local System. However make sure you process does not try to perform more than one Excel option at a time especially on the same workbook as it will cause an error workbook open or Excel already running. Have not seen a way around this.

  • Michael E. Baker

    Say Hey Kid

    Points: 676

    Maybe an IE setting?:

    http://support.microsoft.com/kb/195826/EN-US/

  • Erin Onze

    SSC Enthusiast

    Points: 184

    Thanks so much for all of the responses!

    Erin

  • kevin mann

    Hall of Fame

    Points: 3894

    CSV is the way to go. Excel should recognize. BCP or File Destination Data Pumps work well.

  • Erin Onze

    SSC Enthusiast

    Points: 184

    B/c of the goofy header rows in this file whenever I try to get the file to a different format using DTS is drops the last column out and nulls out another column...grr! 

    PhilM99 - How do I verify that CreateObjects has permission to create objects?

    Michael, thanks for the code - this works for me if I kick the DTS off from my PC and output the file to my hard drive...if I try to output it to the server it bombs so it definitely seems to have trouble creating Excel files on the server.  I'm going to try writing it out to the file server.

    Thanks!

     

  • Michael E. Baker

    Say Hey Kid

    Points: 676

    You are welcome. Try using \\[servername]\[foldersharename]\[filename].xls for your output file.

  • mosaic-287947

    Ten Centuries

    Points: 1045

    Re. Excel and NULL values, this might be of use: http://www.sqldts.com/default.aspx?254

Viewing 12 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply