how to maintain format of cells values ,when copying between 2 excel workbooks,and showing in text boxes

Question

'on event click that code copy values from some workbooks,and show that data in some text boxes 'the problem is whith the time format, it can'tbe shown in textbox for example in excel Cell.value the time is 09:26 => textbox is 0,34615.

Dim ystrdycrq As Excel.Workbook
Dim ystrdextr As Excel.Worksheet
Dim ystrdname As String = [String].Format("D:\data\données station\crq{0}.xls", DateTime.Now.AddDays(-1).ToString("ddMMyyyy"))

Dim fichiextreme As String = "K_" & Date.Now.AddDays(-1).ToString("MMdd") & ".xls"
Dim sourcextreme As String = "\\xxxxxxx\aero_mes\piste0\" & fichiextreme
Dim localextreme As String = "D:\data\bdcrq\" & fichiextreme
MsgBox(fichiextreme & sourcextreme & localextreme)

If System.IO.File.Exists(sourcextreme) Then
    File.Copy("\\xxxxxx\aero_mes\piste0\" & fichiextreme, "D:\data\bdcrq\" & fichiextreme, True)
    Dim extrwb As Excel.Workbook
    Dim extrwsh As Excel.Worksheet
    ystrdycrq = app.Workbooks.Open(ystrdname)
    ystrdextr = ystrdycrq.Worksheets("extrèmes")
    extrwb = app.Workbooks.Open(localextreme) ' ne pas oublier de la fermer
    extrwsh = extrwb.Sheets(1)
    extrwsh.Cells(1, 14).Copy(ystrdextr.Cells(1, 1))
    extrwsh.Cells(2, 14).Copy(ystrdextr.Cells(2, 1))
    extrwsh.Cells(1, 15).Copy(ystrdextr.Cells(1, 2))
    extrwsh.Cells(2, 15).Copy(ystrdextr.Cells(2, 2))
    extrwsh.Cells(1, 16).Copy(ystrdextr.Cells(1, 3))
    extrwsh.Cells(2, 16).Copy(ystrdextr.Cells(2, 3))
    extrwsh.Cells(1, 17).Copy(ystrdextr.Cells(1, 4))
    extrwsh.Cells(2, 17).Copy(ystrdextr.Cells(2, 4))
    extrwsh.Cells(1, 54).Copy(ystrdextr.Cells(1, 5))
    extrwsh.Cells(2, 54).Copy(ystrdextr.Cells(2, 5))
    extrwsh.Cells(1, 55).Copy(ystrdextr.Cells(1, 6))
    extrwsh.Cells(2, 55).Copy(ystrdextr.Cells(2, 6))

    txtUmin.Text = ystrdextr.Cells(2, 5).value
    txtHH_Umin.Text = ystrdextr.Cells(2, 6).value
    txtUmax.Text = ystrdextr.Cells(2, 7).value
    txtHH_Umax.Text = ystrdextr.Cells(2, 8).value
    txtTmin.Text = ystrdextr.Cells(2, 1).value
    txtHH_Tmin.Text = ystrdextr.Cells(2, 2).value
    txtTmax.Text = ystrdextr.Cells(2, 3).value
    txtHH_Tmax.Text = ystrdextr.Cells(2, 4).value

    extrwb.Close(SaveChanges:=False)
    ystrdycrq.Close(SaveChanges:=True)
    ystrdextr = Nothing
    ystrdycrq = Nothing
    extrwb = Nothing
    extrwsh = Nothing

    releaseObject(ystrdextr)
    releaseObject(ystrdycrq)
    releaseObject(extrwsh)
    releaseObject(extrwb)
Else
    MsgBox("the file .....", vbOKOnly)

End If

thank you TnTiMn .what i mean by (09:26 => textbox is 0,34615) that when the txtbox is showing the time value it shows 0,34615),but Ifixed this problem by using this syntax: txtHH_Umax.Text = ystrdextr.Cells(2, 8).Text and it works.But my big problem is when i copy those values from workbook to another ,the source data is in text format it contains signs like (+ or -) ,and when copied these signs disepears.and I'm in big problem for days. thanks for help. — Z.Aymen, Nov 23 '17 at 13:19
Be careful using the `Excel.Range.Text` property. It returns a string that represents the way a value is formatted on screen by Excel; so if the column is not wide enough to show the true value, the `Text` property will contain hash marks ("##") the same as you would see in Excel. As far as for why you are not getting the (+/-) custom format to copy using the `sourceRange.Copy(destinationRange)` method, I do not know why that does not work. One of the biggest issues most people have with that method is that it does in fact copy the source format to the destination which is often not wanted. — TnTinMn, Nov 24 '17 at 20:21

score 0 · Answer 1 · answered Nov 22 '17 at 02:16

Excel stores time values as a fraction of 24 hours.

Your example of:

09:26 => textbox is 0,34615

does not make sense. "9:26 AM" should be represented by "0,39305555555555555" or the time should be "8:18:27 AM". Perhaps you copied the incorrect value?

In any case, you can use the following technique to convert an Excel time value to a formatted .Net String.

Dim rng as Excel.Range = 'your worksheet cell"
TextBox1.Text = DateTime.FromOADate(CDbl(rng.Value2)).ToString("t")

You should enable "Option Strict" in your code and implement proper type conversions instead of relying on the compiler to guess your intent.

There is typically no need to manually release COM objects which is what I am inferring your releaseObject method to be attempting. .Net is not broken and is capable of cleaning things up as long as you are not preventing it from doing so. If you need deterministic release of COM objects, you can follow the pattern in this answer.

For a very good write-up on the COM release issue, see: https://stackoverflow.com/a/25135685/2592875

thanks a lot TnTinMn but my problem is : when I copy the values to other worksheet in oyher workbook,witch is necessary,the signs in the cells(+ or -) are not copied. (the data is températures this is why it's necessary).and thanks for help. — Z.Aymen, Nov 23 '17 at 20:23

how to maintain format of cells values ,when copying between 2 excel workbooks,and showing in text boxes

1 Answers1