Exporting UserForm data to Word

Hey Excel Forum ppl!

I've been reading your forum for a few weeks now and it has been very useful for me. I'm not very experienced with Excel + VBA but learning fast. (Only 1 month studies behind.. )

I'm wondering if you guys could give me some advice since I came to a dead end with my project. I will try to explain my problem as clearly as possible:

I'm working on a tool which picks up values (=text / ComboBox items) from a UserForm. The idea is that the typed text and chosen item from ComboBox are exported from Excel to existing Word document, and to certain points in it. The problem is that I can't figure out how to export the data from Excel to existing Word template. And of course I would also like to know how to export the data to certain points in the Word document

Here's a simplified example attached:

ExistingTemplate.docexcelforum1.xlsm

Thank you in advance!

-ODeveloper

Hi ODeveloper and welcome to ExcelForum,

I made a few behind the scenes changes to your Userform:
a. Changed Control Names
b. Initialized the 'CommandButton' to 'Not Visible'
c. Changed 'ComboBox' attributes to 'Match First Letter' and 'Match Required'.
d. Changed 'Tab Order' so the 'Name' TextBox would have initial focus.

Try the attached file which implements the following code.
UserForm code:

Please Login or Register  to view this content.

Ordinary Module Code such as Module1 to ModExcelToWord:

Please Login or Register  to view this content.

It is a best practice to declare all variables. If you misspell a variable in your code, VBA will silently assume it is a Variant variable and go on executing with no clue to you that you have a bug. Go to the VBA development window, click Tools, Options, and check "Require Variable Declaration." This will insert the following line at the top of all new modules:

Please Login or Register  to view this content.

This option requires all variables to be declared and will give a compiler error for undeclared variables.

Lewis

Hey Lewis, and thanks for a quick answer!

Your code works well! It just takes a little time for a newbie like me to chew that all Thanks for the comments within the code as well, makes it much easier to understand. Do you know if it's possible to add custom text through userform to Word documents header & footer? Let's say that I would like to have the #Name from textbox to header and footer, any solution?

Thx in advance!

-ODeveloper

I would like to have the #Name from textbox to header and footer, any solution?

See the attached file. It attempts to replace each keyword in the:
a. Main Body
b. Header
c. Footer

I figured the general 'replace all' was better than replace some keywords in the 'Main Body', and/or some in the 'Header', and/or some in the 'Footer'. The code can be modified to do each section separately if that is what you want.

I want to thank MacroPod in post #5 of the following link, for header/footer replacement code: http://www.msofficeforums.com/word-v...er-footer.html

In an ordinary code module:

Please Login or Register  to view this content.

Lewis

I finally got that all implented to my project, and it works very well so far!

A thousand thanks to you Mr. Lewis, I couldn't have done it without your help!

I might have further questions following, so lets keep this thread open, Thanks!

BR,
ODeveloper

Hi LJMetzger and others,

This is and old topic, but maybe you could help me with an issue over here.

According to these files and code:

I have to make another userform which uses different template in the same workbook.

1. Can I make another Module to this same workbook, and make this new userform only to use the another Module? I mean is it possible to make one userform to use only one module? If so, How can I "link" them together?

2. Or is it possible to make a macro which changes the template what is going to be used?

Thanks in advance!

1. Can I make another Module to this same workbook, and make this new userform only to use the another Module? I mean is it possible to make one userform to use only one module? If so, How can I "link" them together?

2. Or is it possible to make a macro which changes the template what is going to be used?

If I understand the question correctly, you are concerned about a UserForm using the wrong code.

You should not have any problems if you follow and understand a few simple rules:
a. All the bulit in UserForm routines are automatically Private. That means they can only be seen by that specific UsrForm.
b. In general, my personal design guidelines are to make other routines inside the UserForm code module also Private. I try to have as little code inside the UserForm module as possible.
c. I usually make routines in other modules 'Public' (accessible to anyone). To avoid a conflict between two Public routines, requires careful use of Routine Names.

For example instead of a function called UserFormVerifyData(), if I would name the routine(s) UserForm1VerifyData(), UserForm2VerifyData() etc.

I hope this helps.

Lewis

I think you just answered my question!

Thanks again Lewis!

-ODeveloper