3 Replies - 853 Views - Last Post: 04 May 2012 - 12:13 AM Rate Topic: -----

#1 timotz  Icon User is offline

  • New D.I.C Head

Reputation: 4
  • View blog
  • Posts: 34
  • Joined: 01-April 12

object tostring error in vb.net and excel interroperation

Posted 03 May 2012 - 07:46 PM

i made this program some time ago. it finds all numerics in an excel program and changes their formatting and it works ok.

this is the meat of the code:
Dim bound0 As Integer = array.GetUpperBound(0)
                    Dim bound1 As Integer = array.GetUpperBound(1)
                    

                    For j As Integer = 1 To bound0
                        For x As Integer = 1 To bound1
                            
                            If IsNumeric(array(j, x)) Then
                                Dim cell As String
                                cell = Chr(Asc(x) + 16) & Asc(Chr(j))
                                r = sheet.Range(cell)
                                r.NumberFormat = "#,##0.00"
                                array(j, x) = FormatNumber(array(j, x), 2)
                                array(j, x) = String.Format("{0:F}", array(j, x))
                                r = sheet.UsedRange
                                
                            End If

                        Next

                    Next

                End If
                r = sheet.UsedRange
                r.Value = array



my problem is, there are some alphanumeric values where i need to change the formatting of the number part of the cell example:

"Test Value: 123456.639"
that i need to change to:
"Test Value: 123,456.64"

the array(,) is declared this way:
Dim array(,) As Object = r.Value(XlRangeValueDataType.xlRangeValueDefault)


so what i thought is use something like this:
If array(j, x).ToString().Contains("Test Value: ") Then
'remove the Test Value: part and format the remaining
End If



but it gives me an "Object reference not set to an instance of an object." error
is there another way to solve my problem? thanks in advance

Is This A Good Question/Topic? 0
  • +

Replies To: object tostring error in vb.net and excel interroperation

#2 DimitriV  Icon User is offline

  • They don't think it be like it is, but it do
  • member icon

Reputation: 584
  • View blog
  • Posts: 2,738
  • Joined: 24-July 11

Re: object tostring error in vb.net and excel interroperation

Posted 03 May 2012 - 08:01 PM

There mustn't be objects at that postition. Does the code you have return a value at all (the 2nd code snippet from the bottom). Does array(,) have a value? Try testing this.

This post has been edited by DimitriV: 03 May 2012 - 08:02 PM

Was This Post Helpful? 1
  • +
  • -

#3 timotz  Icon User is offline

  • New D.I.C Head

Reputation: 4
  • View blog
  • Posts: 34
  • Joined: 01-April 12

Re: object tostring error in vb.net and excel interroperation

Posted 03 May 2012 - 11:14 PM

thanks for your reply DimitriV(as always) :D but i have found a way to do it (crudely)
Dim temp(array.GetUpperBound(0), array.GetUpperBound(1)) As String
                    For j As Long = 1 To array.GetUpperBound(0)
                        For x As Long = 1 To array.GetUpperBound(1)
                            If array(j, x) <> Nothing Then
                                temp(j, x) = array(j, x).ToString
                            End If
                        Next
                    Next


and this code somewhere inside the for loops
If Not IsNothing(temp(j, x)) Then
     If temp(j, x).Contains("Test Value: ") Then
        temp(j, x) = temp(j, x).Replace("Test Value: ", "")
        Dim temp2 As Double
        temp2 = CDbl(temp(j, x))
        array(j, x) = "'" & "Test Value: " & temp2.ToString("#,##0.00")
      End If
End If


Was This Post Helpful? 1
  • +
  • -

#4 DimitriV  Icon User is offline

  • They don't think it be like it is, but it do
  • member icon

Reputation: 584
  • View blog
  • Posts: 2,738
  • Joined: 24-July 11

Re: object tostring error in vb.net and excel interroperation

Posted 04 May 2012 - 12:13 AM

Cool!
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1