how to populate word template by using VBA code for userform

[abhilashasingh]

Hi,

I have created a userform to populate my template.

When I open the template and fill the userform, text enters into the specified bookmarks.

But when i reopen the template, the userform does not pop up.

what can be done to open the userform whenever i open my template.

Please suggest.

[HansV]

Welcome to the Lounge!

If you want the userform to be displayed whenever a new document based on the template is created, use code in the Document_New event procedure in the ThisDocument module of the template:

Private Sub Document_New()
  MyUserForm.Show
End Sub

where MyUserForm is the name of your form.

If you want the userform to be displayed whenever an existing document based on the template is opened, use code in the Document_Open event procedure in the ThisDocument module of the template:

Private Sub Document_Open()
  MyUserForm.Show
End Sub

You can use both event procedures.

[abhilashasingh]

Thanks Hans for replying so soon.

The code which i uesd to populate the word document is;

Private Sub CommandButton1_Click()
Application.ScreenUpdating = False
With ActiveDocument
[highlight=yellow].Bookmarks("product_name").Range.Text = TextBox1.Value[/highlight]
.Bookmarks("doc_number").Range.Text = TextBox2.Value

End With

UserForm1.Hide

End Sub

Now, when i insert the code, which you have suggested, some error happens in the above highlighted line.

Error:"requested memeber of collection does not exist"

what I am doing wrong.


[quote name='HansV' post='768203' date='30-Mar-2009 11:15']Welcome to the Lounge!

If you want the userform to be displayed whenever a new document based on the template is created, use code in the Document_New event procedure in the ThisDocument module of the template:

Private Sub Document_New()
  MyUserForm.Show
End Sub

where MyUserForm is the name of your form.

If you want the userform to be displayed whenever an existing document based on the template is opened, use code in the Document_Open event procedure in the ThisDocument module of the template:

Private Sub Document_Open()
  MyUserForm.Show
End Sub

You can use both event procedures.[/quote]

[HansV]

That error message would seem to indicate that the active document does not contain a bookmark named product_name.

Are you sure that the document contains a bookmark of that name?

[abhilashasingh]

Ya actually when i am entering the code,that you have suggested, to open the form whenever I open the document, it creates problem.

can i send you the document with coding and all, so that you can execute it and understand the context.

otherwise, earlier there was no such problem.


[quote name='abhilasha' post='768804' date='02-Apr-2009 12:46']Thanks Hans for replying so soon.

The code which i uesd to populate the word document is;

Private Sub CommandButton1_Click()
Application.ScreenUpdating = False
With ActiveDocument
[highlight=yellow].Bookmarks("product_name").Range.Text = TextBox1.Value[/highlight]
.Bookmarks("doc_number").Range.Text = TextBox2.Value

End With

UserForm1.Hide

End Sub

Now, when i insert the code, which you have suggested, some error happens in the above highlighted line.

Error:"requested memeber of collection does not exist"

what I am doing wrong.[/quote]

[HansV]

You can attach the document to a reply, zipped if necessary (click the Browse... button below the editing area, and click UPLOAD when you have selected the file to attach).

[abhilashasingh]

I'm not able to attach my document frommy system.

is there any other method to do so.



[quote name='HansV' post='768828' date='02-Apr-2009 16:47']You can attach the document to a reply, zipped if necessary (click the Browse... button below the editing area, and click UPLOAD when you have selected the file to attach).[/quote]

[HansV]

[quote name='abhilasha' post='768990' date='03-Apr-2009 08:51']I'm not able to attach my document from my system.[/quote]
Why not?

[StuartR]

I suspect that your problem is that the line
[background=yellow;].Bookmarks("product_name").Range.Text = TextBox1.Value[/font]
has replaced the bookmark with the text from your form. If you try to run the same code a second time then it will generate an error because the bookmark no longer exists.

If this is your problem then the solution is to replace this line with a few lines of code that replace the text and then put the bookmark back in place, I am sure there is an example of this in the lounge but I can't find it at the moment. Hopefully someone will post a pointer for you, if not then I will create some code for you later today.

[abhilashasingh]

when i click upload,it says " upload failed,you are not permitted to upload this type of file".

i dont understand what does it mean by " this type of file".

size of file is less than 2 MB and it is word 2003 template


[quote name='HansV' post='768992' date='03-Apr-2009 12:29']Why not?[/quote]

[abhilashasingh]

yes, you are right.

When i reopen the document, no bookmark is therein the document.

please send me the code.

i have searched the forum but couldnt find the example.


[quote name='StuartR' post='768994' date='03-Apr-2009 13:10']I suspect that your problem is that the line
[background=yellow;].Bookmarks("product_name").Range.Text = TextBox1.Value[/font]
has replaced the bookmark with the text from your form. If you try to run the same code a second time then it will generate an error because the bookmark no longer exists.

If this is your problem then the solution is to replace this line with a few lines of code that replace the text and then put the bookmark back in place, I am sure there is an example of this in the lounge but I can't find it at the moment. Hopefully someone will post a pointer for you, if not then I will create some code for you later today.[/quote]

[StuartR]

I found the code I was looking for. It is in Post 767232. Add the UpdateBookmark subroutine from the code block in that post to your module, and then replace
[background=yellow;].Bookmarks("product_name").Range.Text = TextBox1.Value[/font]
with
[background=yellow;]UpdateBookmark BmkNm:="product_name", NewTxt:=TextBox1.Value[/font]

[abhilashasingh]

Thank you somuch Stuart for helping me out.

Now the userform reopens whenever I open my document.

It accepts the input, but by clicking on ok, no change happens in the document. Text remains the same.


[quote name='StuartR' post='768997' date='03-Apr-2009 13:29']I found the code I was looking for. It is in Post 767232. Add the UpdateBookmark subroutine from the code block in that post to your module, and then replace
[background=yellow;].Bookmarks("product_name").Range.Text = TextBox1.Value[/font]
with
[background=yellow;]UpdateBookmark BmkNm:="product_name", NewTxt:=TextBox1.Value[/font][/quote]

[StuartR]

[quote name='abhilasha' post='768998' date='03-Apr-2009 09:45']It accepts the input, but by clicking on ok, no change happens in the document. Text remains the same.[/quote]
You really are going to have to post your template so we can see what you have done.

[HansV]

[quote name='abhilasha' post='768995' date='03-Apr-2009 09:52']when i click upload,it says " upload failed,you are not permitted to upload this type of file".

i dont understand what does it mean by " this type of file".

size of file is less than 2 MB and it is word 2003 template[/quote]
You can't post a .dot file directly , but you can zip it and attach the zip file.