losing zeros following decimal point

[MallorcaPete]

I am sending data from an excel sheet to a word template (Office 2007). In the excel sheet, 2 of the fields are formatted as Euro currency with 2 decimal points and show as €111.00. When they copy to the word document they are reduced to €111 and the 2 zeros are missing. I can’t change the template to [bookmark].00 because sometimes there is numbers after the decimal point, ie, €111.69.

Any advice please.

Please Login or Register  to view this content.

[Arkadi]

does "Intersect(thisrow, Header.EntireColumn)" represent the euro value?

[MallorcaPete]

I believe that this part selects the contents from all of the [] bookmarks. Two of those are shown here.
sample of sheet.jpg

[Arkadi]

OK I have an idea... But what is the text inside the [ ] in the word template? Where the euro value is posted.

[MallorcaPete]

Both text inside are as in the header titles, as in the before and after images here.
Template Before filling
before.jpg
Tempate after filling
After.jpg

[Arkadi]

So I figured best way is to check the word template, and if to the left of the [ there is a € then format with 2 decimals, otherwise paste as normal.

replace this line:

Please Login or Register  to view this content.

with these:

Please Login or Register  to view this content.

[MallorcaPete]

Thank you Arkadi, you are a star. It works as I wanted now. Thanks again.

Am I right to presume, if there i no € before the [, then the new line would be

Please Login or Register  to view this content.

? I'm trying to understand and not keep bothering people.

Also, if you don't mind me asking, I have put this at the top of the template C:\Users\Me\Enquiries\[Enq_Id]-[First_Name]-[last_Name].doc , So that I cut and paste it to save the file. Can I add something in the code, to make this automatic?

Thank you for your help.

[Arkadi]

MallorcaPete, my code as provided checks for euro symbol, and the second part (else...) Takes care of when there is no euro symbol. You don't need extra code. Does that make sense? If not, then I can explain better, but you don't need to add anything. Estoy a punto de irme al trabajo, te contesto la segunda pregunta en cuanto llegue.

[MallorcaPete]

Thank you Arkadi, Yes, it makes sense, so I could use this whenever there is a € before the value, great.

I will look forward to your reply for the second part, although my Spanish is not very good, I have not been her very long. It's more like Spanglish at the moment.

[Arkadi]

Hi pete, sorry about the spanish. I just assumed with the name and being in Spain... my bad.
To elaborate on the earlier reply, my little piece of code is like this:

Please Login or Register  to view this content.

[Arkadi]

As I think about your second question, I am not sure I fully understand.... you have put that text at the top of the template... but you would like to make the saving of the word document automatic with that file name, I assume in the Excel macro code? I assume Enq_ID, First_Name, and last_name are headers in your excel sheet?

[MallorcaPete]

Yes Arkadi, That text is the first line of the template. It has bookmarks [] that are populated by the excel sheet headers and works perfect.

When the template opens, I select that text and "cut" it, I then select "Save as" and paste tat text into the save file name box.

If the macro can do this, I would be able to simply check the documet is ok and then close it.

Thanks for taking your time to help.

[Arkadi]

Try this version then:

Please Login or Register  to view this content.

EDIT!! Your template no longer needs "C:\Users\Me\Enquiries\[Enq_Id]-[First_Name]-[last_Name].doc" at the top, the code does not cut/paste, it just uses that format from inside the macro itself.

[MallorcaPete]

Thank you so much Arkadi, That is exactly what I wanted it to do. I added an ' in front of "wD.Close 0" and "wA.Quit" and it's perfect, I have left them in place, for future reference. Thanks again Arkadi.

[Arkadi]

You are very welcome
Please mark the tread as solved if you are satisfied?

[MallorcaPete]

Marked as solved, but couldn't find "Mark as solved, quickly and very profesionaly" . Thanks Arkadi

[Arkadi]

Thanks Pete, very kind words. That's what the "Add Reputation" button is for I guess

[MallorcaPete]

Found it