Analysis By Dimension Export to Excel

vikram7_dabas

I m using NAV 4.0 SP3 SQL.We have 20 users all users machine have Microsoft Business Solution installed.Some users are facing following error while doing export to excel in Analysis By Dimension :

This message is for C/AL programmers:
The call to member CreatePivotTable failed. Microsoft Office Excel returned the following message: The PivotTable field name is not valid. To create a PivotTable report, you must use data that is organized as a list with labeled columns. If you are changing the name of a PivotTable field, you must type a new name for the field.

Y some users are getting this error and y some users are not facing this error?Whats the sloution 4 this problem?

jannestig

Is it the Same Analysis View code they are getting this on?,

Do they all have the same version of Excel ETC?

vikram7_dabas

No Analysis view code is not same
All users have Microsoft Office 2007

vikram7_dabas

Soory,Analysis view code 4 one user is same and when I took another Analysis view code on that user's machine it is not showing me any error,it is working fine.And another user is getting same error in the same functionality which is given below:

This message is for C/AL programmers:
Could not invoke the member workbooks.The OLE control or Automation server returned an unknown error code.

jannestig

Its Pointing to an Excel install issue,

Has this routine been changed at all ? What about when you use the other analysis reports to export to excel or accounts Schedule.

What happens when you debug the error? The issue should be in Excel buffer i would think

vikram7_dabas

jannestig wrote:

Its Pointing to an Excel install issue,

Has this routine been changed at all ? What about when you use the other analysis reports to export to excel or accounts Schedule.

What happens when you debug the error? The issue should be in Excel buffer i would think

When I m taking another Analysis view code then export to excel functionality is running fine,but my another user got error which was described in earlier post and What do u mean by routine I didnt understand this?When I debug for following error:

This message is for C/AL programmers: The call to member CreatePivotTable failed. Microsoft Office Excel returned the following message: The PivotTable field name is not valid. To create a PivotTable report, you must use data that is organized as a list with labeled columns. If you are changing the name of a PivotTable field, you must type a new name for the field.

For this error it stoped in codeunit 424 in function "Export Data"
on line:
xlPivotTable := xlWorkSheet2.PivotTables('PivotTable1');

And, for another error,the dubugger stopped at following line:

xlApp.Workbooks.OpenText(FileName);

in function"Export Data".

jannestig

I meant any coding changes to the actual object Analysis view, excel export etc.

Is this a new installation or something that has suddenly just appeared? and if so what is it that has changed in the meantime.

It might be worth trying to delete the .zup of an affected user and see if that makes a difference.

jannestig

Actually i believe it could be because the amount of data your users are trying to put into the export.

Have them do smaller filters first then try again EG shorter time period.

vikram7_dabas

No change in any object related to this functionality.I have reduced date filter, then also getting same error.

jannestig

Well can you see anything that seperates the users that have no problems from the users that do have problems?

Nothing like remote desktop or they have an excel session already open?

Did you try deleting the .zup file of a user?

kimimp

We have experienced the same issue at a customer and this was our fix for it.
The Excel problem was related to an issue regarding the version on some of the user’s computer and with a problem in codeunit 424 in Nav 2009 which was using an unknown automation. I changed the version of excel library required and I modified the code in the “troublesome” function – to make it work – luckily this issue was solved for Nav 2009 SP1 and I got most of that functionality. We tested it on several users with different Excel versions and it worked.

vikram7_dabas

Fist of all, we never deleted the .zup file for any user.Suppose by mistake the .zup file of user A has been deleted then I have reinstalled Navision on the same user(A) machine.Then again it is showing me same error.So whats the solution of this problem?

vikram7_dabas

I didnt understand why this problem is only coming in one only 1 Analysis View Code(Say, P&L(which we have have made it)) not on other Analysis View Code.All users are getting same error on only 1 Analysis view code (i.e., P&L).We have decreased the date filter on that Analysis View Code on which we r getting error.
The error which all users are getting is given below:


This message is for C/AL programmers:

The call to member CreatePivotTable failed. Microsoft Office Excel returned the following message: The PivotTable field name is not valid. To create a PivotTable report, you must use data that is organized as a list with labeled columns. If you are changing the name of a PivotTable field, you must type a new name for the field. :-k

jannestig

So Create a New analysis code based on the old one and see if you get the same issue.

vikram7_dabas

I have created the new Analysis View code like earlier Analysis view code which r running fine.But then also same error is coming.

tewariankur

Hi..

I am also facing same issue at my user end. Please suggest if you have received any solution for the above problem. I have tried Installations of NAV,Excel.

Thanks,

Ankur

vikram7_dabas

Dear concern
I havnt got any solution from this?I got frustuate!!!!!!!!!!!!!!!!! ](*,)



[-o<

jannestig

Okay Since you have done some testing it is most likely to do with the Data being exported.

Can you do the following to Test.

1- Delete the Zup file of a user and try again.
2- Reinstall navision on the PC.
3 -Reverse the Column lines and try exporting
4 -Include a screenshoot of the Analysis view header, both tabs and body
5 - Check to see if any blank Header values are being created
6 - setup another analysis view similiar to the old one but different, Keep aadding in smaller increaments of changes until you eventually get your error message again that should help you identify what is causing the issue.

tewariankur

Hello...I have found out the reason why this error is coming...this is a excel error. It comes every time if we try to create PIVOT from data in excel sheet with one of the header blank. Now while exporting the data from NAV due to some reason data is not getting exported in correct format i.e. One of the header of column is blank. But still i am fighting why the data is not getting exported in a proper format.

Thanks

Ankur

jannestig

What should the header name be? is it due to non english language or too long a name etc?

tewariankur

Hi..

It is because the Header is blank. You may try to create a Pivot with Data in excel with on of the header Blank and the excel gives same error.


Thanks

Ankur

vikram7_dabas

Dear Concern
I have only 1 Analysis view code which is getting error and all other Analysis view code are running fine.All users r getting error on 1 Analysis view code only and on this Analysis view code no header is blank.

Analysis view code(which is geating error) is enclosed all other Analysis view code are also same like this,until it is getting error.

Egis

This problem occurs because of incorect encoding when excel opens text file with data headers. Check codeunit "Export Analysis View" and try to find code

//Populate excel sheet
xlApp.Workbooks._OpenText(FileName);
xlWorkSheet := xlApp.ActiveSheet;

If your Analysis View dimensions contains local characters (Lithuanian, Latvian, Czech) then sometimes excel opens text file with column headers using incorrect encoding (using default encoding which is wrong). Workaround I have found is to specify encoding you need explicitly
xlApp.Workbooks._OpenText(FileName,1257); //1257 - Baltic Windows
xlWorkSheet := xlApp.ActiveSheet;

DecBreen

Hi
I got the same error from a customer this morning.
The Analysis view code was called P&L
I took a gues that excel might not like the Ampersand so I changed the following code in codunit 424 and it sorted my issue.

// xlSheetName := CONVERTSTR(xlSheetName,' -+','___');
xlSheetName := CONVERTSTR(xlSheetName,' -+&','____');

manisharma31

Check this,
viewtopic.php?f=23&t=38346