January 17, 2010 at 12:48 pm
We are migrating to SQL Server 2005. We have several DTS packages that we are not ready to migrate to SSIS. They export data to Excel and then in a separate step in the package use ActiveX scripts to format the spreadsheets using excel commands via the object model.
When we schedule the job it throws the error: Unable to get the Open property of the Workbooks class
Is there something we are overlooking that we need to set in order to get this to work?
The code
Function Main()
dim x
dim max_used_row
set oPkg = DTSGlobalVariables.Parent
set cnExcel = oPkg.Connections("who_bought_what_yesterday")
set Excel_App = CreateObject("Excel.Application")
set Excel_Workbook = Excel_App.Workbooks.open( cnExcel.datasource )
error on the last line.
January 17, 2010 at 6:18 pm
We've had odd failures like this too. Seems that we get orphaned Excel sessions open on the server, and after too many are left open, DTS packages using Excel fail, with different errors.
If I kill those extra Excel sessions through task manager, the failed job runs fine.
Anyone know how to prevent the orphaned Excel sessions ?
January 17, 2010 at 8:14 pm
Unfortunately in our case this is not orphaned excel processes. This process never gets started. I am starting to believe that it might be the fact that when our sysadmin installed the office suite on the server, which is running windows 2008 64 bit, it installed in the program files (x86) folder instead of just program files. When we run the DTS from the legacy folder it runs fine. When we run it as a scheduled job with the DTSrun command it could be failing because it is looking for the excel components in the program files folder rather than program files (x86). I've asked the sysadmin to re-install office and have it install to the program files folder.
January 17, 2010 at 8:20 pm
What would make it look in 2 different locations ? Noting in the DTSrun specifies a file location.
June 30, 2010 at 9:46 am
Has the problem 'Unable to get the Open property of the Workbooks class' been resolved? If so, would you please let me know what method did you use?
I’m encountering the same problem when we upgrade the SQL server to 2008.
Thanks a lot,
Mary Ming Wang
Mary Ming Wang
June 30, 2010 at 12:07 pm
My system administrator is being able to fix this problem. He gives me a link:
Thanks my system administrator.
Mary Ming Wang
Mary Ming Wang
June 30, 2010 at 1:03 pm
The following folder need to be created:
C:\Windows\SysWOW64\config\systemprofile\Desktop
Mary Ming Wang
June 30, 2010 at 1:06 pm
Yes, that is what Microsoft recommended we do as well. Sorry I did not see your request until now, Mary. Regards
June 30, 2010 at 1:12 pm
Thank You,
Mary Ming Wang
December 5, 2011 at 7:13 am
I am having the same problem as you guys were having i think maybe you guys can help me . i am getting the error on this line "Set ExcelBook = ExcelApp.Workbooks.open(xlBookPath)" when i try and open an existing excel file . i have created the the desktop folder as suggested above but that did not solve my problem . i wander if there is anything else i might be missing or doing wrong or if there is some extra configuration i might need to make . my script runs on a windows server 2008 R2 Standard and i am using office 2003.
thank you all in advance
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply