[RESOLVED] VB.NET Excel Chart Cell Formatting

[ozoner]

I have a program that creates a excel spreadsheet and a chart on each tab from extracted data in various .txt files.

Before the chart is created, two of the excel columns will end up with data like this: on column 9 and 10. I also use data (time, looks like 09:15) in column 11 that shows the time at the bottom of the chart. There are just too many digits in the output and the readability looks bad so I can also convert.

Input Output
8004000 47320000
8254000 47120000
8013000 47202000
6315000 47649000
6547000 46035000

The chart will display those values just fine but, normally the conversion in code that I do during the placement of that data (so it reads a bit better) looks like this:

'Convert bit to Kbit
                            If InRate.Length < 7 Then
                                InRate = CDbl(InRate) / 1000 & " Kbit"
                            Else
                                InRate = CStr(CDbl(InRate) / 1000)
                                InRate = CDbl(InRate) / 1000 & " MB"
                            End If

                            oSheet.Cells(oRow, 9).value = InRate
                        End If

			'convert bit to Kbit or MB
                            If OutRate.Length < 7 Then
                                OutRate = CDbl(OutRate) / 1000 & " Kbit"
                            Else
                                OutRate = CStr(CDbl(OutRate) / 1000)
                                OutRate = CDbl(OutRate) / 1000 & " MB"
                            End If
 			oSheet.Cells(oRow, 10).value = OutRate

And then in the excel chart it would come out something like:

Input Output
80.04 MB 47.32 MB
82.54 MB 47.12 MB

But, then the chart will not display those values. I can't figure out how to get the formatting in the cell to be compatible with the chart output. I'm not sure where or how to handle this. My chart code is like this:

 xlCharts = oSheet.ChartObjects
                myChart = xlCharts.Add(95, 200, 700, 300)
                chartPage = myChart.Chart
                chartRange = oSheet.Range("I2", "K" & oRow)
                chartPage.SetSourceData(Source:=chartRange)
                chartPage.ChartType = Excel.XlChartType.xlColumnClustered

                chartPage.SetSourceData(Source:=oSheet.Range("I2:K" & oRow))

                chartPage.SeriesCollection(1).Name = "='24'!$I$1"
                chartPage.SeriesCollection(2).Name = "='24'!$J$1"

                chartPage.SeriesCollection(1).XValues = "='24'!$K$2:$K$" & oRow
                chartPage.SeriesCollection(2).XValues = "='24'!$K$2:$K$" & oRow
                'chartPage.SeriesCollection(3).XValues = "={0}"
                chartPage.SeriesCollection(3).Delete()

Any ideas on how to make the chart work with just numbers and ignore the MB or Kbit at the end? Thanks for any ideas.

[Jon Peltier]

Don't convert your data into "80.04 MB", because that's text, which Excel plots as zero. Instead, don't modify the value, but change the number format in the cell to 0.00,, "MB", which retains the numerical value and merely changes the display.

[ozoner]

This worked great, thank you very much!!!