Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Word > Word VBA

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 09-09-2018, 10:40 PM
kateabode kateabode is offline Windows 7 64bit Office 2016
Novice
 
Join Date: Sep 2018
Posts: 16
kateabode is on a distinguished road
Default Auto-populating a document pulling data from external doc using a Userform with Checkboxes

I need to make a document for my work. I've been doing my best at finding the answers on the net but just keep coming up against errors and it's just not working.



I have a userform with checkboxes. Multiple checkboxes need to be selected. Basically what I was thinking was...

If A is selected insert data from xyz.doc at bookmark 1

If B is selected insert data from xyz.doc at bookmark 2

Then I also read a post on here about docvariables which i'm not entirely sure about.

The inital code I had was as per below, however I'm it's not working as there's some error with the Else statement however I don't necessarily want the Else statement as I don't want to have one or the other checked, I want to be able to have multiple check boxes checked and pull data from each section in Word (or Excel, whatever you think is best)

Private Sub CommandButton1_Click()

Dim DocSrc As Document, DocTgt As Document, RngSrc As Range, RngTgt As Range

Set DocTgt = ActiveDocument

Set DocSrc = Documents.Open("C:\Users\reception.AIRDG\Documents \Custom Office Templates\2019 Master Specification - Kate.dotm")

Set RngTgt1 = DocTgt.Bookmarks("spec").Range

Set RngTgt2 = DocTgt.Bookmarks("surveys").Range



With ActiveDocument

If CheckBox1.Value = True Then

With DocSrc

Set RngSrc = .Range(.Bookmarks("Demo").Range.Start, .Bookmarks("EndDemo").Range.End)

RngSrc.Copy

With DocTgt

RngTgt1.Paste

Else

If CheckBox2.Value = True Then

With DocSrc

Set RngSrc = .Range(.Bookmarks("Surveys").Range.Start, .Bookmarks("Endsurveys").Range.End)

RngSrc.Copy

With DocTgt

RngTgt2.Paste

End If

.Range.Fields.Update

Application.ScreenUpdating = True

I really hope someone can help me out as I just keep going round and round in circles without really getting anywhere.
Reply With Quote
  #2  
Old 09-10-2018, 01:34 AM
gmayor's Avatar
gmayor gmayor is offline Windows 10 Office 2016
Expert
 
Join Date: Aug 2014
Posts: 2,619
gmayor is a jewel in the roughgmayor is a jewel in the roughgmayor is a jewel in the roughgmayor is a jewel in the rough
Default

You appear to have some confusion with your syntax, and while I can't test it without access to the two documents the following should be a deal closer to what you require


Code:
Private Sub CommandButton1_Click()
Dim DocSrc As Document, DocTgt As Document
Dim RngSrc As Range, RngTgt As Range

    Set DocTgt = ActiveDocument
    Set DocSrc = Documents.Open("C:\Users\reception.AIRDG\Documents \Custom Office Templates\2019 Master Specification - Kate.dotm")

    If CheckBox1.Value = True Then
        Set RngSrc = DocSrc.Bookmarks("Demo").Range
        Set RngTgt = DocTgt.Bookmarks("spec").Range
        RngTgt.FormattedText = RngSrc.FormattedText
        RngTgt.Bookmarks.Add "spec"
    End If

    If CheckBox2.Value = True Then
        Set RngSrc = DocSrc.Bookmarks("Surveys").Range
        Set RngTgt = DocTgt.Bookmarks("surveys").Range
        RngTgt.FormattedText = RngSrc.FormattedText
        RngTgt.Bookmarks.Add "surveys"
    End If

    DocTgt.Range.Fields.Update
    Application.ScreenUpdating = True
    Set DocSrc = Nothing
    Set DocTgt = Nothing
    Set RngSrc = Nothing
    Set RngTgt = Nothing
End Sub
__________________
Graham Mayor - MS MVP (Word)
Visit my web site for more programming tips and ready made processes www.gmayor.com
Reply With Quote
  #3  
Old 09-10-2018, 04:17 PM
kateabode kateabode is offline Windows 7 64bit Office 2016
Novice
 
Join Date: Sep 2018
Posts: 16
kateabode is on a distinguished road
Default

Quote:
Originally Posted by gmayor View Post
You appear to have some confusion with your syntax, and while I can't test it without access to the two documents the following should be a deal closer to what you require


Code:
Private Sub CommandButton1_Click()
Dim DocSrc As Document, DocTgt As Document
Dim RngSrc As Range, RngTgt As Range

    Set DocTgt = ActiveDocument
    Set DocSrc = Documents.Open("C:\Users\reception.AIRDG\Documents \Custom Office Templates\2019 Master Specification - Kate.dotm")

    If CheckBox1.Value = True Then
        Set RngSrc = DocSrc.Bookmarks("Demo").Range
        Set RngTgt = DocTgt.Bookmarks("spec").Range
        RngTgt.FormattedText = RngSrc.FormattedText
        RngTgt.Bookmarks.Add "spec"
    End If

    If CheckBox2.Value = True Then
        Set RngSrc = DocSrc.Bookmarks("Surveys").Range
        Set RngTgt = DocTgt.Bookmarks("surveys").Range
        RngTgt.FormattedText = RngSrc.FormattedText
        RngTgt.Bookmarks.Add "surveys"
    End If

    DocTgt.Range.Fields.Update
    Application.ScreenUpdating = True
    Set DocSrc = Nothing
    Set DocTgt = Nothing
    Set RngSrc = Nothing
    Set RngTgt = Nothing
End Sub
Thank you so much! This has helped a little bit. The document is now opening, however it's not copy and pasting the bookmarked sections from the other document to the activedocument, as it doesn't seem that there is anything in the code about copy and paste. Would it be better if I was pulling the data from an excel spreadsheet instead of a doc? Also, just for learning purposes, can you explain to me about the formatted text code, and the end code where you're setting them to Nothing? Your help is greatly appreciated!
Reply With Quote
  #4  
Old 09-10-2018, 08:32 PM
gmayor's Avatar
gmayor gmayor is offline Windows 10 Office 2016
Expert
 
Join Date: Aug 2014
Posts: 2,619
gmayor is a jewel in the roughgmayor is a jewel in the roughgmayor is a jewel in the roughgmayor is a jewel in the rough
Default

You don't need to copy and paste when using ranges. You can simply write one range to the other. Basically it writes what is in one named bookmark to the other.

It sounds like the source document has nothing in the named bookmark because you are using two bookmarks to set the sources. Bookmark the whole of the text segment and don't use start and end bookmarks.

The use of an Excel source for the data is not necessary.
__________________
Graham Mayor - MS MVP (Word)
Visit my web site for more programming tips and ready made processes www.gmayor.com
Reply With Quote
  #5  
Old 10-08-2018, 09:51 PM
kateabode kateabode is offline Windows 7 64bit Office 2016
Novice
 
Join Date: Sep 2018
Posts: 16
kateabode is on a distinguished road
Default

Quote:
Originally Posted by gmayor View Post
You don't need to copy and paste when using ranges. You can simply write one range to the other. Basically it writes what is in one named bookmark to the other.

It sounds like the source document has nothing in the named bookmark because you are using two bookmarks to set the sources. Bookmark the whole of the text segment and don't use start and end bookmarks.

The use of an Excel source for the data is not necessary.
Thanks Greg! This has really helped.

So I have edited the code for the 60 bookmarks I have. I was encountering a runtime error 5941 saying that the bookmarks weren't in the activedocument so I added code into the Object New Document to add the bookmarks. Then when I try to run the code it adds the bookmarks, opens the userform and external doc all without any errors but doesn't copy anything to the activedocument.

This is a sample of the code - I've only included one bookmark as to include them all would be far too long.

HTML Code:
Private Sub CommandButton1_Click()
Dim DocSrc As Document
Dim DocTgt As Document
Dim RngSrc As Range
Dim RngTgt As Range
    Set DocTgt = ActiveDocument
    Set DocSrc = Documents.Open("C:\Users\reception.AIRDG\Documents\Custom Office Templates\2019 Master Specification - Kate.dotm")

    If CheckBox1.Value = True Then
        Set RngSrc = DocSrc.BookMarks("A").Range
        Set RngTgt = DocTgt.BookMarks("A").Range
        RngTgt.FormattedText = RngSrc.FormattedText
        RngTgt.BookMarks.Add "A"
    End If

 DocTgt.Range.Fields.Update
    Application.ScreenUpdating = True
    Set DocSrc = Nothing
    Set DocTgt = Nothing
    Set RngSrc = Nothing
    Set RngTgt = Nothing
End Sub
Reply With Quote
  #6  
Old 10-09-2018, 12:15 AM
gmayor's Avatar
gmayor gmayor is offline Windows 10 Office 2016
Expert
 
Join Date: Aug 2014
Posts: 2,619
gmayor is a jewel in the roughgmayor is a jewel in the roughgmayor is a jewel in the roughgmayor is a jewel in the rough
Default

Provided the bookmarks exist in both the source and target documents and the source bookmark actually contains text, then the code you posted will work. Bookmark names are case sensitive.

To demonstrate that it works see the attached which does meet the criteria (though I have used an activex button to call the code rather than a userform).
Personally I would use more descriptive bookmark names and not use the same names in source and target, but I have run with what you posted.
bmSrcA and bmTgtA would have been my preference.
Attached Files
File Type: docx 2019 Master Specification - GM.docx (26.2 KB, 8 views)
File Type: docm Bookmarks.docm (25.6 KB, 16 views)
__________________
Graham Mayor - MS MVP (Word)
Visit my web site for more programming tips and ready made processes www.gmayor.com
Reply With Quote
  #7  
Old 10-10-2018, 05:56 PM
kateabode kateabode is offline Windows 7 64bit Office 2016
Novice
 
Join Date: Sep 2018
Posts: 16
kateabode is on a distinguished road
Default

Quote:
Originally Posted by gmayor View Post
Provided the bookmarks exist in both the source and target documents and the source bookmark actually contains text, then the code you posted will work. Bookmark names are case sensitive.

To demonstrate that it works see the attached which does meet the criteria (though I have used an activex button to call the code rather than a userform).
Personally I would use more descriptive bookmark names and not use the same names in source and target, but I have run with what you posted.
bmSrcA and bmTgtA would have been my preference.
Thank you so much. You have helped immensely.
I have inserted the bookmarks into the document correctly now but I'm wondering what the code might be to delete the bookmark if not ticked in the userform?

I have tried the below to no avail.

If CheckBox1.Value = True Then
Set RngSrc = DocSrc.BookMarks("A").Range
Set RngTgt = DocTgt.BookMarks("A").Range
RngTgt.FormattedText = RngSrc.FormattedText
RngTgt.BookMarks.Add "A"
ElseIf CheckBox1.Value = False Then
ActiveDocument.BookMarks("A").Delete
End If

I also tried this.

If CheckBox1.Value = True Then
Set RngSrc = DocSrc.BookMarks("A").Range
Set RngTgt = DocTgt.BookMarks("A").Range
RngTgt.FormattedText = RngSrc.FormattedText
RngTgt.BookMarks.Add "A"
ElseIf CheckBox1.Value = False Then
DocTgt.BookMarks("A").Delete
End If

UPDATE. For some reason it's deleting the bookmark from the Source doc rather than Target doc and I can't seem to get it to work out.

Last edited by kateabode; 10-10-2018 at 08:18 PM.
Reply With Quote
  #8  
Old 10-11-2018, 12:18 AM
gmayor's Avatar
gmayor gmayor is offline Windows 10 Office 2016
Expert
 
Join Date: Aug 2014
Posts: 2,619
gmayor is a jewel in the roughgmayor is a jewel in the roughgmayor is a jewel in the roughgmayor is a jewel in the rough
Default

Use the following

Code:
On Error Resume Next
        If CheckBox1.Value = True Then
        Set RngSrc = DocSrc.Bookmarks("A").Range
        Set RngTgt = DocTgt.Bookmarks("A").Range
        RngTgt.FormattedText = RngSrc.FormattedText
        RngTgt.Bookmarks.Add "A"
    Else
        Set RngTgt = DocTgt.Bookmarks("A").Range
        RngTgt.Text = ""
        RngTgt.Bookmarks.Add "A"
    End If
__________________
Graham Mayor - MS MVP (Word)
Visit my web site for more programming tips and ready made processes www.gmayor.com
Reply With Quote
  #9  
Old 10-11-2018, 05:08 PM
kateabode kateabode is offline Windows 7 64bit Office 2016
Novice
 
Join Date: Sep 2018
Posts: 16
kateabode is on a distinguished road
Default

Quote:
Originally Posted by gmayor View Post
Use the following

Code:
On Error Resume Next
        If CheckBox1.Value = True Then
        Set RngSrc = DocSrc.Bookmarks("A").Range
        Set RngTgt = DocTgt.Bookmarks("A").Range
        RngTgt.FormattedText = RngSrc.FormattedText
        RngTgt.Bookmarks.Add "A"
    Else
        Set RngTgt = DocTgt.Bookmarks("A").Range
        RngTgt.Text = ""
        RngTgt.Bookmarks.Add "A"
    End If
Thanks for your response Greg, however this hasn't worked. It hasn't done anything I don't think. When I didn't select the checkbox corresponding to bookmark A, the bookmark remained in the activedocument.
I am hoping that if not selected then the bookmark will be deleted.
Basically I have a document with lots of bookmarks line after line on an empty page and if i don't check a lot of the bookmarks then there are a whole lot of spaces in the document from where the bookmarks are. I hope that makes sense.
Reply With Quote
  #10  
Old 10-11-2018, 08:32 PM
gmayor's Avatar
gmayor gmayor is offline Windows 10 Office 2016
Expert
 
Join Date: Aug 2014
Posts: 2,619
gmayor is a jewel in the roughgmayor is a jewel in the roughgmayor is a jewel in the roughgmayor is a jewel in the rough
Default

It is Graham not Greg!
If you delete the bookmarks, you cannot use the form to re-edit the document.
If the bookmark is empty to begin with not selectiung it will leave it empty. If it has content it will be removed if the box is unchecked. If the bookmarks are in an empty paragraph, include the paragraph break in the bookmark, and ensure that your bookmarked texts include a paragraph break to restore it when used.
__________________
Graham Mayor - MS MVP (Word)
Visit my web site for more programming tips and ready made processes www.gmayor.com
Reply With Quote
  #11  
Old 10-12-2018, 06:56 AM
gmaxey gmaxey is offline Windows 7 32bit Office 2016
Word MVP 2003-2009
 
Join Date: May 2010
Location: Marble, NC
Posts: 795
gmaxey will become famous soon enough
Default

You can save yourself a lot of code if you use a common naming convention for the form checkboxes, source and target bookmarks:

Code:
Private Sub CommandButton1_Click()
Dim oCtr As Control
Dim oDocSrc As Document
Dim oDocTarget As Document
Dim strBMName As String
Dim oBM As Bookmark
Dim oRng As Range
  'Assumes this form is in the target document.
  Set oDocTarget = ActiveDocument
  'Assumes source document is in the same folder and named Doc A.docm
  Set oDocSrc = Documents.Open(oDocTarget.Path & "\Doc A.docm", , , , , , , , , , , False)
  'Assumes that the associated checkbox, source and target bookmarks have a common name e.g.,
     'checkbox - chkClientAddress, Source bookmark - ClientAddress, Target bookmark - ClientAddress
  For Each oCtr In Me.Controls
    If TypeName(oCtr) = "CheckBox" Then
      strBMName = Mid(oCtr.Name, 4, Len(oCtr.Name) - 3)
      Set oBM = oDocTarget.Bookmarks(strBMName)
      If oCtr = True Then
        Set oRng = oBM.Range
        oRng.Text = oDocSrc.Bookmarks(strBMName).Range.Text
        oDocTarget.Bookmarks.Add strBMName, oRng
      Else
        oRng.Text = vbNullString
        oRng.Paragraphs(1).Range.Delete
      End If
    End If
  Next
End Sub
As stated earlier. If you actually delete the empty paragraph associated with the empty bookmarks you will delete the bookmark.
__________________
Greg Maxey
Please visit my web site at http://www.gregmaxey.com/
Reply With Quote
Reply

Tags
vba, word-vba

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Userform with Multiple Cascading Drop Down Lists Populated with External Source Data venganewt Word VBA 21 05-16-2018 02:05 PM
Auto Populating Data with Drop Down Menu vmangano Excel 4 12-20-2016 09:03 AM
UserForm Dropdown List Not Populating highrise955 Word VBA 14 04-10-2016 12:10 PM
Lack of email addresses auto-populating LarryK Outlook 0 10-10-2012 08:09 AM
"Auto-populating" data-worksheet to worksheet. meggenm Excel 4 02-04-2012 02:04 AM


All times are GMT -7. The time now is 06:53 PM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft