February 13, 2006 at 2:05 pm
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
February 13, 2006 at 4:10 pm
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.
February 13, 2006 at 6:38 pm
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.
February 14, 2006 at 6:59 am
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!
February 14, 2006 at 7:18 am
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
February 14, 2006 at 7:23 am
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.
February 14, 2006 at 7:30 am
Maybe an IE setting?:
February 14, 2006 at 7:37 am
Thanks so much for all of the responses!
Erin
February 14, 2006 at 7:43 am
CSV is the way to go. Excel should recognize. BCP or File Destination Data Pumps work well.
February 14, 2006 at 8:21 am
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!
February 14, 2006 at 8:27 am
You are welcome. Try using \\[servername]\[foldersharename]\[filename].xls for your output file.
February 15, 2006 at 2:08 am
Re. Excel and NULL values, this might be of use: http://www.sqldts.com/default.aspx?254
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply