?
Solved

Resize excel chartarea.width fails

Posted on 2006-05-18
5
Medium Priority
?
2,013 Views
Last Modified: 2011-10-03
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.
0
Comment
Question by:continum
  • 3
  • 2
5 Comments
 
LVL 35

Expert Comment

by:mvidas
ID: 16709904
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
 

Author Comment

by:continum
ID: 16710093
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
 
LVL 35

Expert Comment

by:mvidas
ID: 16710745
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
 

Author Comment

by:continum
ID: 16718261
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
 
LVL 35

Accepted Solution

by:
mvidas earned 1500 total points
ID: 16718604
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

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to remove superseded packages in windows w60 or w61 installation media (.wim) or online system to prevent unnecessary space. w60 means Windows Vista or Windows Server 2008. w61 means Windows 7 or Windows Server 2008 R2. There are various …
When we want to run, execute or repeat a statement multiple times, a loop is necessary. This article covers the two types of loops in Python: the while loop and the for loop.
The viewer will learn how to implement Singleton Design Pattern in Java.
The goal of the video will be to teach the user the concept of local variables and scope. An example of a locally defined variable will be given as well as an explanation of what scope is in C++. The local variable and concept of scope will be relat…
Suggested Courses
Course of the Month14 days, 12 hours left to enroll

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question