Embedding excel objects in VB

[j2flk1234]

Hi,

I am trying to graph in VB6 but am having lots of difficulty using MSChart. I switched my strategy to try and use the excel graphing method from VB. I have an excel worksheet with 2 rows, one for the X axis and one for the Y axis as follows:

A B
1 6
2 7
3 8
4 9
5 10

I want the B values to represent the Y axis and the A values to represent the X axis. The code I have so far just graphs the A values as the Y axis which isn't what I want. My code is below:

Dim xlApp As Excel.Application
Set xlApp = New Excel.Application
Dim xlWkb As Excel.Workbook
Set xlWkb = xlApp.Workbooks.Open("C:\Documents and Settings\test.xls")
Dim xlSht As Excel.Worksheet
Set xlSht = xlWkb.Worksheets(2)
Dim xlChart As Excel.Chart
Set xlChart = xlWkb.Charts.Add
xlChart.ChartType = xlLine
'xlChart.
xlChart.SetSourceData xlSht.Range("B1:B5", "A1:A5"), xlColumns
xlChart.Visible = xlSheetVisible
xlChart.Legend.Clear
xlChart.ChartArea.Font.Size = 15
xlChart.ChartArea.Font.Color = vbRed
xlChart.ChartArea.Select
xlChart.ChartArea.Copy
Image1.Picture = Clipboard.GetData(vbCFBitmap)

I'm pretty sure I am completely wrong but can someone point me in the right direction?

Thanks in advance!!