VBA code in Excel opens Word template and fills bookmarks, but can't get correct formatting

Discussion in 'Word' started by Yomero, Aug 29, 2016.

  1. Yomero

    Yomero

    Joined:
    Aug 29, 2016
    Messages:
    3
    Likes Received:
    0
    Hello,

    I am using an excel worksheet and using VBA to access a .dot template. Both files have the bookmarks, data is transfered correctly. I am using placeholder bookmarks, the one that looks like a beam I.

    My only problem is how to get the correct format in the Word document.

    Example: The bookmark in excel shows 0.71 however the bookmark in word is displaying 0.712669683257918.

    How can I get the bookmark to display only 2 decimals in word or exactly display the same value and format as the bookmark in excel?

    Thanks

    Yomero
     
    Yomero, Aug 29, 2016
    #1
    1. Advertisements

  2. Yomero

    macropod Microsoft MVP

    Joined:
    Mar 2, 2012
    Messages:
    211
    Likes Received:
    15
    That's probably because you're reading the cell's value property instead of it's text property.
     
    macropod, Aug 29, 2016
    #2
    1. Advertisements

  3. Yomero

    Yomero

    Joined:
    Aug 29, 2016
    Messages:
    3
    Likes Received:
    0
    Thanks for your reply,

    Below is the code in the macro for reading and sending the value in the bookmark.

    dottempplate.Bookmarks(xlName.Name).Range.Text = Range(xlName.Value)
     
    Yomero, Aug 29, 2016
    #3
  4. Yomero

    macropod Microsoft MVP

    Joined:
    Mar 2, 2012
    Messages:
    211
    Likes Received:
    15
    As I said, you should be reading the cell's text property instead of it's value property. Thus, instead of:
    dottempplate.Bookmarks(xlName.Name).Range.Text = Range(xlName.Value)
    you should be using:
    dottempplate.Bookmarks(xlName.Name).Range.Text = Range(xlName.Text)
     
    macropod, Aug 30, 2016
    #4
  5. Yomero

    Yomero

    Joined:
    Aug 29, 2016
    Messages:
    3
    Likes Received:
    0
    Thanks!..
    The following changes, worked perfect (Thanks to gmaxey).
    dottempplate.Bookmarks(xlName.Name).Range.Text = Format(Range(xlName.Value), "#.00")

    Case closed....
     
    Yomero, Aug 30, 2016
    #5
  6. Yomero

    macropod Microsoft MVP

    Joined:
    Mar 2, 2012
    Messages:
    211
    Likes Received:
    15
    The solution provided by gmaxey will only work with numbers that are to be output with 2 decimal places. It will delete any currency symbols and thousands separators. It is also inappropriate for text. The solution I posted preserves currency symbols, all the decimal places your numbers display, plus thousands separators and is also appropriate for text.

    It is also evident you've posted about this in another forum. For cross-posting etiquette, please read: http://www.excelguru.ca/content.php?184
     
    macropod, Aug 30, 2016
    #6
    1. Advertisements

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.
Similar Threads
  1. woolmep
    Replies:
    1
    Views:
    630
    woolmep
    Mar 20, 2012
  2. tridda
    Replies:
    3
    Views:
    949
    Geoff_G
    Mar 31, 2014
  3. GordonM
    Replies:
    0
    Views:
    896
    GordonM
    Apr 25, 2014
  4. gerimo
    Replies:
    0
    Views:
    271
    gerimo
    Feb 19, 2015
  5. Michael Kleinmuntz
    Replies:
    0
    Views:
    263
    Michael Kleinmuntz
    Jun 16, 2015
  6. software_ss
    Replies:
    0
    Views:
    219
    software_ss
    Apr 18, 2016
  7. Tall MurphymiN
    Replies:
    1
    Views:
    280
    macropod
    Aug 16, 2016
  8. Sara H.
    Replies:
    1
    Views:
    219
    macropod
    Aug 16, 2016
Loading...