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/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

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/details.aspx?familyid=7554f536-8c28-4598-9b72-ef94e038c891&displaylang=en) but without improvement.

Any suggestions?

Thanks a lot!
sgovoniAsked:
Who is Participating?
 
Mark WillsConnect With a Mentor Topic AdvisorCommented:
Well, not sure what country you are a part of - use the drop down in the top right to select your region....  http://www.microsoft.com/sqlserver/2005/en/us/support-options.aspx

0
 
Mark WillsTopic AdvisorCommented:
Have you tried just a refreshall ?

DECLARE @FileName varchar(512),
        @status int,
        @Excel int,
        @WorkBook int,
        @cmd varchar(255),
        @msg varchar(255),
        @rsn varchar(255)


SET @filename = 'c:\ee\pivot-example2.xls'        -- excel filename

EXEC @status = sp_OACreate 'Excel.Application', @Excel output

IF @status=0 EXEC @status = sp_OAMethod @Excel, 'WorkBooks.Open', @WorkBook output, @FileName

IF @status=0 EXEC sp_OAMethod @Excel, 'Workbooks(1).refreshall'

IF @status <> 0
BEGIN
        exec sp_OAGetErrorInfo @excel, @rsn out, @msg out
        print @msg
END
 
EXEC sp_OAMethod @Excel, 'ActiveWorkbook.Save'
EXEC sp_OAMethod @Excel, 'Workbooks.Close'
 
EXEC sp_OADestroy @WorkBook
EXEC sp_OADestroy @Excel

GO
0
 
Mark WillsTopic AdvisorCommented:
There have been some issues with 64 bit OS from 64bit sql. Seem to recall a question not so long ago where it was rumoured that MS said it couldnt be done. So, might not actually get it to work - or - might need to ask MS about it.
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
sgovoniAuthor Commented:
Hi,

thanks for your answers. I try with RefreshAll method but I have got the same problem. 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 ideas ?

Thanks
Excel32.png
0
 
Mark WillsTopic AdvisorCommented:
Yeah - it is the 32 bit business that the 64bit SQL combined with 64bit OS that might cause some issues with the automation.

I think you might need to place a quick call to Microsoft.

Unfortunately I dont have both 64bit - I have kept 32bit SQL Server for very similar reasons (mainly ODBC issues), and fortunately do not have memory issues which might warrant 64bit.

On that other thread I was talking about, it was a linked server issue (not office), they ended up using a 32 bit instance of SQL as a "slave" and ran their stored procedures from that server, and linking to it from the production 64bit server - but that was a pretty heavy duty solution.

Now, it could be the office 2007 drivers, or the 64+64 combo - not exactly sure at this point. If you had the inclination, might suggest installing office 2003 if you had a "spare" machine to see if it is simply excel trying to run 32 bit mode.

Anyway, see what other experts can come up with - might be a solution - I am just unaware of one, and would recommend a call to Microsoft if no other experts show some signs of hope.
0
 
sgovoniAuthor Commented:
Hi Mark,

which forum advise me to write for - might be a solution - ? How do I contact Miscrosoft ?

Thanks!
0
 
sgovoniAuthor Commented:
The problem is still open...
0
 
sgovoniAuthor Commented:
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
0
 
Mark WillsTopic AdvisorCommented:
Thanks for sharing that with us. Good information. I am sure it will prove valuable for others (and me too)....
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.