Results 1 to 5 of 5

Thread: string to excel as number

  1. #1
    .NET Framework
    .NET 4.5
    Join Date
    Apr 2018
    Posts
    3
    Reputation
    0

    string to excel as number

    Good morning to you all

    Let me start by stating that I am not a programmer, and all I have done so far, is based on your help in the web, with your examples.

    So I have a form where the user inputs a number on a textbox -TB1 let's say 12.31, and other value (integer) on TB2. On another textbox - TB3 I made the calculation TB1 x TB2 and present the result as currency (in EU #.## €) with this code:

    Private Sub TB2_LostFocus(sender As Object, e As EventArgs) Handles TextBox2.LostFocus

    T3.Text = Val(TB1.Text) * Val(TB2.Text)
    Dim VS As Decimal = TB3.Text
    TB3.Text = Format(VS, "Currency")
    End Sub

    After this, I need to send TB3.text to Excel, which I did with the code:

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

    Dim xlApp As Microsoft.Office.Interop.Excel.Application
    Dim xlWB As Microsoft.Office.Interop.Excel.Workbook
    Dim xlWS As Microsoft.Office.Interop.Excel.Worksheet
    Dim lastRow As Long

    xlWB = xlApp.Workbooks.Open("C:\Users\3787\Desktop\InTren d\Intrend - listagens.xlsx")
    xlWS = CType(xlWB.Worksheets(2), Microsoft.Office.Interop.Excel.Worksheet)
    xlApp.Visible = False

    lastRow = xlWS.Range("A" & xlApp.Rows.CountLarge).End(Microsoft.Office.Intero p.Excel.XlDirection.xlUp).Row + 1


    With xlWS
    .Range("G" & lastRow).Value = Me.TextBox3.Text
    End With
    End Sub

    Now my problem is. that I need that this value of TB3 to be presented as number on Excel...for some reason that I cannot see, the result are presented on Excel as text...on the selected cell appears that green error "number stored as text"

    How can I correct this? I need that value stored as number because I need to retrieve it back to another textbox in order to send it to another Excel sheet...please help

    Many thanks

    FMontana

  2. #2
    .NET Framework
    .NET 4.0
    Join Date
    Aug 2004
    Location
    Sydney, Australia
    Posts
    13,998
    Reputation
    1728
    The reason that Excel gets the data as text is that you are providing it as text. What data type is the Text property of a TextBox? It's String, right? It's not going to magically become a number just because it contains digit characters. They are still characters and it is still a String. If you want Excel to tret your data as a number then give it a number.

    The fact is that you're doing things in the wrong order. You are getting text from the input TextBoxes and converting those Strings to numbers for the calculation. That's good, although there are better options than Val to do it. After the calculation, you have a number. THAT is the number that you should be passing to Excel. Instead, you implicitly convert it to a String and put it into a TextBox, then get that String back from the TextBox and implicitly convert it to a number, then format that as a String and put it back into the TextBox, then get it back from the TextBox and pass that String to Excel. Does that sound convoluted? That's because it is.

    What you should be doing is validating your input text and converting that to numbers of the appropriate types, which sounds like Decimal for one and Integer for the other. The result of that will be a Decimal. That is your number and wherever you need a number, that is what you use. That means that that is the value that you pass to Excel. That is also the value that you format and display in the output TextBox. No in and out and in and out. In once and that's it.

    Dim dec As Decimal
    Dim int As Integer

    If Decimal.TryParse(TB1.Text, dec) AndAlso Integer.TryParse(TB2.Text, int) Then
    Dim result As Decimal = dec * int

    TB3.Text = result.ToString("c")
    Else
    'Notify user of invalid input.
    End If

    That 'result' variable contains the number that you need to send to Excel. If it needs to be access from multiple methods then you should declare it at the class level rather than with local scope as I have done here. Note also the use of TryParse methods to validate and convert and the use of ToString to format. We're not in VB6 anymore Toto.

  3. #3
    .NET Framework
    .NET 4.5
    Join Date
    Apr 2018
    Posts
    3
    Reputation
    0
    Dear jmcilhinney many thanks for you help...problem solved.

    Now let me ask for your help once again...I this project I have a initial form 1, and from this form I can open another 4 forms. Now, my problem is that I am working with Interop Microsoft Excel, in order to write/read some data from a Excel workbook, with 3 worksheets.

    This workbook is common to all the project...is it possible to give instruction on form 1 to open this workbook, and on the rest of the forms give the instruction of the worksheet to work with, without the need to open/save/close in all of them?

    Many thanks in advance

    FMontana

  4. #4
    .NET Framework
    .NET 4.0
    Join Date
    Aug 2004
    Location
    Sydney, Australia
    Posts
    13,998
    Reputation
    1728
    Quote Originally Posted by FMontana71 View Post
    problem solved.

    Now let me ask for your help once again
    If the current issue is resolved, please edit the thread to change the title prefix to "Answered". I've done that for you on this occasion. If you want help with a new problem, please create a new thread with a title that summarises this new problem and provide all the information relevant to the new problem. Please keep each topic to one thread and each thread to one topic.

  5. #5
    .NET Framework
    .NET 4.5
    Join Date
    Apr 2018
    Posts
    3
    Reputation
    0
    Thanks again jmcilhinney.

Similar Threads

  1. Question Excel Number formatting
    By fachagooch in forum Third Party Products
    Replies: 2
    Last Post: 01-09-2013, 8:01 AM
  2. working with excel number format
    By ridhwaans in forum VB.NET General Discussion
    Replies: 3
    Last Post: 07-23-2012, 6:29 PM
  3. comparing number to string
    By RonMex in forum VB.NET General Discussion
    Replies: 8
    Last Post: 04-12-2011, 9:57 PM
  4. Question A number As String ??
    By jaybee33 in forum Windows Forms
    Replies: 3
    Last Post: 03-18-2009, 10:51 AM
  5. Create Number from String
    By bortiquai in forum VB.NET General Discussion
    Replies: 5
    Last Post: 12-30-2007, 10:05 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •