Refresh PivotTables with OLE Automation and MS Excel 2007

Hi,

I need to update a pivot table that resides on a Microsoft Excel worksheet.

I created a stored procedure (dbo.USP_DMO_EXCEL_Pivot_RefreshTable: http://www.ugiss.org/Content/Article...oft-Excel.aspx) that uses OLE Automation (sp_OA*) to refresh, every night (without open the Excel file), the pivot table data in Microsoft Excel worksheet.

The stored procedure works well in this enviroment:

- OS Windows Server 2003
- Office 2003
- SQL Server 2008

But it does not work with:

- OS Windows Server 2008 64-bit or Windows 7 64-bit
- Office 2007
- SQL Server 2008 64-bit

The Excel file will open correctly, bit I get an error during execution method RefreshTable in the following line of code:

exec sp_OAMethod @objWorkSheet PivotTables('Pivot_Name').RefreshTable

The execution of stored procedures to stop crashes and I have to forcefully close Excel task "Excel *32" on server in which I performed.

When forced to close the task, I get the following error:

-2146827284 Unable to find Microsoft Office Excel PivotTables property for the class Worksheet. C:\Program Files (x86)\Microsoft Office\Office12\1040\0 XLMAIN11.CHM
Message 50000, Level 16, State 1, Server <name> USP_DMO_Excel_Pivot_RefreshTable procedure, line 369
Whilst Error: Return object workbooks, Could not find property PivotTables for Class Worksheet

I also installed the 2007 Office System Driver: Data Connectivity Components (http://www.microsoft.com/downloads/d...displaylang=en) but without improvement.

I have got also try with RefreshAll method, but it doesn't work too.

I also try to execute SSMS as administrator with the options "run as administrator" but I have got the same error.

In the task manager the Excel task appears like in the attached image.

Any suggestions?

Thanks a lot!