none
remove commas from numbers when converting excel to csv file using excel interop. RRS feed

  • Question

  • I am using excel interopt with vb 2010 to convert an excel file to a csv file.  However, there are commas used with the numbers  greater than 999.99 that have to be removed so I only have the full decimal number.  The code to convert is very easy but I do not know what to add to remove these commas . (hopefully also easy)  I need to put these numbers into a decimal field in a datatable.

    Here is what I do now.  code is below.

     Dim excelApplication As New Microsoft.Office.Interop.Excel.Application
            Dim excelWrkBook As Excel.Workbook
    
            excelApplication.Visible = False
            excelApplication.DisplayAlerts = False
            excelWrkBook = excelApplication.Workbooks.Open(prxls)
    
            excelWrkBook.SaveAs(Filename:=prtxt, FileFormat:=-4158)
            excelWrkBook.Close()
            excelApplication.DisplayAlerts = True
            excelApplication.Quit()


    sm haig

    Sunday, August 3, 2014 4:45 AM

Answers

  • Hi,

    According to your description, you wantto remove the commas in the decimal number which is in the value of ranges such "1,235.60".

    If I understand correctly, you could change the Cells Format to "Number"  without "Use 1000 separator"as followed.

    To do this automatically with VB.Net, you could resort to Range.NumberFormat Property. You could set it to be "0.00".

    Here is a sample to set the fomat of all cells in column 2 to be "0.00" for your reference.

            Dim excelApplication As New Microsoft.Office.Interop.Excel.Application
            Dim excelWrkBook As Excel.Workbook
    
            excelApplication.Visible = False
            excelApplication.DisplayAlerts = False
            excelWrkBook = excelApplication.Workbooks.Open(prxls)
            
            Dim ws As Worksheet = excelWrkBook.Sheets(1)
            ws.Columns(2).NumberFormat = "0.00"
    



    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by smhaig Monday, August 4, 2014 5:47 PM
    Monday, August 4, 2014 5:54 AM
    Moderator

All replies

  • Hi,

    According to your description, you wantto remove the commas in the decimal number which is in the value of ranges such "1,235.60".

    If I understand correctly, you could change the Cells Format to "Number"  without "Use 1000 separator"as followed.

    To do this automatically with VB.Net, you could resort to Range.NumberFormat Property. You could set it to be "0.00".

    Here is a sample to set the fomat of all cells in column 2 to be "0.00" for your reference.

            Dim excelApplication As New Microsoft.Office.Interop.Excel.Application
            Dim excelWrkBook As Excel.Workbook
    
            excelApplication.Visible = False
            excelApplication.DisplayAlerts = False
            excelWrkBook = excelApplication.Workbooks.Open(prxls)
            
            Dim ws As Worksheet = excelWrkBook.Sheets(1)
            ws.Columns(2).NumberFormat = "0.00"
    



    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by smhaig Monday, August 4, 2014 5:47 PM
    Monday, August 4, 2014 5:54 AM
    Moderator
  • Thank you.  I was able to manually format this but I had wanted to do this via office .Interop.    Thank you for that answer.

    sm haig

    Monday, August 4, 2014 5:47 PM