Axes values changes autmatically / textboxes lose formatting

[Anand Nichkaode]

Hi all,

We are using Excel for reporting in our organization.
Basically there is a Java web application which instantiates Excel 2007 with
an XLA. This XLA opens a Report Template(an .xls file, name supplied as
command line parameter). The Template has a data sheet where we have multiple
ranges and the Report sheet which shows the final report based on the data
from the DataSheet. The ReportSheet contains formulas to show the final
output. It could contain Charts/Graphs also. The data is fetched from SQL
server and is pasted on the Ranges. And finally when all the data is pasted
and Macro is run the Template is saved as a Final Report somewhere on the
Report Server.

The Excel runs in the background in all this process. There could be
multiple instances of Excel running on the same box. The instances run under
separate WindowStation.

In this process when we try to Paste Charts/Graphs as Pictures following
things are observed.
1. Axes values of the Graphs are changed i.e. if the Axes values are like 0,
5, 10, 15 then the values are changed like 0, 1, 2, 3, 4, 5, 6, 7, 8, 9,
10, 11, 12, 13, 14, 15
2. The Graph legends are kind of blurred
3. The Category(X) Axes are blurred.
4. Overall the Graph is blurred
In case of PieCharts
5. The values are placed on the chart itself whereas originally they are
outside the piechart.
6. The legends are not readable
7. The Piechart is overall blurred.

In this process it is also observed that some of the Text boxes loose their
formatting (font, bold, alignment) automatically. There is no code playing
with Text boxes in the macro or XLA

The code used to paste charts as pictures, when run separately in the Excel
application (not in the process) works perfectly and converts graphs to
pictures as it is, without any distortion, blurring or skewing.

Why?

Any help appreciated.

Thanks in advance.

 

[Andy Pope]

Hi,

When you say the Excel works in the application is that on the same server
as the xla runs or on a desktop machine?

Cheers
Andy

 

[Anand Nichkaode]

Yes, both the tests are performed on the same machine.

Updates on the Textbox issue. During my debugging I observed that the
Textbox loses its formatting as soon it is delinked from the formula. The
statement used to do so is given below.

"shp.DrawingObject.Formula = Empty"

Thx for the reply.

 

[Andy Pope]

Loss of formatting appears to be the default action.
I added a textbox to a chart and linked it to a cell. Applied some
formatting, color and boldness. I then manually cleared the formula link
and whilst the text remained all formatting was lost.
So again I think you need to store formatting information and then
re-apply it.

Cheers
Andy

 

[Anand Nichkaode]

Yes, we can even PickUp the formatting and Apply it again. It works, have
tested it.

Anyways....thanks and anything on Graph issue.

Thx in advance.

 

[Andy Pope]

Can't help on the chart -> picture problem. I can not replicate the problem.
But I do know that the CopyPicture method does not appear to differ when
using either xlBitmap or xlPicture. It always does xlPicture.

Cheers
Andy