Very Stuck ... vb OLE to excel chart question

[terry22]

Hello,

I've been trying for hours and hours (since 5am) to solve this and the only thing I've accomplished is changing the chart type from what I suppose is the veritical/column default chart type to a bar/horizontal chart. I've googled it, search ms site and other forums - checked out the object library ... I just have not been successful. I throw myself at your feet in desperation ... and plant my face in the keyboard in frustration! Please help, if you can.

I am working with an example from MS website:

http://support.microsoft.com/default.aspx?scid=kb;en-us;142387

I am trying to accomplish the following:
1. Label the y-axis points, say "Label 1", "Label 2" ...
"Label 10" -- Currently, it prints out 1-10 - showing only the odd numbers ie: 1-9.

2. Label the Legend. Currently it reads "Series1" and "Series2"

3. Add a title to the Chart. Currently, there isn't a title in the Chart.

Here is the code w/ setup info plus the ONE part I did
manage to get working -- less all the other non-working junk I messed it up with:
------------------------------------------------------
' HOWTO
' Create Excel Chart w/OLE Automation from Visual Basic
' View products that this article applies to.
' This article was previously published under Q142387
' SUMMARY
' This article illustrates how you can use Automation in a
' Visual Basic program to create an Excel workbook that
' ontains a chart embedded on a worksheet.
' MORE Information
' Steps to Create Example Program
' Start a new project in Visual Basic. Form1 is created by default.
' Add a command button (Command1) to Form1.
' Add the following code to the Command1_Click event procedure:
'
' ** Add project reference: Microsoft Excel Object Library **
'
' Press the F5 key to run the program, and click the command button.
'
' Results: Microsoft Excel starts, and a new workbook is created.
' Random data is added to cells A1:J2 and a chart is embedded on
' the first worksheet. The embedded chart uses the random data for its source.

Private Sub Command1_Click()
On Error GoTo ErrorLog
Dim oXL As Object ' Excel application
Dim oBook As Object ' Excel workbook
Dim oSheet As Object ' Excel Worksheet
Dim oChart As Object ' Excel Chart

Dim iRow As Integer ' Index variable for the current Row
Dim iCol As Integer ' Index variable for the current Row

Const cNumCols = 10 ' Number of points in each Series
Const cNumRows = 2 ' Number of Series


ReDim aTemp(1 To cNumRows, 1 To cNumCols)

'Start Excel and create a new workbook
Set oXL = CreateObject("Excel.application")
Set oBook = oXL.Workbooks.Add
Set oSheet = oBook.Worksheets.Item(1)

' Insert Random data into Cells for the two Series:
Randomize Now()
For iRow = 1 To cNumRows
For iCol = 1 To cNumCols
aTemp(iRow, iCol) = Int(Rnd * 50) + 1
Next iCol
Next iRow

oSheet.Range("A1").Resize(cNumRows, cNumCols).Value = aTemp

'Add a chart object to the first worksheet
Set oChart = oSheet.ChartObjects.Add(50, 40, 300, 200).Chart

' how to label y-axis ?

With oChart
' how to name/label Series 1 and Series 2 ?
.Type = xlBar 'the Only thing I've been able to make work!
' how to put in Chart Title?
End With

oChart.SetSourceData Source:=oSheet.Range("A1").Resize(cNumRows, cNumCols)

' Make Excel Visible:
oXL.Visible = True

oXL.UserControl = True

Exit Sub
ErrorLog:
MsgBox Err.Description & Chr(13) & " " & Chr(13) & Err.Number, vbExclamation
End Sub
------------------------------------------------------

Thanks so much for your time and effort!

 

[terry22]

Hello Again ...

I think I may have it!

I will post back in a bit.

 

[terry22]

Hi, Well I basically have it. I still have a problem though. It is with the alignment. I am setting the .datalabel.left = 50 to try and get all labels on the chart to align left ... but they are not aligned and end up staggering a bit further to the left with each successive label that gets put in place ... And, when I set it to 0, well then they ARE aligned left BUT, they are now too far to the left and hanging off of the series and charting area.

Any suggestions on this would sure be appreciated! Thanks so much.

This is what I have now ... add a command button to a form, and the project reference: Microsoft Excel Object Library plus this code:

--------------------------------------------------------
Private Function CreateChart()

Dim xlApp As Excel.Application
Dim xlWrkbk As Excel.Workbook
Dim xlChartObj As Excel.Chart
Dim xlSourceRange As Excel.Range
Dim xlColPoint As Excel.Point
Dim xlWrkSheet As Excel.Worksheet ' Excel Worksheet
Dim iRow As Integer ' Index variable for the current Row
Dim iCol As Integer ' Index variable for the current Row
Const cNumCols = 10 ' Number of points in each Series
Const cNumRows = 2 ' Number of Series
x = 1
On Error GoTo Err_CreateChart

ReDim aTemp(1 To cNumRows, 1 To cNumCols)

'Start Excel
' Create a Microsoft Excel object.
Set xlApp = CreateObject("Excel.Application")

' create a new workbook
Set xlWrkbk = xlApp.Workbooks.Add

Set xlWrkSheet = xlWrkbk.Worksheets.Item(1)

' Create a new chart.
Set xlChartObj = xlApp.Charts.Add

' Insert data into Cells for the two Series:
For iRow = 1 To cNumRows

For iCol = 1 To cNumCols
aTemp(iRow, iCol) = x + iCol
Next iCol

x = x + 1
Next iRow

xlWrkSheet.Range("A1").Resize(cNumRows, cNumCols).Value = aTemp

' Format the chart.
With xlChartObj

' Specify chart type
.ChartType = xl3DBarClustered

' Set the source and range of the chart.
.SetSourceData Source:=xlWrkSheet.Range("A1").Resize(cNumRows, cNumCols)

' Create and set the title; set title font.
.HasTitle = True

With .ChartTitle
.Characters.Text = "My Chart Title"
.Font.Size = 18
End With

' Delete the label at the far left of the y-axis.
.Axes(xlCategory).Delete
.HasLegend = True

' format the series
With .SeriesCollection(1)
.Name = "My Series #1"
.Interior.Color = RGB(160, 120, 250)

For x = 1 To 10

With .Points(x)
.HasDataLabel = True
.DataLabel.Top = .DataLabel.Top + 1
.DataLabel.Left = 0
.DataLabel.Font.Size = 9
.DataLabel.Caption = "One" & " - " & "RATHER LONG" & " - " & "Label"
End With
Next x
End With

With .SeriesCollection(2)
.Name = "My Series #2"
.Interior.Color = RGB(250, 250, 140)
For x = 1 To 10
With .Points(x)
.HasDataLabel = True
.DataLabel.Top = .DataLabel.Top + 1
.DataLabel.Left = 0
.DataLabel.Font.Size = 9
.DataLabel.Text = "Two" & " - " & "RATHER LONG" & " - " & "Label"
End With
Next x

End With

'formatting the category axis
With .Axes(xlCategory)
.HasTitle = True
End With

With .Axes(xlValue)

.HasTitle = True

With .AxisTitle
.Caption = "How Many Boo-Boos"
'.Font.Size = 12
.Orientation = xlHorizontal
End With
End With
End With

xlApp.Visible = True
xlApp.UserControl = True

Exit_CreateChart:
Set xlChartObj = Nothing
Set xlWrkbk = Nothing
Set xlApp = Nothing
Exit Function

Err_CreateChart:
MsgBox CStr(Err) & " " & Err.Description
Resume Exit_CreateChart
End Function

Private Sub Command1_Click()
Call CreateChart
End Sub

 

[terry22]

hmmm ... one other problem with the DataLabel ...when assigning a very long string it reaches a certain point and then begins to wrap, thus growing in size/height vertically. I want it to grow horizontally only ... or change the length of the DataLabel in such a way that it is long enough to hold the longest string I might need to assign.

Please can anyone make any suggestions on how to solve these two issues?

1. position/align DataLabels
2. make the DataLabel very long

Thanks!