|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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. |
#2
|
||||
|
||||
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) (2002-2019) Visit my web site for more programming tips and ready made processes www.gmayor.com |
#3
|
|||
|
|||
Quote:
|
#4
|
||||
|
||||
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) (2002-2019) Visit my web site for more programming tips and ready made processes www.gmayor.com |
#5
|
|||
|
|||
Quote:
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 |
#6
|
||||
|
||||
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.
__________________
Graham Mayor - MS MVP (Word) (2002-2019) Visit my web site for more programming tips and ready made processes www.gmayor.com |
#7
|
|||
|
|||
Quote:
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. |
#8
|
||||
|
||||
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) (2002-2019) Visit my web site for more programming tips and ready made processes www.gmayor.com |
#9
|
|||
|
|||
Quote:
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. |
#10
|
||||
|
||||
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) (2002-2019) Visit my web site for more programming tips and ready made processes www.gmayor.com |
#11
|
|||
|
|||
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 |
Tags |
vba, word-vba |
|
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 |