Resize excel chartarea.width fails

Hi,

Can anybody tell me how to resize an excel chart.
I have tried to use the chartarea.width property but found this to be readonly.
The chartarea is always set to a size far larger than the one I need but I cannot override this setting.
continumAsked:
Who is Participating?
 
mvidasConnect With a Mentor Commented:
That sounds like a good workaround, assuming you've programmed some white space on the side to be cut off.

Something else that popped in my head last night was to add margins to the chart object.  It will make things a little tougher to get to exactly 400px wide, but with some trial and error you could probably get it just fine.  On my pc, after adding the following to the With objChart block, the .ChartWidth was 400
        .PageSetup.LeftMargin = 191
        .PageSetup.RightMargin = 191.5
        .PageSetup.TopMargin = 191
        .PageSetup.BottomMargin = 191.5

If you think an answer I've given has warranted points, you can click the 'Accept' button next to one of my comments to award and close the question.  If you'd just like to have the question closed and points refunded you can post a zero-point request in the Community Support forum ('Support' link in top-right of page)
Matt
0
 
mvidasCommented:
Hi continum,

If your chart is embedded on a worksheet (not a standalone chart tab), you could either scale it by a percentage (90% in my example here):

    YourWorksheetObject.Shapes("Chart 1").ScaleWidth 0.9, 0, 0 '0=msoFalse, 0=msoScaleFromTopLeft

Or even specify the width you want:

    YourWorksheetObject.Shapes("Chart 1").Width = 300


If your chart is a standalone tab, the chartarea can't be changed, but you can change the plotarea:

    YourChartObject.PlotArea.Width = 380

Matt
0
 
continumAuthor Commented:
Thanks Matt... can you view my code here and advise the best solution. The main thing is I get a clean un-distorted image to fit an area 400px wide....

Dim objExcel As Object = New Microsoft.Office.Interop.Excel.Application
objExcel.Visible = False
Dim objWorkbook As Microsoft.Office.Interop.Excel.Workbook = objExcel.Workbooks.Add()
Dim objWorksheet As Microsoft.Office.Interop.Excel.Worksheet = objWorkbook.Worksheets(1)
' ===
objWorksheet.Cells(0, 1) = "some text"
objWorksheet.Cells(1, 2) = somevalue
objWorksheet.Cells(1, 2).EntireColumn.NumberFormat = "General"
' ===
Dim tempimage As String = "mttempimage.jpeg"
' ===
Dim objRange = objWorksheet.UsedRange
objRange.Select()
Dim colCharts = objExcel.Charts
colCharts.Add()
Dim objChart As Microsoft.Office.Interop.Excel.Chart = colCharts(1)
Dim xlDataLabelsShowPercent As Integer = 3
' ===
With objChart
 .HasTitle = False
 .ChartType = 5
 .PlotArea.Fill.Visible = False
 .PlotArea.Border.LineStyle = -4142
 .ApplyDataLabels(xlDataLabelsShowPercent)
 .Legend.Position = -4107
 .Legend.Width = 400
 .Legend.Height = 100
 .Legend.Top = 270
 .Legend.Left = 0
 .PlotArea.Top = 60
 .PlotArea.Width = 200
 .PlotArea.Height = 150
 .PlotArea.Left = 100
End With
' ===
objChart.Export(tempimage, "jpeg", True)
' ===
objChart = Nothing
' === Destroy excel
objWorkbook.Saved = True
objWorkbook.Close()
objWorkbook = Nothing
objWorksheet = Nothing
objExcel.quit()
System.Runtime.InteropServices.Marshal.ReleaseComObject(objExcel)
objExcel = Nothing
GC.Collect()
GC.WaitForPendingFinalizers()
GC.Collect()

I have read articles where they mention resizing the shape object but I dont know how to add the chart object to the shape woth the worksheet values..

Please could you copy my example and recode with the chartarea being resizable.

Thanks

John
0
 
mvidasCommented:
Since you're creating a chart object rather than a shape (which is a chart embedded on a worksheet), you can't resize the whole thing.  
Have you tried just resizing the exported jpeg?  Seems to me like that would be the easier way to do it.

I can try and help clean your code up, but I'm not too familiar with vb.net so you may have to fine tune it a little (I know the excel object pretty well, just though vb6 and vba rather than .net).  Here is your With objChart block, with a couple modifications (added the last .Location line of the block, as well as the line after end with):

With objChart
 .HasTitle = False
 .ChartType = 5
 .PlotArea.Fill.Visible = False
 .PlotArea.Border.LineStyle = -4142
 .ApplyDataLabels (xlDataLabelsShowPercent)
 .Legend.Position = -4107
 .Legend.Width = 400
 .Legend.Height = 100
 .Legend.Top = 270
 .Legend.Left = 0
 .PlotArea.Top = 60
 .PlotArea.Width = 200
 .PlotArea.Height = 150
 .PlotArea.Left = 100
 .Location Where:=2, Name:=objWorksheet.Name '2=xlLocationAsObject
End With
objWorksheet.Shapes(Mid(objExcel.ActiveChart.Name, Len(objWorksheet.Name) + 1)).Width = 400

The reason I used:
 objWorksheet.Shapes(Mid(objExcel.ActiveChart.Name, Len(objWorksheet.Name) + 1)).Width = 400
Is because if I just used "objExcel.ActiveChart.Name", it would return something like "Sheet1 Chart 1".  Using mid and len there will cause it to return just "Chart 1", which is what the shapes collection uses.  Honestly, you could probably even just use
 objWorksheet.Shapes(1).Width = 400
I just coded it like I did to be safe.  Try it with the index of 1, based on your code it should always be that.


Also, though it doesn't make much of a difference, rather than use:

 objWorkbook.Saved = True
 objWorkbook.Close()

You should just be able to use:

 objWorkbook.Close(False)

As the first argument of the .Close method is SaveChanges.  As I said, probably doesnt make much of a difference, but it can't hurt.

Matt
0
 
continumAuthor Commented:
Hi Matt,

I have managed to work round the problem by formatting the chart area to the size I want and then dropped my oversized inage onto my PDF document with the image overlapping to the right of the doc, this appears to work fine...

The image tends to distort if I try and scxale the image after it has been created.

Thanks Anyway.

How do I close this question and award you the points?
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.