SQL Agent and Active X script

Posted on 2014-08-13
Last Modified: 2014-08-13
Hello Experts Exchange
I have a ActiveX Script and I'm trying to get it to work in a SQL Agent task.

The code is the following;
strExcelFile = "F:\Graham Rock\OEE Scripting\MouldingBlue561.xls"
 strMacroName = "Macro1"
 Set objExcel = CreateObject("Excel.Application")
 Set objWB = objExcel.Workbooks.Open(strExcelFile)
 objExcel.Visible = True
 objWB.Application.Run strMacroName
 objWB.Close False

Open in new window

The script works when I run it as a VBS file.

But I get the following error when I try to save it to a SQL Agent task;

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

The command script does not destroy all the objects that it creates. Revise the command script. (.Net SqlClient Data Provider)

Can anyone help me to resolve the error please?


Question by:SQLSearcher
    1 Comment
    LVL 32

    Accepted Solution

    I would run the macro when the user opens the Excel file..

    But your error should vanish, when you free the pointers at the end of the script:

    Set objWB = Nothing
    Set objExcel = Nothing

    Open in new window

    Caveat: under what account does the job runs? objExcel.Visible = True is necessary for some operations in Excel, but some service accounts do not interact with the desktop, thus these actions cannot be executed succesfully.

    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    Join & Write a Comment

    A theme is a collection of property settings that allow you to define the look of pages and controls, and then apply the look consistently across pages in an application. Themes can be made up of a set of elements: skins, style sheets, images, and o…
    Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
    This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA.…
    This video is in connection to the article "The case of a missing mobile phone (". It will help one to understand clearly the steps to track a lost android phone.

    745 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

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now