Insert File Into Word Document

[DomFino]

Hi Everyone,
I need some help with Inserting A file into Word. I have a document that is a Offer Letter. Much of the text is cut and dry and I used the code below to take the data from Access and put it into the Word Offer Letter.

There are several places in the Offer letter where the paragraph used will be determined by another factor. In my case if a person is Hourly they have one paragraph inserted that pertains to them. If the person is Salary, they have a different paragraph inserted.

My problem is I am not sure how to go about making a selection in Access (where all my data is stored) and have it carry over to Word in the right location of the document.

I have my main document (OfferLTR.doc)
I also have a document that contains just the Hourly text (Hourly.doc)
I also have a document that contains just the Salary text (Salary.doc)

Here is the code I have so far. The code in RED is what I have been toying with but of course it does not work.

[VBA]Private Sub cmdOfferLtr_Click()
'Written by Helen Feddema 4-22-98
'Last modified 8-2-2000 by Jerry Dennison
On Error GoTo ErrorHandler
' Dim appWord As Word.Application - enable this line for early binding
Dim appWord As Object 'this line is for late binding, disable for early binding
' Dim docs As Word.Documents - enable this line for early binding
Dim docs As Object 'this line is for late binding, disable for early binding
Dim strLetter As String
Dim strTemplateDir As String
Dim prps As Object
Dim strDate As String
Dim strFileDir As String

Set appWord = GetObject(, "Word.Application")
strDate = CStr(Date)

If Me!PayType = 1 Then
strFileDir = "C:\HomeForms\"
Selection.InsertFile FileName:=strFileDir & "Hourly.doc"
Else
Selection.InsertFile FileName:=strFileDir & "Salary.doc"
End If

strTemplateDir = "C:\HomeForms\"
strLetter = strTemplateDir & "OfferLTR.doc"

Set docs = appWord.Documents
docs.Add strLetter

Set prps = appWord.ActiveDocument.CustomDocumentProperties

With prps
.Item("MasterID").Value = (Me!MasterID)
.Item("FormDate").Value = (Me!FormDate)
.Item("Salutation").Value = (Me!Salutation)
.Item("EmplFirstName").Value = (Me!EmplFirstName)
.Item("EmplMiddleInitial").Value = (Me!EmplMiddleInitial)
.Item("EmplLastName").Value = (Me!EmplLastName)
.Item("EmplAddress").Value = (Me!EmplAddress)
.Item("EmplCity").Value = (Me!EmplCity)
.Item("EmplState").Value = (Me!EmplState)
.Item("EmplZip").Value = (Me!EmplZip)
.Item("HiringCompany").Value = (Me!HiringCompany)
.Item("HourlyRate").Value = (Me!HourlyRate)
.Item("StartDate").Value = (Me!StartDate)
.Item("NoOfMonths").Value = (Me!NoOfMonths)


End With

With appWord
.Visible = True
.Activate
.Selection.WholeStory
.Selection.Fields.Update
.Selection.MoveDown 'this line should be disabled for early binding
'.Selection.MoveDown Unit:=wdLine, Count:=1 - this line should be enabled for early binding
End With
ErrorHandlerExit:
Exit Sub
ErrorHandler:
If Err = 429 Then
'Word is not running; open Word with CreateObject
Set appWord = CreateObject("Word.Application")
Resume Next
Else
MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
Resume ErrorHandlerExit
End If
End Sub
[/VBA]

I saw a post on this site where they used Autotext to insert specific paragraphs but do not think that will work here. Since I have all the data in Access I would like to have an all VBA solution.

If anyone can help me with the code to insert a file I would be very grateful. Also, the text in Hourly and Salary contain several docproperty fields. The question would be do I insert the file first then populate the properties or vis a versa.
Thanks,
Dom

[fumei]

What, exactly, does not work? I can insertfile using strings like that.

[DomFino]

Gerry,
Thanks for your reply. What I cannot figure out is how to get the code above to actually insert text into word. Do I use a bookmark field in the document where I want the text to appear? Or is there some other field type I should use. As far as I know the code above is okay except I don't know how to get the result into the word document with the rest of the updates. Hope that makes sense.
Dom

[MOS MASTER]

[VBA]If Me!PayType = 1 Then
strFileDir = "C:\HomeForms\"
Selection.InsertFile FileName:=strFileDir & "Hourly.doc"
Else
Selection.InsertFile FileName:=strFileDir & "Salary.doc"
End If[/vba]

appWord

Welcome to VBAX!~

You're automating Word so you should call Word selection in your sub like:[vba]
appWord.Selection.InsertFile FileName:=strFileDir & "Hourly.doc"[/vba]

A bit further in your code you're using the Appword selection as intended in the With block.

HTH,

[fumei]

Uh...yes, that may help....

[DomFino]

Thanks for the tip. I will give that a go as soon as I get out of these darn meetings and post back.
Dom

[DomFino]

Hi Joost & Gerry,
I changed my code as you have suggested. I am now receiving the following error:
Error 91 Object variable or with block variable not set.

I suspect it has to do with me not knowing how to get the now selected file to insert to show in the "With prps" area.

Here is the code I am using now:
[VBA] '************************************************************************** ***
Private Sub cmdOfferLtr_Click()
'Written by Helen Feddema 4-22-98
'Last modified 8-2-2000 by Jerry Dennison
On Error GoTo ErrorHandler
' Dim appWord As Word.Application - enable this line for early binding
Dim appWord As Object 'this line is for late binding, disable for early binding
' Dim docs As Word.Documents - enable this line for early binding
Dim docs As Object 'this line is for late binding, disable for early binding
Dim strLetter As String
Dim strTemplateDir As String
Dim prps As Object
Dim strDate As String
Dim strFileDir As String

Set appWord = GetObject(, "Word.Application")
strDate = CStr(Date)

If Me!PayType = 1 Then
strFileDir = "\\bigguy\users\dom_f\HomeForms\"
appWord.Selection.InsertFile FileName:=strFileDir & "Hourly.doc"
Else
appWord.Selection.InsertFile FileName:=strFileDir & "Salary.doc"
End If

strTemplateDir = "\\bigguy\users\dom_f\HomeForms\"
strLetter = strTemplateDir & "OfferLTR.doc"

Set docs = appWord.Documents
docs.Add strLetter

Set prps = appWord.ActiveDocument.CustomDocumentProperties

With prps
.Item("MasterID").Value = (Me!MasterID)
.Item("FormDate").Value = (Me!FormDate)
.Item("Salutation").Value = (Me!Salutation)
.Item("EmplFirstName").Value = (Me!EmplFirstName)
.Item("EmplMiddleInitial").Value = (Me!EmplMiddleInitial)
.Item("EmplLastName").Value = (Me!EmplLastName)
.Item("EmplAddress").Value = (Me!EmplAddress)
.Item("EmplCity").Value = (Me!EmplCity)
.Item("EmplState").Value = (Me!EmplState)
.Item("EmplZip").Value = (Me!EmplZip)
.Item("HiringCompany").Value = (Me!HiringCompany)
.Item("HourlyRate").Value = (Me!HourlyRate)
.Item("StartDate").Value = (Me!StartDate)
.Item("NoOfMonths").Value = (Me!NoOfMonths)
.Item("PayType").Value = (Me!PayType)


End With

With appWord
.Visible = True
.Activate
.Selection.WholeStory
.Selection.Fields.Update
.Selection.MoveDown 'this line should be disabled for early binding
'.Selection.MoveDown Unit:=wdLine, Count:=1 - this line should be enabled for early binding
End With
ErrorHandlerExit:
Exit Sub
ErrorHandler:
If Err = 429 Then
'Word is not running; open Word with CreateObject
Set appWord = CreateObject("Word.Application")
Resume Next
Else
MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
Resume ErrorHandlerExit
End If
End Sub [/VBA]

[fumei]

1. I hope you are using Set object = Nothing - and are just not posting it.

2. Not sure why you are bothering to make a docs object, when appWord.Documents.Add strLetter works just as well.

3. Still not clear what you are trying to do. Using Documents.Add strLetter does not open that file, it inserts the contents into a new document.

So you are inserting a file into a blank document, then making a new blank document and bringing in the contents of another file, then setting docproperties?

Could you describe exactly what the steps are, and what the purpose is?

[DomFino]

Gerry,

Here goes my explanation.


I have an Access database that holds all of a new or prospective employee?s data (name, SSN, DOB, and ton of other HR type data.

Within Access, I have command buttons that represent the various forms that are used within the organization. W4 form, emergency contact info, etc. plus an Offer Letter. Once the employee or perspective employee?s data in access, the various forms can be printed. Since much of the data is repeated on several forms, taking this Access to word approach cuts down on the data entry.

Up to now, all the forms I have been automating have only used single data elements from Access. In other words, Name, DOB, Telephone, etc. are docproperties on the Word form and this all works fine.

The problem cam in when I hit the offer letter. It has several standard docpropertis like Name, Stat Date, Job Title, etc. However there are several places in the offer letter that require a decision. For example, if the person is Hourly, they have a specific paragraph inserted. If the person is salary, they have a different paragraph inserted. In Access, I have an Option Group with two choices (Hourly & Salary). The option group is called PayType. The specific paragraphs for Hourly and Salary are stored as separate word.docs. So far, I have not been able to get the selected PayType to insert the correct paragraph in the Offer Letter.

There are several other cases like this in the offer letter. Do they want health insurance or not. If yes, one paragraph is inserted, if no another is inserted, etc.

I sure hope this makes sense.

[fumei]

Yes, it makes sense. InsertFile should work. However, if I may make a design suggestion, I would put ALL your text to be inserted into one file, and bookmark them. Then when you want text (and you will want one or the other, or another......), open the file and extract the bookmark text as a string and put it where ever you need it. Use Select Case on the variable required.

Say the document containing all your text is TextInserts.doc

[vba]appWord.Documents.Open Filename:="blah blah\TextInserts.doc"

...... whatever else

Select Case PayType
Case 1
strTextInsert = appWord.Documents("TextInserts.doc") _
.Bookmarks("Hourly").Range.Text
...make sure the Selection is at the right place
Selection.TypeText Text:=strTextInsert
Case 2
strTextInsert = appWord.Documents("TextInserts.doc") _
.Bookmarks("Salary").Range.Text
...make sure the Selection is at the right place
Selection.TypeText Text:=strTextInsert
End Select[/vba]

That way you can maintain all the text to be inserted in one file (easier and can be expanded for other values that need different text), plus less I/O as only one file needs to be opened.

[DomFino]

Hi Gerry, et All,

I was toying (really struggling) with trying to use the CASE example you suggested. I am a novice at best and it may be over my head. Anyway, I was thinking (always a bad thing) about how this could work. You see, the inserted text also has bookmarks that require updating. Therefore, I thought maybe this should be a two-step process. First, open the Offer Letter form and select the options, which would then insert the proper text in the correct location on the document. Second, populate all the bookmarks with the data from Access.


I found the following code on this site and tried to adapt it but am having a problem getting the data to go where I want it in the document. This code simply adds it to the end of the document. The code in red is where I need the inserted file to goto a specific location in the document.


Does this sound like a reasonable approach to solving the problem?

Thanks,
Dom

[VBA]Const AppPath As String = "\OptionsSystem"
Private Sub CommandButton1_Click()
Dim cnt As Control
Dim docpath As String
Application.ScreenUpdating = False
docpath = ActiveDocument.Path & AppPath
For Each cnt In Me.Controls
If TypeName(cnt) = "OptionButton" Then
If cnt.Value = True Then
If Dir(docpath & "\" & cnt.Tag) = "" Then
MsgBox docpath & "\" & cnt.Tag & " doesn't exist.", vbOKOnly + vbExclamation, "Missing File"
Else

With Selection
.EndKey 6
.InsertBreak 2
.InsertFile docpath & "\" & cnt.Tag
End With
End If
End If
End If
Next cnt
Selection.HomeKey Unit:=wdStory
Application.ScreenUpdating = True
Unload Me
End Sub
[/VBA]

[geekgirlau]

Another approach here is to use the build-in IF field in Word.

1. Set your letter as a merge document
2. Make sure your query have a field that identifies whether the employee is hourly or salary
3. Link the merge document to the data source (in this case, your query)
4. Use the IF field in the merge document (there's an "Insert Word Fields" button on the merge toolbar). The field should end up looking something like

[VBA]{ IF { MERGEFIELD "EmpType" } = "Hourly" "hourly paragraph here" "salary paragraph here" }[/VBA]

[fumei]

am having a problem getting the data to go where I want it in the document.

AND

code in red is where I need the inserted file to goto a specific location in the document

Uh...you REALLY REALLY need to figure out how to move through a Word document.

Your red code in fact does not go to a specified location. There is no location specified whatsoever. It uses the .EndKey of the Selection. This is NOT a specified location.

You state the inserted text has bookmarks. This is not an issue at all, as Word handles nested bookmarks quite well. You can have the inserted text AS a bookmark, and it can contain another bookmark. No problem.

Are you perhaps mistaking formfields for bookmarks?

geekgirlau's suggestion is a good one as well.

Plus...people, could we please, pretty please, use underscore characters when we are posting code into the VBA window? Please, pretty please. It is really a drag scrolling left and right as well as up and down. Simple use of the underscore would solve this.

Please?

[DomFino]

Gerry & GeekGirlau'
I played around with geekgirlau's suggestion and it works well. It is also easy to do for an inexperienced guy like myself.

Gerry, you are absolutly correct. I need to figure out how to move through a Word document. That has been my main problem throughout. I just don't know enough about coding to make my initial idea come to reality.

I am going to use the merge option for its simplicity. However, I do have one last question. From within Access, can I open the merged word document using a command button. If I can, then the merged document will be a seemless operation for the user. They complete the Access form and click the command button which will then open the word document with the merged fileds filled in via the query.
Dom

GeekGirlau
I ran into a problem when there are three options to select from in the Word merge "If Than Else". Althogh the Word merge field states "If Than Else" in the drop down, it only give two options (either this or that). I have one area of the offer letter that requires a user to select one of three choises. Any suggestions on how to handle three options?
Thanks,
Dom

[DomFino]

All,
Since my previous post, I figured out how to have three options in a If, Than, Else merge field. I simply had to add three If, Then Else fields with the second option blank on each.

I have created a command button on my Access form to open Word. However I am not sure of the syntax to actually open the correct document after Word opens.

Here is the code behind the command button:
[VBA] Private Sub cmdOfferLTR_Click()
On Error GoTo Err_cmdOfferLTR_Click
Dim oApp As Object
Set oApp = CreateObject("Word.Application")
oApp.Visible = True
Exit_cmdOfferLTR_Click:
Exit Sub
Err_cmdOfferLTR_Click:
MsgBox Err.Description
Resume Exit_cmdOfferLTR_Click

End Sub
[/VBA]

The Word document I want to open is named OfferLTR.doc

[JohnnyBravo]

Plus...people, could we please, pretty please, use underscore characters when we are posting code into the VBA window? Please, pretty please.

Gerry, how do you do that? type the "_" where - exactly?

Many newbies like myself are not aware of what effect it has when we don't use underscores so please bear with us. This is my first time coming to this forum so I certainly wasn't aware of it when I posted my code in my thread earlier. But I can certainly appreciate your fustration as you participate here so much.

[JohnnyBravo]

I am a novice at best and it may be over my head.

DomFino if you thought you were a novice, you should see me over here! I'm completely flying blind and if I had some money to spare, i would go out and buy myself a Dummies for VBA book - cuz i really love this stuff. I just don't know much about it.

Your situation is very similar to the one i'm working on. I will have to see how I can modify your coding to work for me. I'm glad I saw this thread.

http://vbaexpress.com/forum/showthread.php?t=5598

[DomFino]

JohnnyBravo,
I read your post regarding the multiple paragrahs, etc. We are basically working on very similar problems. In my case it is an offer letter with various paragraphs and other text that must be inserted into the offer letter depending upon certain selections a user makes. For example if they want health care, one paragraph is used vs. if they do not want health care. In addition each of the paragraphs has certain text that may or may not be inserted. For example if they want health care, is it for them alone, them and a spouse, or them and a child.

Anyway, with the help of the good folks on this site I have managed to get 99% there. I am hoping that I can put this post to bed very soon. Once I am 100% done with this form, I have a ton more to do but they should be easy compared to this one.
Dom

[fumei]

Not only should you be using the _ character here, in the VBA window, but it also makes it nicer in your own code windows.

Essentially the underscore character ( _ ) says to the VBA parser - "HEY! Go down a line! I'm down there."

I am going to use the underscore character, but I want you to imagine how long this instruction would be without it.
[vba]Sub SetMyRange()
Dim oRange As Word.Range
Set oRange = Selection.Range
oRange.SetRange _
Start:=ActiveDocument.Paragraphs(8).Range.End, _
End:=ActiveDocument.Paragraphs(14).Range.Start
' do something
' for heaven's sake DO something
Set oRange = Nothing
End Sub[/vba]

Here is that line as one line. Kinds stretches out doesn't it?
oRange.SetRange Start:=ActiveDocument.Paragraphs(8).Range.End, End:=ActiveDocument.Paragraphs(14).Range.Start

This is certainly not an extremely long line, there could be much longer ones. Plus, when you are nicely and properly indenting, you could have some deep indents when you would really like to be able to see. Unless of course in the VBE you always have the code window maximized. Ugh, yuck.

I posted, then realized that I had not actually mentioned the context of using the darn underscore.

Uh, hmmmm, it can be used where there is a parse-able break.

[vba]oRange.SetRange _
Start:=ActiveDocument.Bookmarks("blah"). _
Range.Start, _
End:=ActiveDocument.Paragraphs(14).Range.Start[/vba]
See how nicely that looks? You can EASILY glance at it and see what the Start parameter is.

The underscore can be used - this is important - after a dot (.), or before a dot (.) In either case the underscore ALWAYS must have a space preceding it.

So both:
Start:=ActiveDocument.Bookmarks("blah"). _
Range.Start, _

AND

Start:=ActiveDocument.Bookmarks("blah") _
.Range.Start, _

will work. This is because the .dot is a parse-able break. For this reason:
[vba]Start:=ActiveDocument.Boo _
kmarks("blah").Range.Start, _[/vba]
will not work. The space required by the underscore makes gibberish to the parser.