none
MS word table numbers shown in scientific format RRS feed

  • Question

  • I have a VBA program that reads invoice data from an excel workbook and does a mail merge into a word document to create printed invoices.  In the table that shows the item data, It has (after a couple of years of normal operation) now started displaying the item number, which is 12 digits long, in scientific format.  (e.g. 2.7122147527e+011).  In the original excel worksheet the relevant cell is formatted so that the number is shown correctly in normal form.  To complicate matters further, if there is a second item in the word table, its item number is shown normally (i.e. not in scientific format), despite the excel cell format being identical for both item numbers.

    The invoice in word is generated from a .dotm template, so I am seeking a way of formatting the relevant cells in the word table in the template to be in non-scientific format.  (I have tried changing the column width and a few other general format changes without success.)  It may be a simple matter of finding a way to format cells in MS Word, or there may be a VBA approach I can use, but any guidance will be gratefully received.
    Sunday, March 23, 2014 11:05 AM

Answers

  • The chances are that you are experiencing a "mixed data types" problem. Word mailmerge gets its data from Excel via OLE DB by default (ODBC is similar, but DDE is different). Where a column has mixed data types, OLE DB will decide a data type for the column, and convert/coerce values that have other types. By default, OLE DB looks at the first 8 rows in the column to determine the data type. If, for example, the column contains a text value in the first 8 cells, you may find that the scientific notation value is treated as a text string by the provider. If the column just contains numbers, it may be treated as a number. (I would have to re-check exactly what the provider does in each situation).

    That's why the problem can appear arbitrary. It's because it /is/ arbitrary! (i.e. Excel files are basically unreliable data sources for merges. Further, exporting data to (say) a text file may not help because similar conversion/coercion is performed if the Jet/ACE provider is used to read a plain text file. Although it is possible to tweak the Windows registry and/or use .odc files to modify Word's behaviour, it may be easier to insert rows at the top of your Excel table to ensure that columns are interpreted the way you expect.


    Peter Jamieson

    • Marked as answer by AndyColRomsey Thursday, March 27, 2014 9:11 AM
    Wednesday, March 26, 2014 12:49 AM
  • Convert to text doesn't always do the trick, but there a slightly more aggressive approach is to select a column, then use the Data tab, Data Tools group, Text to Columns option to convert a column's data. If you are merging your own data, that's worth trying.


    Peter Jamieson

    • Marked as answer by AndyColRomsey Saturday, May 3, 2014 8:48 AM
    Friday, May 2, 2014 6:49 PM

All replies

  • Simplest solution is in the VBA macro, instead of copying the cell, copy the text so the data is pre-formatted.

    Use strValue=xlr.Text rather than currVal=xlr


    Rod Gill
    Author of the one and only Project VBA Book
    www.project-systems.co.nz

    Sunday, March 23, 2014 8:15 PM
  • Rod

    Thanks for looking at this.  The problem is that the data that causes the problem is never declared as a variable.  It starts life as a 12 digit number in a .csv file that is downloaded from the internet, and that number is shown in scientific format.  The contents of that .csv file are copied into a workbook with a different layout, and in that workbook the relevant column is formatted as "number" to make it show as a proper (l12 digit) number.  If I leave the column as "general" or "text" it reverts to scientific format again.  To create the invoices I use a mail merge in MS Word, reading the data from the workbook file.

    To complicate matters further, the most recent run of the program, with new data, has worked fine, with the mail merged word document showing the Item numbers correctly.  I can see no obvious difference in the style of the data in each original .csv file.  So I suspect that it will prove impossible to track down the source of the problem.  For the moment the problem seems to have gone away;  I hate it when that happens!

    Andy C

    Monday, March 24, 2014 8:22 AM
  • The chances are that you are experiencing a "mixed data types" problem. Word mailmerge gets its data from Excel via OLE DB by default (ODBC is similar, but DDE is different). Where a column has mixed data types, OLE DB will decide a data type for the column, and convert/coerce values that have other types. By default, OLE DB looks at the first 8 rows in the column to determine the data type. If, for example, the column contains a text value in the first 8 cells, you may find that the scientific notation value is treated as a text string by the provider. If the column just contains numbers, it may be treated as a number. (I would have to re-check exactly what the provider does in each situation).

    That's why the problem can appear arbitrary. It's because it /is/ arbitrary! (i.e. Excel files are basically unreliable data sources for merges. Further, exporting data to (say) a text file may not help because similar conversion/coercion is performed if the Jet/ACE provider is used to read a plain text file. Although it is possible to tweak the Windows registry and/or use .odc files to modify Word's behaviour, it may be easier to insert rows at the top of your Excel table to ensure that columns are interpreted the way you expect.


    Peter Jamieson

    • Marked as answer by AndyColRomsey Thursday, March 27, 2014 9:11 AM
    Wednesday, March 26, 2014 12:49 AM
  • If you're reading cell by cell from Excel, format the column first to "#,##0.00" format then read the .Text property.

    Rod Gill
    Author of the one and only Project VBA Book
    www.project-systems.co.nz

    Wednesday, March 26, 2014 5:29 AM
  • Peter

    Thank you.  What you describe is almost certainly the reason for my problem.  I am nowhere near brave or competent enough to muck about with the registry, so I will look at the idea of inserting a few lines at the top of the spreadsheet to get the columns reading the right way.

    With thanks

    Andy C

    Thursday, March 27, 2014 9:19 AM
  • Rod

    Thanks for looking at this again.  I could, as you suggest, format the cells to #,##0 (the item numbers are integers), but since the cell value is read by the mail merge procedure in the word document I don't see how I would be able to read the .text property as part of the mail merge.  I could abandon the mail merge and use VBA to read each cell in turn, but I would then have to remove all the commas afterwards.  The procedure already takes a minute or so to run (quite a lot of invoices to generate) so I am reluctant to extend it further.  However, if Peter Jamieson's suggestion above does not work I shall try that approach next.

    with thanks

    Andy C

    Thursday, March 27, 2014 10:02 AM
  • No, no, no, don't change anything in the registry.  Just format the relevant Excel column(s) as Text and do the import. 

    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Friday, May 2, 2014 4:13 PM
  • ryguy72

       Thanks for looking at this.  Fear not, I'm not going near the registry!  I have tried formatting the columns as text (see my reply to Rod Gill March 24).  When I do so, the number is displayed in scientific format.  Only by formatting as number can I get a normal display.  For the moment the problem has gone away when the top lines are seen as numbers during the mail merge.

    Andy C

    Friday, May 2, 2014 6:02 PM
  • Convert to text doesn't always do the trick, but there a slightly more aggressive approach is to select a column, then use the Data tab, Data Tools group, Text to Columns option to convert a column's data. If you are merging your own data, that's worth trying.


    Peter Jamieson

    • Marked as answer by AndyColRomsey Saturday, May 3, 2014 8:48 AM
    Friday, May 2, 2014 6:49 PM
  • Peter

        Thanks for your suggestion.  I came across a similar problem some time ago when text fields were being merged into MS Word documents as zeros, and this trick worked then.  It appears to work in this context too, in that it successfully changes the format from number to text without reverting it to scientific format.  (Doing the format change to text using the Home ribbon number box did change the long numbers to scientific format.)  Because it has been an intermittent fault I will await events, but so far it looks like an answer.

    Thanks

    Andy C

    Saturday, May 3, 2014 8:48 AM
  • A final word on this problem, which is difficult to fix because it appears intermittently.  The Text to Columns approach worked for most of the time, but still, on occasions, the data would be read as the wrong type.  So I finally took the time to write code to generate two dummy rows at the top of the data that were formatted in an appropriate and unambiguous way for each column.  I then had to generate more code to delete the mail merge documents generated from those rows, and to hide the rows from the user to avoid confusion.

    So my code ends up with three attempts to fix the problem.  One to format the cells in the appropriate way, two to use the Text to Columns method and three to generate dummy rows.  After a couple of months it now seems to work without a problem, but it is not pretty coding, and I suspect that it slows it down marginally.  Each of the approaches has worked in some cases, but only by using all three have I arrived at a solution that seems to work in all cases.

    Andy C

    Wednesday, July 2, 2014 11:33 AM