Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Refresh PivotTables in MS Excel 2007 with OLE Automation Expand / Collapse
Author
Message
Posted Thursday, May 6, 2010 4:03 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, August 29, 2012 5:09 PM
Points: 42, Visits: 194
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(link: http://www.ugiss.org/Content/Article/Aggiornare-una-tabella-pivot-di-Microsoft-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

The stored procedure does not work with:

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

The pivot table connecting to SQL Server with an ODBC connection (32 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 will not be completed, the only way (to stop it) is to interrupt forcefully the task "EXEC.EXE *32".

When I forced to close the task, in SSMS 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/details.aspx?familyid=7554f536-8c28-4598-9b72-ef94e038c891&displaylang=en) but without improvement.

Any suggestions?

Thanks a lot!
Post #917482
Posted Tuesday, June 1, 2010 4:43 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, August 29, 2012 5:09 PM
Points: 42, Visits: 194
I am finally able to update a PivotTable test that resides on a Microsoft Excel worksheet.

The last test I've run on a PC with Windows 7 Professional x64 x64 SS2008 and Excel 2007.

After install the updated Office 2007 Data Connectivity Components http://www.microsoft.com/downloads/details.aspx?familyid=7554F536-8C28-4598-9B72-EF94E038C891&displaylang=en I have recreated the PivotTable and test... but this time I checked, as a data source, type "Microsoft Office Data Connection" and not the ODBC 32-bit...

Read the complete thread (italian language) at this link: http://community.ugiss.org/forums/t/2787.aspx

Thanks
Post #931022
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse