open data from excel userform in word document

[sessionone]

Hi all,

Not sure if this is the right forum, so sorry if it's not.

I have an excel userform, where information entered into textboxes is saved in particular cells. However, not all info must be saved in the spreadsheet, but everything must be printed and saved as a word document.

So, I have a button, which says "open report", and what I'd like it to do is to open a word document, where info from the textboxes appears in certain places in the document.

I suppose, I would need to create a word template with something like bookmarks or docvariables. But I've heard that the bookmarks delete themselves, and docvariables are way too complicated.

Is there a simple way to do this? My vba knowledge is very very limited.

Thanks for your help,
Sessionone

 

[SkipVought]

Have you considered saving your data in a TABLE (sheet) in Excel and then connecting the data using the MailMerge feature in Word?

I used a technique very similar to this 15 years ago, where I would enter customer data for a personalized children's book in Excel and then open my Word document that had the MailMerge fields in the proper locations in my document. I had View Mail Merge Data active so that I could see the actual data and not the Field Names. Go To Record, gets you to the row of data that is relevant, or this this case it would be the LAST ROW.

No VBA code required.

BTW, I had over a dozen different personalized children's books, so I had a separate Word document for each.

Easy Peasy!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[fumei]

Skip's suggestion is probably where you can start.

However, yes, "what I'd like it to do is to open a word document, where info from the textboxes appears in certain places in the document." is quite do-able. Yes, it would t6ake some VBA but not a huge amount. Depending of course on exactly you are wanting to achieve.

But I've heard that the bookmarks delete themselves, and docvariables are way too complicated.

The first is true, but only if you do not know how to avoid this...and it is not hard.

The second (docvariables are way too complicated), well, not really. They are variables like any other variables.

You create and name them.....and then give them a value.

That is it.

Gerry

 

[sessionone]

Hi,


"Have you considered saving your data in a TABLE (sheet) in Excel and then connecting the data using the MailMerge feature in Word?"

Well, I thought about that, but I don't really need everything that I enter in the userform to be saved in the table. So, I figure, the data for the word document has to come from the form, but not from the table.

Fumei, I'm looking for the most simple solution. I think bookmarks are more simple than docvariables, and if there's a way to make them do what I want them to do, then I should go for them. Perhaps there is even a better way to do this? At the moment, I need the button to open a particular document and fill it with data from the userform.

Cheers,
Sessionone

 

[SkipVought]

"but I don't really need everything that I enter in the userform to be saved in the table. "

So only save what you need from the userform in the table.

How 'bout save it in a DIFFERENT table. I dunno, just save the most recent data so you only have ONE ROW in your other table.

You wanted a 'simple' solution. I gave you one. It works well. I cannot understand your heartburn.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[sessionone]

thanks skip for your suggestion. what i meant was that i need some info from the userform saved in the table but all of the info must go into the word document. so, if I use the table as the source for the word doc, then not all info will be in the doc.

"How 'bout save it in a DIFFERENT table. I dunno, just save the most recent data so you only have ONE ROW in your other table."

Not sure what you mean by that. The spreadsheet will contain a lot of info, which will be spread through different sheets. So I'd be reluctant to use more tables, because I imagine it will be confusing for the user.

Anyways, I was able to sort out the word doc by using bookmarks and some code. It's very amateur, but at the moment it does nearly everything I want it to do.

Which leads me to another problem. I have an "open form" button in the sheet, and when I click it the form opens with the data that is stored in particular cells. And when I input new info, it changes the info in those cells. I think the problem is that the control source of textboxes on the userform points to those particular cells. I don't know how to load a blank form and to tell it to store new info in the next row.

I'd appreciate any input on that, or at least some pointers so I know what to look for and where.

Thanks again,
sessionone

 

[sessionone]

Hi,

No worries. Got the last one sorted.

Thanks,
sessionone