VBA Code Help - Copy from one document into another using forms

[MrConfused10]

Hi all,

I'm hoping that someone will be able to help me. Firstly, I warn you I am very new to VBA and therefore what I have achieved so far is based on lots of time spend hunting around on the internet. :rotlaugh:

An explanation :

I am currently creating a series of documents that are for use in my business. The documents are reports that will be sent out to clients. In my industry legislation is changing all the time and therefore I need to ensure that I keep my documents up to date all the time. I have created a series of documents that link to a master excel spreadsheet, you simply update the spreadsheet and that in turn updates the documents templates.

In several of the reports I need to provide the user with the option to enter a predetermined block of text by selecting either option 1, 2 or 3. By making the selection the correct text is then entered.

I have built the following code that simply provides the user with the three options, in my example optISA1, 2 and 3. The selection points to a word document (that contains a table) copies the content, and then pastes the information in the active document.

The Problem :

The copy/paste seem to work ok, but the paste needs to be in a specific area of the original report. I have therefore bookmarked the section but it will not paste to the bookmark.

Could someone please look at this and correct me where I am going wrong, or offer some help. It will be greatly appreciated.


Dim strISA As String
If optISA1 = True Then strISA = Word.Documents.Open("F:\Neil H\Test 1.doc")
Word.ActiveDocument.Select
Word.Selection.Copy
If optISA2 = True Then strISA = Word.Documents.Open("F:\Neil H\Test 2.doc")
Word.ActiveDocument.Select
Word.Selection.Copy

If optISA3 = True Then strISA = Word.Documents.Open("F:\Neil H\Test 3.doc")
Word.ActiveDocument.Select
Word.Selection.Copy

Word.ActiveDocument.Close (False)
With ActiveDocument
Word.Selection.Paste
.Bookmarks("ISAType").Range.Text = strISA
End With
Application.ScreenUpdating = False


Many thanks

- Neil

:hi:

[Tinbendr]

You never assign a value to strISA.

I would rewrite as
Dim strISADoc As Document
Dim strISA As String
If optISA1 Then
Set strISADoc = Word.Documents.Open("F:\Neil H\Test 1.doc")
strISA = strISADoc.Range
strISADoc.Close (False)
ElseIf optISA2 Then
Set strISADoc = Word.Documents.Open("F:\Neil H\Test 2.doc")
strISA = strISADoc.Range
strISADoc.Close (False)
ElseIf optISA3 Then
Set strISADoc = Word.Documents.Open("F:\Neil H\Test 3.doc")
strISA = strISADoc.Range
strISADoc.Close (False)
End If

With ActiveDocument
.Bookmarks("ISAType").Range.Text = strISA
End With
'if you want to reserve the bookmark. (I highly recommend you do.)
Dim BMRange As Range
'Identify current Bookmark range and insert text
Set BMRange = ActiveDocument.Bookmarks("ISAType").Range
BMRange.Text = strISA
'Re-insert the bookmark
ActiveDocument.Bookmarks.Add "ISAType", BMRange


I would also assign the Report document to an object. When you start opening other documents, it can easily get mixed up fast. (Would the REAL Activedocument please stand up!)

You don't say how the report is generated. If you're adding by template then
Set MyReport = Documents.Add (MyReport.dot)

If opening a document already created then
Set MyReport = Documents.Open(MyReport.doc")

Now, when you refer to MyReport, it's always going to refer to that document. When you refer to strISADoc, it's always going to refer to that document.

[fumei]

Tinbendr's suggestions are all good. Particularly the use of ElseIf, rather than multiple If...Then. Of course this depends on whether the choices are in fact exclusive.

Pay attention to his suggestions about the use of objects. This is well worth grasping, as it is a major concept.

While technically correct, I would recommend you use explicit terms. Tinbendr's:
strISA = strISADoc.Range
is technically correct, but ONLY because the default property is .Text. That means VBA parses the above to actually be:
strISA = strISADoc.Range.Text
This is important because, again technically, variables MUST match, and in the instruction it is:

strISA String! = strISADoc.Range (Range)

Except that, again, the default property is .Text (a string), so it does work. But it is better to always be explicit.
strISA (string) = strISADoc.Range.Text(string)
However, the most significant advantage of Tinbendr code is that he replaces your:
Word.ActiveDocument.Select
Word.Selection.Copy
which requires a Paste, with the use of Range:
strISA = strISADoc.Range.Text
which puts the document contents (via its Range) into strISA. Notice that his code does NOT use a Copy and Paste.

You may (depending on your requirements and your source docs) have a possible alternative by using INCLUDETEXT fields. Here is an example of how this could work.
Sub Yadda()
Dim strTest1 As String
Dim strTest2 As String
Dim strTest3 As String
Dim r As Range

strTest1 = _
" INCLUDETEXT ""F:\\Neil H\\Test 1.doc"" \* MERGEFORMAT "
strTest2 = _
" INCLUDETEXT ""F:\\Neil H\\Test 2.doc"" \* MERGEFORMAT "
strTest3 = _
" INCLUDETEXT ""F:\\Neil H\\Test 3.doc"" \* MERGEFORMAT "

Set r = ActiveDocument.Bookmarks("ISAType").Range

' VERY important to have show codes off!
ActiveWindow.View.ShowFieldCodes = False

If optISA1 Then
Call DoStuff(r, strTest1)
ElseIf optISA2 Then
Call DoStuff(r, strTest2)
ElseIf optISA3 Then
Call DoStuff(r, strTest3)
End If

' reset the bookmark
ActiveDocument.Bookmarks.Add "ISAType", r

End Sub

Sub DoStuff(r As Range, strIn As String)
With r
.Fields.Add Range:=r, Type:=wdFieldEmpty
' this gets the empty field INSIDE the bookmark
.MoveEnd Unit:=wdCharacter, Count:=1
.Fields(1).Code.Text = strIn
.Fields(1).Update
End With
End Sub


What this does:

It creates three string variables with what will be the INCLUDETEXT field codes text.

It is VERY important that you use proper syntax. Notice the double quotation marks and slashes:
" INCLUDETEXT ""F:\\Neil H\\Test 3.doc""

It makes a Range variable of the bookmark "ISAType".

Depending on the option button test, it call a sub-routine - DoStuff - passing the Range of the bookmark (r), and the string to put into the field code (strTest1 or strTest2 etc.)

The DoStuff procedures puts an empty field code at the bookmark, but this actually places outside the bookmark. So the range is moved to bring the field code inside. Then the text of the field code is inserted, and the field updated.

DoStuff terminates and control is passed back to the originating procedure, which then makes a bookmark with the same name. This will now include the INCLUDETEXT field.

Note that as it is written the .Result of the INCLUDETEXT will be the entire contents of the document it points to.

This can be otherwise. INCLUDETEXT can point to bookmarks, and in fact I would recomment this.

In other words, you can have ONE document (instead of three; Test1.doc, Test2.doc, Test3.doc) with each appropriate text chunk as a bookmark.

Say you call the document that has all three of the chunks you may want to include: Sources.doc.

And, the three separate chunks are bookmarked as "One", "Two", and "Three".

strTest1 = _
" INCLUDETEXT ""F:\\Neil H\\Sources.doc"" One \* MERGEFORMAT "
strTest2 = _
" INCLUDETEXT ""F:\\Neil H\\Sources.doc"" Two \* MERGEFORMAT "
strTest3 = _
" INCLUDETEXT ""F:\\Neil H\\Sources.doc"" Three \* MERGEFORMAT "

This way you can maintain ONE document (Sources.doc) with all the chunks you need.