August 8, 2005 at 2:48 pm
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
August 8, 2005 at 4:35 pm
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
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
August 8, 2005 at 6:07 pm
Just a side to what Phil said..you just need excel.dll registered..
Also you may want to check your permissions on the folder where the excel file resides..
HTH
Mathew J Kulangara
sqladventures.blogspot.com
August 9, 2005 at 8:53 am
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
August 9, 2005 at 9:04 am
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
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
August 9, 2005 at 9:10 am
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
September 6, 2005 at 12:53 am
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
September 8, 2005 at 9:39 am
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.
-- Stephen Cook
July 24, 2008 at 12:26 am
I also face a similiar problem with my DTS package.Can someone say whether the windows patch i had installed on the server could have lead to this error.
There was no excel installed on the machine before and the DTS ran fine.
January 27, 2010 at 3:21 am
To resolve the above issue and access the form, you need to follow these actions:
· Run the following command to register the DAO file:
regsvr32 Dao360.dll
· Replace Dao360.dll
· Remove the DAO Folder and Reinstall Microsoft Access 2000
However, if you still encounter any of the above error messages, even after taking above steps, then you need to reinstall MS Access or Office. To do so, you firstly need to delete the leftover files. In such situations, Access database might get corrupted because of mismatch of the DLL files or installation of an upgraded version of MS Access. To recover the data after corruption, you should use effective Access Repair tools.
David Poul
http://www.mssqldatabaserecovery.com
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply