Results 1 to 2 of 2
  1. #1
    2 Star Lounger
    Join Date
    Oct 2007
    Thanked 2 Times in 1 Post

    Red face VBA Text / Number format conversion when opening HTML file in Excel


    I've been battling with a problem for a while now and need some help..

    Our company has a VB .NET web-based tool over which I have no control or visibility of the code. It does provide an Excel export button to export tables of data. Although I can save it as a .xls file which can be read into MS Excel, the file actually is actually HTML. The HTML for the heading row is shown below:

    <table cellspacing="0" rules="all" border="1" style="border-collapse:collapse;">
     <tr style="font-weight:bold;">
      <td><b>Opportunity ID</b></td><td><b>Opportunity Title</b></td><td><b>Client Name</b></td><td><b>Sales Lead</b></td><td><b>Market</b></td><td><b>SubMarket</b></td><td><b>Business</b></td><td><b>Sub Business</b></td><td><b>Country</b></td><td><b>Profit Centre</b></td><td><b>Sub Profit Centre</b></td><td><b>Value GBP</b></td><td><b>Value Euro</b></td><td><b>Rate Value GBP</b></td><td><b>Rate Value Euro</b></td><td><b>Bid Status</b></td><td><b>Probability Percentage</b></td><td><b>Framework Enabling</b></td><td><b>Project Start</b></td><td><b>Project End</b></td><td><b>Red Team Date</b></td><td><b>Submission Date</b></td><td><b>ITT Due Date</b></td><td><b>Expected Award Date</b></td><td><b>Award Month</b></td>

    Basically I have a spreadsheet macro which opens this file in Excel, copies and pastes the data into a worksheet in my workbook, removes some of the columns then compares and merges the data with a previous import to identify changes etc.

    The values of the "VALUE EURO" and "VALUE GBP" are correct in the HTML, but when the HTML file is opened in Excel, some values are converted to "General" format and some are converted to numbers. Those that are converted to numbers have some random format ("###.###.###") applied to them and the decimal place has been removed, effectively multiplying the numbers by 100. So what was "5437.944" (i.e. five thousand four hundred euros) in the HTML becomes 5.437.944 in the Excel sheet, which is five million four hundred thousand Euros using european notation, in which a "." is a thousand marker and not a decimal point.

    This formatting is performed automatically by excel on opening the file, and once this has been done, I've seemingly lost the original values as they were in the HTML.

    Is there any way to retain the original numbers? Ideally I'd like to also round the numbers up or down to the nearest Euro in the same process.

    Those numbers which are formatted incorrectly when opening the HTML file in MS Excel are those with more than one decimal place. Those with 2 dps are apparently formatted as "General", whereas those with more than 3dps are formatted as "###.###.###".


  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Pittsburgh, Pennsylvania, USA
    Thanked 342 Times in 335 Posts
    does it help to set the windows default delimiters to what the HTML is using? I think XL reads from those settings.


Posting Permissions

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