Problems in VB.net land with Excel charting!
Howdy All,
I've been tsked with producing an XY scatter Chart in Excel via a VB.net application.
I have succesfully started Excel and imported the data to chart. I have also created a chrat and set it's type to xlXYScatterSmoothNoMarkers, which is what I want.
I have having MAJOR problems setting the XValue Range and the Value Range of the chart via code... I've tried lokking at and imulating the macro capture of doing this, but when I get over into the .net land it does NOT seem to work!!!!
VB Code:
Dim oXL As Excel.Application
Dim oWB As Excel.Workbook
Dim oSheet As Excel.Worksheet
Dim oRng As Excel.Range
' Start Excel and get Application object.
oXL = CreateObject("Excel.Application")
oXL.Visible = True
' Get a new workbook.
'oWB = oXL.Workbooks.Add
oWB = oXL.Workbooks.OpenXML(job.Item("JobDir").InnerText & "\" & "vps.submitter." & job.Item("id_job").InnerText & ".xml")
oSheet = oWB.ActiveSheet
Dim rowIndex As Long
Dim DisplCol As Long
Dim ForceCol As Long
Dim startRowIndex As Long
Dim endRowIndex As Long
Dim TLMAXRow As Long
'Misc. code used here to set the column... (ie remove from this example...)
DisplCol = 2
'Misc. code used here to set the row... (ie remove from this example...)
TLMAXRow = 33
'Misc. code used here to set the row... (ie remove from this example...)
startRowIndex = 3
'Misc. code used here to set the row... (ie remove from this example...)
endRowIndex = 83
Dim NewCol As String
Dim NewColRange As String
'the ColumnLetter function is one I wrote to convert a number to a Letter for the Column, ie. 3 = "C"
NewCol = ColumnLetter(DisplCol + 1)
NewColRange = NewCol & ":" & NewCol
'MsgBox("NewCol : " & NewCol & vbCrLf & "NewColRange: " & NewColRange)
oSheet.Range(NewColRange).Insert()
'Misc. code used here to set the column... (ie remove from this example...)
ForceCol = 3
Dim NewChart As Excel.Chart
NewChart = oWB.Charts.Add
NewChart.Activate()
NewChart.ChartType = XlChartType.xlXYScatterSmoothNoMarkers
Dim XValRange As Range
Dim ValRange As Range
XValRange = oSheet.Range(NewCol & startRowIndex, NewCol & endRowIndex)
ValRange = oSheet.Range(ColumnLetter(ForceCol) & startRowIndex, ColumnLetter(ForceCol) & endRowIndex)
Dim serCollect As Excel.Series
serCollect = NewChart.SeriesCollection(1)
Dim vrange As String
Dim xrange As String
vrange = "=" & oSheet.Name & "!R" & startRowIndex & "C" & ForceCol & ":R" & endRowIndex & "C" & ForceCol
MessageBox.Show(vrange)
xrange = "=" & oSheet.Name & "!$" & NewCol & "$" & startRowIndex & ":$" & NewCol & "$" & endRowIndex
MessageBox.Show(xrange)
'Heres where the "FUN" starts... I can't get either of the following lines to work!!!
serCollect.Values = vrange
serCollect.XValues = xrange
'I have also tried:
'serCollect.Values = ValRange
'serCollect.XValues = XValRange
' To NO aval, that didn't work either...
' And I tried:
'serCollect.Values = ValRange.value
'serCollect.XValues = XValRange.value
' Guess what.... that didn't work either...
So I've tried a few different ways to do this with strings and ranges, neither WORK!!!
Any Ideas out there on how to set the series XValues and Values out there????
Re: Problems in VB.net land with Excel charting!
Quick questions
1/ How many columns in your XML file?
2/ If more than 2, then how do you determine which 2 columns contain the chart data?
3/ Are all rows in the xml file used in the chart?
4/ If not, then how do you determine which rows contain the chart data?
Re: Problems in VB.net land with Excel charting!
Ok,
I was trying to save the forum at looking at misc. code... here's the full code to find the various colums and rows of where the data is...
I do know that in row 2 are my column headings... and that in column "E" is where I will find the word "TLMAX"
In my first post I said I had remove some of the code (For simplicity sakes!)
Sorry I wasn't clear on that and for any confusion as a result!
VB Code:
foundResults = oSheet.Range("2:2").Find(what:="displacement", LookIn:=XlFindLookIn.xlValues, lookat:=XlLookAt.xlPart, MatchCase:=False)
If Not foundResults Is Nothing Then
DisplCol = foundResults.Column
'MsgBox("Displacement Column: " & DisplCol)
Else
MsgBox("Not Found")
End If
foundResults = oSheet.Range("E:E").Find(what:="TLMAX", LookIn:=XlFindLookIn.xlValues, lookat:=XlLookAt.xlWhole, MatchCase:=False)
If Not foundResults Is Nothing Then
TLMAXRow = foundResults.Row
'MsgBox("TLMAX Row: " & TLMAXRow)
Else
MsgBox("Not Found")
End If
foundResults = oSheet.Range("A:A").Find(what:="0", LookIn:=XlFindLookIn.xlValues, lookat:=XlLookAt.xlWhole, MatchCase:=False)
If Not foundResults Is Nothing Then
startRowIndex = foundResults.Row
'MsgBox("Start Row Index Row: " & startRowIndex)
Else
MsgBox("Not Found")
End If
foundResults = oSheet.Range("A:A").End(XlDirection.xlDown)
If Not foundResults Is Nothing Then
endRowIndex = foundResults.Row
'MsgBox("End Row Index Row: " & endRowIndex)
Else
MsgBox("Not Found")
End If
So that's the code to go out and find the extents of the data
I want the displacement column to be my x values (in this case column 2)
and the Y Values are in the "Force" column (in this case 3)
The data starts on row 3 and ends on row 83 (in this case... the next file will be different and the code hould find the extents properly)
1 Attachment(s)
Re: Problems in VB.net land with Excel charting!
OK
Try this.
You are doing way too much to find the range for the chart. All you really need are the Starting Column number, the starting row number and the ending row number.
I have included an example file.
VB Code:
Sub BuildScatterChart()
Dim oXL As Excel.Application
Dim oWB As Excel.Workbook
Dim oSheet As Excel.Worksheet
Dim lStartCol As Long
Dim lStartRow As Long
Dim lEndRow As Long
Dim rngData As Range
Dim chtScat As Chart
' Start Excel and get Application object.
Set oXL = New Excel.Application
oXL.Visible = True
' Get a new workbook.
'oWB = oXL.Workbooks.Add
Set oWB = oXL.Workbooks.OpenXML(job.Item("JobDir").InnerText & "\" & "vps.submitter." & job.Item("id_job").InnerText & ".xml")
oSheet = oWB.ActiveSheet
'Find the starting column number by looking for
' "displacement" in Row 2
lStartCol = oSheet.Rows(2).Find(what:="displacement", _
LookIn:=XlFindLookIn.xlValues, _
lookat:=XlLookAt.xlPart, _
MatchCase:=False).Column
'Find the starting Row number by looking for
' a value of 0 in column A
lStartRow = oSheet.Columns(1).Find(what:="0", _
LookIn:=XlFindLookIn.xlValues, _
lookat:=XlLookAt.xlWhole, _
MatchCase:=False).Row
'Find the ending Row number by looking for
' a "TLMAX" in column E
lEndRow = oSheet.Columns(5).Find(what:="TLMAX", _
LookIn:=XlFindLookIn.xlValues, _
lookat:=XlLookAt.xlWhole, _
MatchCase:=False).Row
'Set the Data range to the range between:
'The cell that is the intersection of the starting Row and Column
'and
'The cell that is the intersection of the ending Row and the next Column
With oSheet
Set rngData = .Range(.Cells(lStartRow, lStartCol), .Cells(lEndRow, lStartCol + 1))
End With
'Add a chart
Set chtScat = ThisWorkbook.Charts.Add
With chtScat
.ChartType = xlXYScatterSmoothNoMarkers
'set the datasource for the chart to the range defined above
.SetSourceData rngData
'Do what ever else you need with the chart
.HasLegend = False
.HasTitle = False
End With
End Sub
