Create a new word document by a macro

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Sridhar Pentlavalli - 23 Mar 2005 09:59 GMT

Hi

I have a word document with some fields of a form in which data can be
entered.

I will be having one standard template (not another document... just
assigned to variables like text_1, text_2 ... in my macro. Please see below
example) in which I will be having some variables like Name, Age, Sex
etc... Get all the values of the varaible from the form fields... fill them
in variables and create a new docuemnt.

For Example, my Final Document should look like:

Name : Sridhar P
Age : 26
Sex : Male

So I will be having a form with three fields. I will read these three
values from the form in to Name, Age and Sex variables.

text_1 = "Name : " & Name
text_2 = " Age : " & Age
text_3 = " Sex : " & Sex

Now write these three lines in to a new docuemnt.

I have written a macro to meet my requirements. The Macro does the
following steps.

1. Read the values in the fiels of a form.

2. Fill the variables.

3. Create new instance of word by the command
Set appWD = CreateObject("Word.Application.10")

4. Create new document by the command
appWD.Documents.Add

5. Copy the text and paste in to this new word docuemnt by commands

Selection.Text = text_1
Selection.Copy
appWD.Selection.Paste

Selection.Text = text_2
Selection.Copy
appWD.Selection.Paste

Selection.Text = text_3
Selection.Copy
appWD.Selection.Paste

6. Save the new document by command
appWD.ActiveDocument.SaveAs "C:\Sri.doc"

7. Close and quit the word by commands
appWD.ActiveDocument.Close
appWD.Quit

The problems with my macro are

1. When I paste the text in the newly created docuemnt, it is getting
pasted in the new document and also it is being pasted in the original
document ( in which I have my form). How to avoid pasting in original
document.????

2. I need to paste these three text variables in three seperate lines. Now
they are comming in a single line. I mean "Enter" has to be somehow pasted
in the docuemnt. How to achieve this.?????

Can any body please help me.......

Thanks in advance....
Sridhar P

Reply to this Message

Chuck - 23 Mar 2005 12:53 GMT

You don't need to copy and paste your variables, and you shouldn't be using
Selection especially since you're working with a Word application object.

Instead work with your object and use Range to insert your variable text.

You can use vbNewLine as a string constant to create a new line in a string.

Lastly, when closing down your objects it's a good idea to erase them as
well (set them to Nothing).

Below is some sample code. HTH

Option Explicit

Sub AddInfo()

Dim appWD As Object
Dim docDoc As Object
Dim rngRange As Range
Dim Name As String
Dim Age As String
Dim Sex As String
Dim text_1 As String
Dim text_2 As String
Dim text_3 As String

On Error GoTo errorhandler:

'Inputboxes used to simulate
'a user form
Name = InputBox("name")
Age = InputBox("age")
Sex = InputBox("sex")

text_1 = "Name : " & Name
text_2 = "Age : " & Age
text_3 = "Sex : " & Sex

Set appWD = CreateObject("Word.Application")

Set docDoc = appWD.Documents.Add

'Position your range object (insertion point)
'For example, this sets the insertion point
'at the beginning of the document
Set rngRange = docDoc.Range
rngRange.Collapse wdCollapseStart

rngRange.Text = text_1 & vbNewLine & _
text_2 & vbNewLine & _
text_3 & vbNewLine

docDoc.SaveAs "C:\Sri.doc"

docDoc.Close wdDoNotSaveChanges

appWD.Application.Quit wdDoNotSaveChanges

Set docDoc = Nothing
Set appWD = Nothing

Exit Sub

errorhandler:

If appWD Is Nothing Then
'do nothing
Else
docDoc.Close False
appWD.Application.Quit
Set docDoc = Nothing
Set appWD = Nothing
End If

MsgBox "error encountered"

End Sub

> Hi
>
[quoted text clipped - 71 lines]
> Thanks in advance....
> Sridhar P

Reply to this Message

Sridhar Pentlavalli - 23 Mar 2005 13:56 GMT

Hi Chuck

Thank you very much for the answer. I tried your code and it works
excallently.

Thanks again for your great help.

Friendly
Sridhar P

Reply to this Message

Chuck - 23 Mar 2005 14:13 GMT

You're very welcome.

> Hi Chuck
>
[quoted text clipped - 5 lines]
> Friendly
> Sridhar P

Reply to this Message

Sridhar Pentlavalli - 23 Mar 2005 15:11 GMT

Can you please help me in identifying the first word in the Sring text_1
assuming that text_1 has one or more than one word.

I need to save the newly created word file with the first name of the
applicant.

Thanks in advance

Reply to this Message

Chuck - 23 Mar 2005 15:49 GMT

There are a variety of ways to get the first word of a string or indeed any
part of any string.

One of the more basic ways is to determine where the first word ends in the
string and to take everything before that point. (You can also split an array
but it might be best if you're starting out to get to grips with basic string
manipulation before advancing to arrays IMHO.)

Dim lngNum As Long
Dim strName As String
Dim strFirstName As String

'Get position of first space in Name string
lngNum = InStr(1, strName, " ")

'Subtract 1 from lngNum to get length
'of first word, take everyting to the left
'up to that point
strFirstName = Left(strName, lngNum - 1)

You can use strings to name documents:

docDoc.SaveAs "C:\" & strFirstName & ".doc"

Be careful though -- you may have more than one applicant with the same
first name so you'd need to differentiate the doc names. You'd need to check
the doc name first to see if it exists, and if it does, try new names until
you find one that isn't already taken. The following code creates a variable
x to act as a counter that adds a number after the person's first name if a
file already exists using that first name.

Dim x As Long

'start at 2 to create new docs named
'"Sri.doc" then "Sri2.doc", "Sri3.doc" etc
x = 2

'Add x to the first name for so long as
'documents exist
Do While Dir("c:\" & strFirstName & ".doc") <> ""
strFirstName = strFirstName & x
Loop

docDoc.SaveAs "c:\" & strFirstName & ".doc"

> Can you please help me in identifying the first word in the Sring text_1
> assuming that text_1 has one or more than one word.
[quoted text clipped - 3 lines]
>
> Thanks in advance

Reply to this Message

Sridhar Pentlavalli - 23 Mar 2005 16:12 GMT

Dear Chuck,

With your great help I completed my project successfully. All the credit
that my leader gave to me actually goes to you. Now Chuck is the name known
in our project as an MS-OFFICE SPECIALIST.

Thank you very much
Sridhar P

Reply to this Message

Chuck - 23 Mar 2005 16:19 GMT

You're too kind. I blush. ;-)

> Dear Chuck,
>
[quoted text clipped - 4 lines]
> Thank you very much
> Sridhar P

Reply to this Message