Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: Keeping trailing zero's when transferring from excel to word VBA

  1. #1
    New Member
    Join Date
    Oct 2017
    Posts
    24
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Keeping trailing zero's when transferring from excel to word VBA

    Hi.
    I have this VBA code, where i pull data from word and then i transfer other data back to word.

    I have this code:
    Code:
    Call findAndReplace(Replace(Round(vbaSheet.Cells(i, "U").Value, 2), ",", "."), "Freight:", 3)
    And this code for example takes this from excel "155,40" and returns it to word as this "155.4".

    This problem only occurs when there's a zero at the end of the decimals. But i want the number to be transferred as "155.40".
    What can i add to this?

    Thank you so much in advance
    Last edited by Madter; Nov 14th, 2017 at 04:10 AM. Reason: Edited code

  2. #2
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    46,920
    Post Thanks / Like
    Mentioned
    36 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Keeping trailing zero's when transferring from excel to word VBA

    The issue is that trailing zeroes have no meaning in decimals of numeric values. You need to convert your entry to text.
    Do you always want two decimals?

    Is the issue with the value you are passing in or the value you are getting out of your findAndReplace procedure?
    If the value you are passing in, use the FORMAT function to ensure that, i.e.
    Code:
    Call findAndReplace(Format(Replace(Round(vbaSheet.Cells(i, "U").Value, 2), ",", "."),"0.00"), "Freight:", 3)
    If the value you are getting out, you will need to amend your findAndReplace code to return a String instead of a Number, and use the FORMAT function within that.
    Last edited by Joe4; Nov 14th, 2017 at 08:12 AM.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  3. #3
    New Member
    Join Date
    Oct 2017
    Posts
    24
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Keeping trailing zero's when transferring from excel to word VBA

    Yes, i always need two decimals.
    But the findAndReplace function does return a value as a string.
    This is the findAndReplace code that i have:
    Code:
    Private Sub findAndReplace(val As String, fVal As String, extWord As Long)
    Etc.
    The problem is with the value that comes out of this.

    And i tried your solution, where one number comes out with "," instead of "." - like "944,00" instead of "944.00". And another value comes out like "5324101,01" instead of "53241.01"

  4. #4
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    46,920
    Post Thanks / Like
    Mentioned
    36 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Keeping trailing zero's when transferring from excel to word VBA

    Please post the entire code of your findAndReplace procedure. Without seeing that, we cannot help you.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  5. #5
    New Member
    Join Date
    Oct 2017
    Posts
    24
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Keeping trailing zero's when transferring from excel to word VBA

    Sorry
    Code:
    Private Sub findAndReplace(val As String, fVal As String, extWord As Long)
        With wdDoc
            ' Find value to replace
            .Application.Selection.Find.Text = fVal
            .Application.Selection.Find.Execute
            .Application.Selection.MoveRight unit:=wdWord, Count:=1
            .Application.Selection.MoveRight unit:=wdWord, Count:=extWord, Extend:=wdExtend
            .Application.Selection.TypeText val
            .Application.Selection.MoveDown unit:=wdLine, Count:=1
        End With
    End Sub

  6. #6
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    46,920
    Post Thanks / Like
    Mentioned
    36 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Keeping trailing zero's when transferring from excel to word VBA

    Word VBA is not my thing, but maybe try changing this line:
    Code:
            .Application.Selection.TypeText val
    to this:
    Code:
            .Application.Selection.TypeText Format(val,"0.00")
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  7. #7
    New Member
    Join Date
    Oct 2017
    Posts
    24
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Keeping trailing zero's when transferring from excel to word VBA

    Does the same thing as the modification you gave earlier.

  8. #8
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    46,920
    Post Thanks / Like
    Mentioned
    36 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Keeping trailing zero's when transferring from excel to word VBA

    So, is the issue that the data being pasted back to Word is losing the trailing zero?
    If so, I would recommend handling it in the Word merge field, as shown here: https://www.extendoffice.com/documen...er-format.html
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  9. #9
    New Member
    Join Date
    Oct 2017
    Posts
    24
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Keeping trailing zero's when transferring from excel to word VBA

    The thing is that the macro is in excel, as a master excel file and it handles all the data transfer. The word files, on the other hands, are received every day and are therefore different for every day. It isn't the most difficult task in the world, to manually add the decimals in the word file, after the data transfer. But i just wanted to make the VBA complete. Otherwise it works perfectly, except for this.

  10. #10
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    46,920
    Post Thanks / Like
    Mentioned
    36 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Keeping trailing zero's when transferring from excel to word VBA

    We have a person here who is an expert at controlling Word from Excel. I will reach out to him and see if he can help.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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