[X]My Assistant

[pmcpowell]

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

[June7]

Fairly common topic and many tutorials. See if this one helps https://www.engram9.info/access-vba-program...osoft-word.html

[pmcpowell]

Thanks for your reply but this link doesn't really address my situation.

[June7]

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

[moke123]

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

[pmcpowell]

 Automated_Statements_Working_v3.0_Sample.zip ( 90.22K )Number of downloads: 4
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.

[June7]

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

[pmcpowell]

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

[pmcpowell]

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

[June7]

Which names are causing error? Do they include any characters other than letters or underscore?

[pmcpowell]

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

[pmcpowell]

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)

 Automated_Sttements_Working_v_4.0.zip ( 77.18K )Number of downloads: 1

 

[MadPiet]

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.

[pmcpowell]

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.

[pmcpowell]

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.

[June7]

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

[pmcpowell]

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

[June7]

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

[pmcpowell]

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.

[June7]

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