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

    Joined:
    Mar 2, 2012
    Messages:
    134
    Likes Received:
    9
    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

    Joined:
    Mar 2, 2012
    Messages:
    134
    Likes Received:
    9
    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

    Joined:
    Mar 2, 2012
    Messages:
    134
    Likes Received:
    9
    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?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. woolmep
    Replies:
    1
    Views:
    590
    woolmep
    Mar 20, 2012
  2. tridda
    Replies:
    3
    Views:
    888
    Geoff_G
    Mar 31, 2014
  3. GordonM
    Replies:
    0
    Views:
    831
    GordonM
    Apr 25, 2014
  4. gerimo
    Replies:
    0
    Views:
    233
    gerimo
    Feb 19, 2015
  5. Michael Kleinmuntz
    Replies:
    0
    Views:
    224
    Michael Kleinmuntz
    Jun 16, 2015
  6. software_ss
    Replies:
    0
    Views:
    175
    software_ss
    Apr 18, 2016
  7. Tall MurphymiN
    Replies:
    1
    Views:
    209
    macropod
    Aug 16, 2016
  8. Sara H.
    Replies:
    1
    Views:
    169
    macropod
    Aug 16, 2016
Loading...