Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Jared Baszler
  Where is Jared Baszler?
 Brookings
 South Dakota - United States
 Jared Baszler
 Tags
Subject: Excel automation - Label alignment in a chart
Thread ID: 22121 Message ID: 22121 # Views: 27 # Ratings: 0
Version: Visual FoxPro 7 Category: Active X Controls & OLE Automation
Date: Wednesday, April 09, 2003 8:45:05 PM         
   


Alright, I feel I've made some pretty big stides in learning how to automate Excel but I have one small problem that would make my day if I could figure it out with your help if need be. I've thrown some data into a sheet and then have automated creating a chart and it looks just fine except for one thing. The labels on the x-axis are abbreviated test names and if too many tests need to fit on the x-axis then Excel will only display every other test name. What I need to do is change the alignment of the labels to either 45° or 90° (vertical). I cannot seem to find the property that will let me do this. You can obviously do this manually under the "Format Axis" property when double-clicking on the x-axis (this allows all the labels to be displayed). I just need to be able to do this via VFP code. Please let me know if anyone has the answer for this one. Thanks.

Here is the code I'm using to create the chart.

* Get the range we want to graph.
rangeString = startColumnLetter2 + ALLTRIM(STR(startRowNumber - 1)) + ":" +;
			   endColumnLetter + ALLTRIM(STR(startRowNumber - 1)) + "," +;
			   startColumnLetter2 + ALLTRIM(STR(totalRowNumber)) + ":" +;
			   endColumnLetter + ALLTRIM(STR(totalRowNumber))
			   
* Set the chart title with the date of the report.
chartTitle = "Effective Date: " + dateBegin + " - " + dateEnd
* Determine the width of the graph - first find out how many tests.
numberOfTests = endColumnNumber - startColumnNumber + 1
* Set the width - 25 pixels per test with a base width of 200.
chartWidth = 200 + (numberOfTests * 25)
* Check for chart width above 800, if so then it will be too big for page.
IF (chartWidth > 700)
	chartWidth = 700
ENDIF && (chartWidth > 700)
* Determine where the top of the chart will be - 47 rows on a page.
chartTop = FLOOR(((totalRowNumber + 2) / 47))
chartTop = (((chartTop + 1) * 47) + 1) && First row on next page after data table.
* Now get the pixel coordinate.
chartTop = XLSheet.Cells(chartTop, 1).Top

* Add a chart to the sheet - place it 2 rows below the totals row.
XLsheet.ChartObjects.Add(0, chartTop, chartWidth, 300)
XLChart = XLSheet.chartObjects(1).Chart

WITH XLChart
	* Automate chart wizard to create the wizard with the data specified above.
	.chartWizard(XLSheet.Range(rangeString), 11, 4, 1, 1, 1, 0, chartTitle, "Test Name",;
				 "Number Of Tests", "")
	* Clustered column
	.chartType = 51
	* Show the value at the top of the bar.
	.applyDataLabels(2)
ENDWITH 

Jared Baszler
Computer Programmer
Mid-West Seed Services, Inc.


COMPLETE THREAD

Excel automation - Label alignment in a chart Posted by Jared Baszler @ 4/9/2003 8:45:05 PM
RE: Excel automation - Label alignment in a chart Posted by Yuri Rubinov @ 4/9/2003 10:19:54 PM
RE: Excel automation - Label alignment in a chart Posted by Jared Baszler @ 4/9/2003 10:51:21 PM
RE: Excel automation - Label alignment in a chart Posted by Yuri Rubinov @ 4/10/2003 3:02:44 PM