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
 To: Yuri Rubinov
  Where is Yuri Rubinov?
 Philadelphia
 Pennsylvania - United States
 Yuri Rubinov
 Tags
Subject: RE: Excel automation - Label alignment in a chart
Thread ID: 22121 Message ID: 22127 # Views: 5 # Ratings: 0
Version: Visual FoxPro 7 Category: Active X Controls & OLE Automation
Date: Wednesday, April 09, 2003 10:51:21 PM         
   


> Something like this:
> .....
> o.activesheet.ChartObjects(1).Activate
> o.activesheet.ChartObjects(1).chart.Axes(xlValue).Select
> o.Selection.TickLabels.Orientation = xlDownward
>
> ..........................
>
> > 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.

Thanks a lot Yuri, while your solution wasn't quite correct you lead me on the right path. I had just glossed over the TickLabels class. I was always looking for just labels. Here is what I did so the labels are slanted at a 45° angle and centered between the tick marks.
XLChart = XLSheet.chartObjects(1).Chart

WITH XLChart
	.Axes(1).tickLabels.Orientation = 45 && denotes 45°
	.Axes(1).tickLabels.Alignment = -4108 && xlCenter = 4108
ENDWITH

Thanks again...once again, foxite pulls through when most needed!

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