Results 1 to 15 of 15

Thread: Excel Interop Change NumerFormat on Range

  1. #1

    Thread Starter
    Hyperactive Member Frabulator's Avatar
    Join Date
    Jan 2015
    Location
    USA
    Posts
    393

    Excel Interop Change NumerFormat on Range

    I am currently using Excel Interop from 2007 to integrate an application export into an Excel document.

    I am taking string data from a DataGridView bound by a DataTable and putting them into and Excel document for graphing. Everything works great, until the default decimals change to a comma (,) instead of a period (.).

    Example:
    1.24 graphs fine
    1,24 graphs nothing

    The problem comes from the text being read as text and not numbers. I have found a work around for this, which is to use the Interop to specify the Number Format of the cell, as listed below:

    Code:
                        Try
                            For ri = RowStart + 2 To MEx + RowStart + 1
                                For ci = 5 To MEy + 4
                                    Dim rng5 As Microsoft.Office.Interop.Excel.Range
                                    rng5 = xlWorkSheet.Cells(ri, ci)
                                    rng5.Select()
    
                                    Try
    
                                        rng5.NumberFormat = "General"
                                        rng5.Value = rng5.Value
    
                                        rng5.NumberFormat = "0.00"
                                        rng5.Value = rng5.Value
                                    Catch ex As Exception
    
                                    End Try
    
                                    ReleaseCOM(rng5)
                                Next
                            Next
                        Catch exe4 As Exception
    
                        End Try
    Yes, this works, but it is extremely slow, as it has to loop through each cell.

    I tried to combine this together and do an entire range:

    Code:
                        Try
                            Dim rng5 As Microsoft.Office.Interop.Excel.Range
                            Dim rng6 As Microsoft.Office.Interop.Excel.Range
                            Dim rng7 As Microsoft.Office.Interop.Excel.Range
                            rng6 = xlWorkSheet.Cells(RowStart + 2, 5)
                            rng7 = xlWorkSheet.Cells(MEx + RowStart + 1, MEy + 4)
                            Dim r6 As String = rng6.Address
                            Dim r7 As String = rng7.Address
                            r6 = Replace(r6, "$", "")
                            r7 = Replace(r7, "$", "")
                            rng5 = xlWorkSheet.Range(r6 & ":" & r7)
                            rng5.Select()
    
                            Try
                                rng5.NumberFormat = "General"
                                rng5.Value = rng5.Value
                                rng5.NumberFormat = "0.00"
                                rng5.Value = rng5.Value
    
                            Catch ex As Exception
    
                            End Try
    
    
                            ReleaseCOM(rng5)
                            ReleaseCOM(rng6)
                            ReleaseCOM(rng7)
                        Catch ex As Exception
    
                        End Try
    However, this does not work. No errors are thrown, everything goes through, but the Number Format does not change.

    What am I doing wrong? Is there a better way to do this? Any suggestions?

    Thanks!
    ~Frab
    Oops, There it goes. Yep... my brain stopped...
    _________________________________

    Useful Things:

    How to link your VB.Net application to Excel

  2. #2
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,046

    Re: Excel Interop Change NumerFormat on Range

    you might want to try NumberFormatLocal

    see this
    Code:
    Private Sub Button3_Click(sender As System.Object, e As System.EventArgs) Handles Button3.Click
            Try
                Dim xlApp As New Microsoft.Office.Interop.Excel.Application()
                Dim xlWb As Microsoft.Office.Interop.Excel.Workbook
                xlWb = xlApp.Workbooks.Open("E:\TestFolder\ExcelTest\vbexcel.xlsx")
                Dim xlSt As Microsoft.Office.Interop.Excel.Worksheet = CType(xlWb.Worksheets("Tabelle4"), Worksheet)
                With xlSt
                    'do stuff here with Sheet
                    'xlSt.Range("A4").Interior.Color = Color.BlueViolet
                    xlSt.Range("B1", "B222").NumberFormatLocal = "#.##0.00"
                End With
                xlWb.Save()
                xlApp.Quit()
                xlApp = Nothing
            Catch g As Exception
                MessageBox.Show(g.ToString)
            End Try
        End Sub
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  3. #3

    Thread Starter
    Hyperactive Member Frabulator's Avatar
    Join Date
    Jan 2015
    Location
    USA
    Posts
    393

    Re: Excel Interop Change NumerFormat on Range

    Quote Originally Posted by ChrisE View Post
    you might want to try NumberFormatLocal

    see this
    Just tried it, same issue. The numbers are still formatted as text instead of numbers.
    Code:
                        Try
                            Dim rng5 As Microsoft.Office.Interop.Excel.Range
                            Dim rng6 As Microsoft.Office.Interop.Excel.Range
                            Dim rng7 As Microsoft.Office.Interop.Excel.Range
                            rng6 = xlWorkSheet.Cells(RowStart + 2, 5)
                            rng7 = xlWorkSheet.Cells(MEx + RowStart + 1, MEy + 4)
                            Dim r6 As String = rng6.Address
                            Dim r7 As String = rng7.Address
                            r6 = Replace(r6, "$", "")
                            r7 = Replace(r7, "$", "")
                            rng5 = xlWorkSheet.Range(r6, r7)
                            rng5.Select()
    
                            Try
                                rng5.NumberFormatLocal = "#.##0.00"
    
                            Catch ex As Exception
    
                            End Try
    
    
                            ReleaseCOM(rng5)
                            ReleaseCOM(rng6)
                            ReleaseCOM(rng7)
                        Catch ex As Exception
    
                        End Try
    Oops, There it goes. Yep... my brain stopped...
    _________________________________

    Useful Things:

    How to link your VB.Net application to Excel

  4. #4
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,046

    Re: Excel Interop Change NumerFormat on Range

    you will have to correct your Datatable first then

    you say here...
    I am taking string data from a DataGridView bound by a DataTable and putting them into and Excel document for graphing. Everything works great, until the default decimals change to a comma (,) instead of a period (.).
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  5. #5

    Thread Starter
    Hyperactive Member Frabulator's Avatar
    Join Date
    Jan 2015
    Location
    USA
    Posts
    393

    Re: Excel Interop Change NumerFormat on Range

    Quote Originally Posted by ChrisE View Post
    you will have to correct your Datatable first then

    you say here...
    Please elaborate, because I do not understand.

    For US conversions, everything is fine, inside the software and inside Excel. For DE conversions (, decimals), everything on the program side does with it is suppose to, Excel doesnt. This is even the case when I have all the regional settings changed to reflect commas as the decimal point in both the windows OS and office suite.
    Oops, There it goes. Yep... my brain stopped...
    _________________________________

    Useful Things:

    How to link your VB.Net application to Excel

  6. #6
    Powered By Medtronic dbasnett's Avatar
    Join Date
    Dec 2007
    Location
    Jefferson City, MO
    Posts
    9,754

    Re: Excel Interop Change NumerFormat on Range

    Have you tried setting options?

    Code:
            ' XLapp = Excel.Application
    
            'default US
            'With XLapp
            '    .DecimalSeparator = "."
            '    .ThousandsSeparator = ","
            '    .UseSystemSeparators = True
            'End With
    
            'Other format
            'With XLapp
            '    .DecimalSeparator = ","
            '    .ThousandsSeparator = "."
            '    .UseSystemSeparators = False
            'End With
    My First Computer -- Documentation Link (RT?M) -- Using the Debugger -- Prime Number Sieve
    Counting Bits -- Subnet Calculator -- UI Guidelines -- >> SerialPort Answer <<

    "Those who use Application.DoEvents have no idea what it does and those who know what it does never use it." John Wein

  7. #7

    Thread Starter
    Hyperactive Member Frabulator's Avatar
    Join Date
    Jan 2015
    Location
    USA
    Posts
    393

    Re: Excel Interop Change NumerFormat on Range

    Quote Originally Posted by dbasnett View Post
    Have you tried setting options?

    Code:
            ' XLapp = Excel.Application
    
            'default US
            'With XLapp
            '    .DecimalSeparator = "."
            '    .ThousandsSeparator = ","
            '    .UseSystemSeparators = True
            'End With
    
            'Other format
            'With XLapp
            '    .DecimalSeparator = ","
            '    .ThousandsSeparator = "."
            '    .UseSystemSeparators = False
            'End With
    I have. No change. Excel is still reading the data as text and not numbers.
    Oops, There it goes. Yep... my brain stopped...
    _________________________________

    Useful Things:

    How to link your VB.Net application to Excel

  8. #8
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,046

    Re: Excel Interop Change NumerFormat on Range

    Quote Originally Posted by Frabulator View Post
    Please elaborate, because I do not understand.

    For US conversions, everything is fine, inside the software and inside Excel. For DE conversions (, decimals), everything on the program side does with it is suppose to, Excel doesnt. This is even the case when I have all the regional settings changed to reflect commas as the decimal point in both the windows OS and office suite.
    you load Data to a DataTable then to a DGV, where does this Data come from ?
    a CSV ?
    a TXT ?
    ....
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  9. #9

    Thread Starter
    Hyperactive Member Frabulator's Avatar
    Join Date
    Jan 2015
    Location
    USA
    Posts
    393

    Re: Excel Interop Change NumerFormat on Range

    Quote Originally Posted by ChrisE View Post
    you load Data to a DataTable then to a DGV, where does this Data come from ?
    a CSV ?
    a TXT ?
    ....
    It is pasted into the application by the user, or loaded from a csv.
    Oops, There it goes. Yep... my brain stopped...
    _________________________________

    Useful Things:

    How to link your VB.Net application to Excel

  10. #10
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,046

    Re: Excel Interop Change NumerFormat on Range

    with a CSV you can use a schema.ini

    Code:
    [yourCSVFile.csv]
    ColNameHeader=True
    Format=Delimited(;)
    DecimalSymbol=.
    CurrencyDecimalSymbol=.
    CharacterSet=ANSI
    Col1=ProductCode Text Widht 30
    Col2=Batch Integer
    Col3=Counted Integer
    Col5=myMoney Decimal
    take a look at this thread
    https://www.vbforums.com/showthread....nversion-Issue
    Last edited by ChrisE; Jul 29th, 2022 at 09:51 AM.
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  11. #11

    Thread Starter
    Hyperactive Member Frabulator's Avatar
    Join Date
    Jan 2015
    Location
    USA
    Posts
    393

    Re: Excel Interop Change NumerFormat on Range

    Quote Originally Posted by ChrisE View Post
    with a CSV you can use a schema.ini

    Code:
    [yourCSVFile.csv]
    ColNameHeader=True
    Format=Delimited(;)
    DecimalSymbol=.
    CurrencyDecimalSymbol=.
    CharacterSet=ANSI
    Col1=ProductCode Text Widht 30
    Col2=Batch Integer
    Col3=Counted Integer
    Col5=myMoney Decimal
    take a look at this thread
    https://www.vbforums.com/showthread....nversion-Issue
    I dont think that would solve my issue. Once the data is loaded in, then it is automatically converted over into a data table of string variables inside my software. I need them to be strings so I can maniuplate the data. Not all will start off as numbers. The program does what it does with the data and then plops it out into Excel as all numbers.

    I could possibly make it so the program exports a CSV, and then have Excel load the CSV, but that seems redundant.

    It would be much easier if there were just a setting inside Excel that would allow me to convert a range of data to number formatting. I can do that manually (just by clicking on the exclamation mark and saying 'Convert all to Numbers') but I cant find a way to do this in code.
    Oops, There it goes. Yep... my brain stopped...
    _________________________________

    Useful Things:

    How to link your VB.Net application to Excel

  12. #12
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,046

    Re: Excel Interop Change NumerFormat on Range

    I dont think that would solve my issue. Once the data is loaded in, then it is automatically converted over into a data table of string variables inside my software.


    well you know where the problem is then

    EDIT:
    last try of mine ist the use of...EntireColumn

    Code:
    '....
       xlSt.Range("A1").Value = "ID"
       xlSt.Range("A:A").EntireColumn.NumberFormat = "0.00"
    '........
    but the problem is really your Datatable
    Last edited by ChrisE; Jul 30th, 2022 at 01:38 AM.
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  13. #13

    Thread Starter
    Hyperactive Member Frabulator's Avatar
    Join Date
    Jan 2015
    Location
    USA
    Posts
    393

    Re: Excel Interop Change NumerFormat on Range

    Quote Originally Posted by ChrisE View Post


    well you know where the problem is then
    But if that is the problem, then why does it only affect the numbers that have a comma as the decimal? In the same logic, wouldn't 1.24 be read as text just as 1,24 is? Would it also not read 1,245.6 as text as well? I just dont understand how that could be different?


    EDIT:
    last try of mine ist the use of...EntireColumn

    Code:
    '....
       xlSt.Range("A1").Value = "ID"
       xlSt.Range("A:A").EntireColumn.NumberFormat = "0.00"
    '........
    I tried that, but the formatting is a little off using an interop setup. I will try and tweak it, as converting a column at a time is much quicker than a cell.
    Oops, There it goes. Yep... my brain stopped...
    _________________________________

    Useful Things:

    How to link your VB.Net application to Excel

  14. #14
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: Excel Interop Change NumerFormat on Range

    If values are stored as their original types (Integer, Single, Date, etc), then they are passed directly into the target application that way, and the target application just needs to format them for display (ie: show a String version of the value).

    If values are stored as Strings then the target application needs to decode those strings to convert them back to the original type (and this is the part that goes wrong, due to a wide variety of assumptions made in the code that converts them). After the conversion has happened, the target application can format them for display (as it did for values passed directly as their original type).


    If you pass a DataTable containing the original data types, the values will be passed correctly, as no conversion is necessary. In terms of formatting things for display, you can do that in your app (via the various properties/methods of the DataGridView), and in the target app (such as setting NumberFormat in Excel).

  15. #15

    Thread Starter
    Hyperactive Member Frabulator's Avatar
    Join Date
    Jan 2015
    Location
    USA
    Posts
    393

    Re: Excel Interop Change NumerFormat on Range

    I have tried to take all the values from the datatable, converted the values to doubles, and then transposed them into another new datatable. The result was the same. Worked fine for period decimals, still 'text' as comma decimals when put into Excel.

    I tried to special paste the values in, thinking that I could paste them in as 'values', but I had the same result.

    I found that directly converting the numbers to doubles in Excel, instead of converting the text into numbers with the format tools, is much quicker.

    Code:
                        Try
                            Dim rng5 As Microsoft.Office.Interop.Excel.Range
                            For ri = RowStart + 2 To MEx + RowStart + 1
                                For ci = 5 To MEy + 4
                                    rng5 = xlWorkSheet.Cells(ri, ci)
                                    rng5.Select()
                                    rng5.Value = CDbl(rng5.Value)
                                    ReleaseCOM(rng5)
                                Next
                            Next
                        Catch exe4 As Exception
    
                        End Try

    I have not tried to export the data as CSV and load the CSV into excel. I suppose that will be the next step.
    Oops, There it goes. Yep... my brain stopped...
    _________________________________

    Useful Things:

    How to link your VB.Net application to Excel

Posting Permissions

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



Click Here to Expand Forum to Full Width