My Assistant
![]()
Custom Search
|
![]() ![]() |
![]() |
![]() Post#1 | |
Posts: 117 Joined: 13-January 05 From: Phoeinx, AZ, USA ![]() | Hi - I have a fairly large document in which I use a Data Sheet to populate many Bookmarks which are used within the document by "Ref" Fields. I use a Command Button to run the update. Up until recently the person populating the Bookmarks was myself so no problems, but now I need to make it easy for a wide range of users, some with little patience. The problem with populating Bookmarks by typing is that you need to be sure to get the data inside the square brackets"[....]" and it is too easy to wipe out one or both of the brackets. Ideally, I would like to make a simple table with 3 columns, "Description", "Value" and "Bookmark Name", where the user would populate the "Value", and run a macro to populate the Bookmarks behind the scenes. I know how to reference cell values in code in Excel, but have no idea how to do it for a Word Table. Appreciate any assistance. Thanks, Peter |
![]() Post#2 | |
Posts: 1,006 Joined: 25-January 16 ![]() | Fairly common topic and many tutorials. See if this one helps https://www.engram9.info/access-vba-program...osoft-word.html -------------------- Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression. DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx |
![]() Post#3 | |
Posts: 117 Joined: 13-January 05 From: Phoeinx, AZ, USA ![]() | Thanks for your reply but this link doesn't really address my situation. |
![]() Post#4 | |
Posts: 1,006 Joined: 25-January 16 ![]() | How is your situation different? You want to build a table in Word? Currently users are typing data into Word table? If so, then why would bookmarks be involved? If you want Access VBA to add data to table in Word, don't think bookmarks are involved. Review https://social.msdn.microsoft.com/Forums/of...access-database If you want to provide your Word document and Access data for analysis, follow instructions at bottom of my post. What exactly does command button do? What is updated? This post has been edited by June7: Nov 2 2019, 05:18 PM -------------------- Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression. DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx |
![]() Post#5 | |
![]() Posts: 1,369 Joined: 26-December 12 From: Berkshire Mtns. ![]() | I use Albert Kallal's super easy word merge in one of my apps. Everyone can use the docs in a shared library or create their own very easily. http://www.kallal.ca/msaccess/msaccess.html |
![]() Post#6 | |
Posts: 117 Joined: 13-January 05 From: Phoeinx, AZ, USA ![]() | ![]() Hi June7 - Sorry for the confusion. I am attaching a sample of the document which I am trying to create/change. You'll see that the first page is a Data Page I created for use in storing/editing the bookmarks which are referenced throughout the document. This page is never printed as part of the document but used to change data from Aircraft to Aircraft. I used to be the only person making these changes so I was used to the difficulties in over typing existing bookmarks. Now I want to open this up to other users so what I want to do is for them (the other users) to type in data into the yellow highlighted column on the new Data Entry table, and have a macro populate the bookmarks behind the scenes. They could stay on the old data entry page if they actually need a physical location, or they could be hidden if that is possible. |
![]() Post#7 | |
Posts: 1,006 Joined: 25-January 16 ![]() | So you don't want to involve Access database? This data is not maintained in a relational database? Only thing I can find that might serve your requirement is to use FormFields and VBA sets them with values entered into Word table. Word table has cell referencing. Appears cell has a CrLf at end of data. ActiveDocument.FormFields("Text1").Result = Left(ActiveDocument.Tables(1).Cell(1, 3).Range.Text, Len(ActiveDocument.Tables(1).Cell(1, 3).Range.Text) - 2) With FormFields named like Text1, Text2, etc, could try something like: For x = 2 to 92 ActiveDocument.FormFields("Text" & x - 1).Result = Left(ActiveDocument.Tables(1).Cell(x, 3).Range.Text, Len(ActiveDocument.Tables(1).Cell(x, 3).Range.Text) - 2) Next Unfortunately, you seem to duplicate data which complicates. I don't know if your current fields can reference the FormField names. FormFields seem to be created differently from the fields you have but they do show up in Bookmarks list and they look like bookmarks. I tried referencing your existing bookmarks by name and that fails. Your 'new method' table is split into 2 parts - might merge to a single table. This post has been edited by June7: Nov 4 2019, 03:34 PM -------------------- Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression. DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx |
![]() Post#8 | |
Posts: 117 Joined: 13-January 05 From: Phoeinx, AZ, USA ![]() | Thanks June7 - I will play with it when I get a chance. Unfortunately I cannot work on it during the work day as I have other priorities. I really appreciate you taking the time to look at this. Best Regards, Peter |
![]() Post#9 | |
Posts: 117 Joined: 13-January 05 From: Phoeinx, AZ, USA ![]() | Hi June7 - Finally got the Bookmarks to populate based upon the "Value Column". Some Bookmark names give an Error "Bad Bookmark Name" so I put the On Error Resume next" statement in so that it doesn't stop execution. ----------------------------------------------------------------------------------------------------------------------------------------- Sub Mcr_Set_Bookmarks() ' ' Mcr_Set_Bookmarks Macro ' ' Dim BKMkName As Variant Dim BkmkRange As Variant Dim BkmkNameLength As Integer Dim BkmkRangeLength As Integer Dim cV As Integer Dim rV As Integer Dim Endrow As Integer Dim StartRow As Integer '--------------------------------------------------------------------- On Error Resume Next StartRow = 2 Endrow = 94 rV = StartRow For rV = StartRow To Endrow BkmkNameLength = Len(ActiveDocument.Tables(1).Cell(rV, 4).Range) - 2 Debug.Print BkmkNameLength BkmkRangeLength = Len(ActiveDocument.Tables(1).Cell(rV, 3).Range) - 2 Debug.Print BkmkRangeLength BKMkName = Left(ActiveDocument.Tables(1).Cell(rV, 4).Range, BkmkNameLength) Debug.Print BKMkName BkmkRange = Left(ActiveDocument.Tables(1).Cell(rV, 3), BkmkRangeLength) Debug.Print BkmkRange ' With ActiveDocument.Bookmarks ActiveDocument.Tables(1).Cell(rV, 3).Select ActiveDocument.Bookmarks.Add Name:=BKMkName, Range:=Selection.Range Next rV 'End With 'Stop End Sub ------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Im sure that it's a bit "Clunky" by Pro standards but it seems to work. Any Idea what generates the "Bad Bookmark Name" Error? Thanks for putting me on the right track. PP |
![]() Post#10 | |
Posts: 1,006 Joined: 25-January 16 ![]() | Which names are causing error? Do they include any characters other than letters or underscore? -------------------- Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression. DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx |
![]() Post#11 | |
Posts: 117 Joined: 13-January 05 From: Phoeinx, AZ, USA ![]() | Hi - it was "AC_LAST_DAY" and "STMT_DATE". "AC_LAST_DAY" I changed to "AC_LD" and it worked. STMT_DATE I just re-typed and it worked. Now I see "S_ SINCE_FC" has skipped when run with the On Error Resume Next Statement but on pasting this I see it has a space in it. The names are not cast in stone and I can modify them accordingly to find ones that work. I just need to go through the Documents which have the "Ref" fields in them to be sure that they all match. Thanks again, PP |
![]() Post#12 | |
Posts: 117 Joined: 13-January 05 From: Phoeinx, AZ, USA ![]() | Hi June7 - My original problem is now solved and my macro to update the Bookmarks based upon the information entered into the data sheet Table works flawlessly. I also created a Macro to remove the bookmarks as a "Cleanup" between iterations I now have a new problem: When the document ref fields which use the bookmarks are updated they insert the table cell and not just the text associated with the bookmark.This creates a new single row table within the document body parent table. I've tried all kinds of Switches but cannot get just the Bookmark Text to populate the Ref Fields. I'm attaching a portion of the document which contains the datasheet showing the bookmarks, and a page with a few "Ref" Fields showing the field codes and how they look when they're updated. I put a snip on that page showing what I mean. Just a note that I always use tables to create boilerplate Word docs as it retains the format and spacing better than just free text. I don't know if this is part of the problem but to create these documents not using tables would be a real pain. Again I thank you in advance for any assistance you can give me as I am completely stumped. Thanks, Peter Attached File(s) |
![]() Post#13 | |
Posts: 3,364 Joined: 27-February 09 ![]() | I think you have to refer to the Excel's Cell.Range.Text property to get just the contents of the cell, and not the cell itself. |
![]() Post#14 | |
Posts: 117 Joined: 13-January 05 From: Phoeinx, AZ, USA ![]() | Unfortunately I'm not using an Excel spreadsheet. It's a Word Table and i dont see that option when adding the bookmark or referencing the bookmark from the "Ref"field. |
![]() Post#15 | |
Posts: 117 Joined: 13-January 05 From: Phoeinx, AZ, USA ![]() | Unfortunately I'm not using an Excel spreadsheet. It's a Word Table and i dont see that option when adding the bookmark or referencing the bookmark from the "Ref"field. |
![]() Post#16 | |
Posts: 1,006 Joined: 25-January 16 ![]() | Still use Range.Text with Word table. That is shown in my example in post #7. This post has been edited by June7: Nov 10 2019, 01:10 AM -------------------- Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression. DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx |
![]() Post#17 | |
Posts: 117 Joined: 13-January 05 From: Phoeinx, AZ, USA ![]() | When I do I get a "Type Mismatch" Error on the Bookmarks.Add line. I've tried Dim as String, Variant,Range and Bookmark with no success. The Debug.Print lines produce the values expected for the Name and Range with no extraneous characters. Surely it has to be something simple here? Please help as I'm at my wits end and ready to abandon the project ![]() ------------------------------------------------------------------------------------------------------------------------------------------- Sub Mcr_Set_Bookmarks() ' ' Mcr_Set_Bookmarks Macro ' This Macro Takes the User Inputted Data in the "Value" Column of the Data Sheet Table ' and allocates the Bookmark Name from the "Bookmark Name" Column Dim BKMkName As Variant Dim BkmkRange As String Dim BkmkNameLength As Integer Dim BkmkRangeLength As Integer Dim rV As Integer Dim Endrow As Integer Dim StartRow As Integer '--------------------------------------------------------------------- 'On Error Resume Next 'StartRow = Val(InputBox("Enter First Row Number Which Contains Data", "First Row")) StartRow = 2 Endrow = ActiveDocument.Tables(1).Rows.Count rV = StartRow '------------------------------------------------------------------ Loop Through all Values in Table and allocate Bookmarks For rV = StartRow To Endrow BkmkNameLength = Len(ActiveDocument.Tables(1).Cell(rV, 4).Range) - 2 BkmkRangeLength = Len(ActiveDocument.Tables(1).Cell(rV, 3).Range) - 2 BKMkName = Left(ActiveDocument.Tables(1).Cell(rV, 4).Range, BkmkNameLength) Debug.Print BKMkName BkmkRange = Left(ActiveDocument.Tables(1).Cell(rV, 3), BkmkRangeLength) ActiveDocument.Tables(1).Cell(rV, 3).Select BkmkRange = Left(Selection.Text, BkmkRangeLength) Debug.Print BkmkRange ActiveDocument.Bookmarks.Add Name:=BKMkName, Range:=BkmkRange Next rV End Sub |
![]() Post#18 | |
Posts: 1,006 Joined: 25-January 16 ![]() | Downloaded your document and ran code unchanged. Both procedures work perfectly. However, my entries are not reflected in body of document. What am I missing? Ah, right click on field and select Update. This is where you get the new table/cell. Don't know if I can solve this. How would an entire document be updated? Why is following line repeated: Endrow = ActiveDocument.Tables(1).Rows.Count This post has been edited by June7: Nov 10 2019, 05:18 PM -------------------- Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression. DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx |
![]() Post#19 | |
Posts: 117 Joined: 13-January 05 From: Phoeinx, AZ, USA ![]() | Hi June7 - Appreciate your reply. Didn't realize that the endrow line was repeated. The entire document would be updated be selecting Ctrl+A and Ctrl+ F9, or I would put a Cmd button on the Datasheet page to run code to execute those commands if the person performing the operation isn't computer savvy. |
![]() Post#20 | |
Posts: 1,006 Joined: 25-January 16 ![]() | I can't fix the Refs to Bookmarks creating a table row. I would have to use VBA as shown in post #7. This post has been edited by June7: Nov 10 2019, 06:59 PM -------------------- Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression. DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx |
![]()
Custom Search
|
![]() | Search Top Lo-Fi | 8th December 2019 - 03:41 AM |