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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

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
Mark WillsTopic 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
System Programming

From novice to tech pro — start learning today.