Hello,

I have a DTS that calls an existing Excel workbook on a network then clears its contents and inserts data from a SQL Script in the DTS. When I try to run the DTS from enterprise manager, I get an error that workbook.xls already exist do you want to replace it? So I say yes and it runs. But the same DTS when run through a scheduled SQL Job gives this error:

ActiveX component can't create object: 'Excel.Application'

I imagine it's because Automation can't prompt the user for input to see if existing application can be replaced.  This is my guess, would like to know if I am correct? Do I need to delete the Excel workbook completely each time I run this DTS so it creates a new one each time? Following is my script and this is running on SQL Server 7:

'**********************************************************************
'  Visual Basic ActiveX Script
'************************************************************************

Function Main()
Dim Excel_Application
Dim Excel_WorkBook
Dim Excel_WorkSheet
Dim sFilename
Dim sSheetName
sFilename = "\\myhared\ftp\files\workbook.xls"
sSheetName = "Sheet1"

Set Excel_Application = CreateObject("Excel.Application")

' Open the workbook specified
Set Excel_WorkBook = Excel_Application.Workbooks.Open(sFilename)
Excel_WorkBook.Worksheets(1).Activate

'Excel_WorkBook.Worksheets(1).Range("A2", "T59999").ClearContents
Set objrange = Excel_WorkBook.Worksheets(1).Range("2:59999")
objrange.EntireRow.Delete
Excel_WorkBook.Save
Excel_WorkBook.Close
Excel_Application.Quit

Set Excel_Application = Nothing
Set Excel_WorkBook  = Nothing
Set Excel_WorkSheet = Nothing

Main = DTSTaskExecResult_Success
End Function

Please help if you can.

Thanks.

JN

Sorry if this is obvious, but is Excel installed on the SQL Server server itself (if you know what I mean)?

Your error is happening at the CreateObject line, before the .open line has been executed (I would guess).

Regards

Yes, the fact that when I run the DTS manually, I am able to run it so yes, Excel is installed and all accounts have proper permissions on the server/folder share. The only problem is that when I run it manually, it prompts me that the file already exists do you want to replace it and when I click yes, it proceeds with the DTS execution. But obviously, when this DTS is executed from an automated job there is no user to click Yes at the prompt asking if ok to replace the existing file so it failes with that massage that Excel.Application could not be created, because it never got an okay from the user to replace existing file. I want to know if I can automate this DTS and somehow skip or eliminate that prompt.

Thanks.

JN

I still think that the error is happening before processing gets to the

Set Excel_WorkBook = Excel_Application.Workbooks.Open(sFilename)

line.  To prove it one way or the other, try changing sFilename to something that doesn't exist and run it again.  If no error, your reasoning is correct.  If your reasoning is correct, just add in another step before this to delete the file first.

Regards

Yes, it's happening at following line, which waits for user input then executes. If it never got the user OK then it failes right there at that line!

Set Excel_Application = CreateObject("Excel.Application")

I actually returned the DTS to the developer and asked him to redesign the DTS by either using Transformation passing data to Excel connection or rewriting his ActiveX to delete Excel Workbook each time DTS is run then recreate it in the same DTS. So that should work.

Thanks.

JN

Hi Guys,

How do I register just the excel.dll?

History: I was having the same problems as JN, trying to the do the exact same function (clear shared xls file, and then insert new data) however I resolved the part when it asked for user input to save the file - this occurred because it couldnt save the file as the file was somehow still opened during the initial development stage which locked the file. I simply logged off and logged back on which destroyed any previous connections to that shared file.

So now that the Package is running fine, and not asking for me to save it, I faced my next problem. I could run the package manually from my machine, but when I created a job (DTSRun etc.. under the sql service account on the DBServer) I got the error: ActiveX component can''t create object: ''Excel.Application'  

Lol, I was stuck on this problem for a while until i happened to stumble upon this thread. So after reading, i tested this package again on a dev machine only to get the same problem. However this time, i knew how to troubleshoot the problem and after installing EXCEL on the dev machine, I was able to run this package automatically on the server under the normal sql service account.

So that comes to me $$$ next question?

How can do I go about registering only the DLL, so that I can create the Excel Object without having to install the full program on the Production machine.

thanks in advance.

'**************************************************

'My Code

'**************************************************

'**********************************************************************
'  Visual Basic ActiveX Script
'************************************************************************

Function Main()

 Dim xlsApp
 Dim xlsWorkBook
 Dim xlsSheet
 Dim NumRows
 Dim LastRow
 
 
 Set xlsApp = CreateObject("Excel.Application")
 Set xlsWorkBook = xlsApp.Workbooks.Open("\\fileserver\reports\MyReport.xls")
 
 LastRow = 1000 'arbitrary definition
 
 '******MySheet1 Sheet
 Set xlsSheet = xlsWorkBook.Sheets("MySheet1")
 NumRows = xlsSheet.UsedRange.Row - 1 + xlsSheet.UsedRange.Rows.Count
 xlsSheet.Range("A2:H" & LastRow).Clear
 xlsSheet.Range("A2:H" & NumRows).Delete
 
 
 'Close the workbook saving changes.
 Set xlsSheet = Nothing
 xlsWorkBook.Close True
 Set xlsWorkBook = Nothing
 xlsApp.Quit
 Set xlsApp = Nothing
 
 
 Main = DTSTaskExecResult_Success

End Function

You can try REGSVR32, it is for registering DLLs on NT-based Windows OSes.

http://www.ss64.com/nt/regsvr32.html

That web page has the syntax, and links to the MS docs and whatnot.