Answered string to excel as number

[FMontana71]

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\InTrend\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.Interop.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

 

[jmcilhinney]

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.

 

[FMontana71]

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

 

[jmcilhinney]

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.

 

[FMontana71]

Thanks again jmcilhinney.