Modifications to code in Access to modify Word doc (1 Viewer)

sxschech

Registered User.
Local time
Today, 10:45
Joined
Mar 2, 2010
Messages
721
I've seen a few discussions about interacting with Word documents from Access and thought I would post a few questions regarding some items that aren't functioning very well.
Background: A set of word documents were developed to be used as templates (starting point as they aren't real templates). Users, being users, do some of the following...Add unnecessary spaces, remove hard returns, turn on tracking and don't remove it once approved, upper case where lower case is requested, use earlier versions of a template, etc.

Even though we had buy-in from management initially, there was too much push back from the users refusing to follow the guidelines and thus correcting the documents fell back on us. I put together some code in a function called from an access form to review the documents. It does most of what I would like it to do, such as inserting letter numbers, correcting the subject/title and filename etc. However, there are a few parts that don't quite do what is being requested.

Included a few screen shots to see if helps illustrate the issues. Didn't include all of the code, but can provide if needed.

1. The command to turn off track changes doesn't seem to work for me.
Code:
worddoc.Revisions.AcceptAll                 'accept all changes and stop tracking
2. Tried to figure out how to remove spaces where user added them because they didn't adhere to the existing Tab Stops or apply the predefined alignment of the template
I ended hard coding and had to have a few sets with exact match to the number of spaces to remove because I couldn't figure out how determine a variable amount, thus any docs we receive that don't have the exact phrase such as below aren't corrected via the code. After the below code removes the spaces, I still have to manually use tab stops or alignment to get the text to move to its desired location.
Code:
'get rid of excess spaces
    '20210608
    FindReplaceAnywhere worddoc, "                             IBI", "IBI", True
    DoEvents
    FindReplaceAnywhere worddoc, "                             IBI", "IBI", True
    DoEvents
    FindReplaceAnywhere worddoc, "                 IBI", "IBI", True
    DoEvents
    FindReplaceAnywhere worddoc, "            IBI", "IBI", True

3. Lowercase. Used the LCase function within a replace function and it finds the phrase, does the replacement but doesn't seem to change some of the text to lower case. Looks like it leaves the first letter upper case.
Code:
FindReplaceAnywhere worddoc, stOrigText, LCase(stcc)

BEFORE:
cc: AA, BB, CC, DD

AFTER:
cc: Aa, bb, cc, dd

DESIRED:
cc: aa, bb, cc, dd

4. How to insert hard return(s) to allow empty space for a person to write their signature. If less than three hard returns, add returns until there are at least three.
4a. If there are more than 3 hard returns (empty rows) remove until there are only 3
I tried this, but imagine that perhaps word doesn't recognize vbcrlf? (Also probably should not be hard coding the number of returns in case there are more or less as well as not having to hard code the name of the signer)
Code:
FindReplaceAnywhere worddoc, "Regards," & vbCrLf & vbCrLf & "Lorum Ipsum", "Regards," & vbCrLf & vbCrLf & vbCrLf & "Lorum Ipsum"

Code:
Public Sub FindReplaceAnywhere(worddoc As Object, pFindTxt As String, pReplaceTxt As String, Optional blBodyOnly As Boolean)
'This version relies on word already being open and is called from another sub/function
'Originally wanted to only edit the header, however since this code searches all, leaving
'as is for now.  If the amount of time to run takes too long, may try to narrow down to
'only look at header.  Story Range Header StoryType is 10.
'MODIFIED FROM FindRpleaceAnywhereOrig --Actually the name of the Original did not have ORIG
'https://wordmvp.com/FAQs/Customization/ReplaceAnywhere.htm
'Suggested by GasMan Post#6
'https://www.access-programmers.co.uk/forums/showthread.php?t=307233
'20191008
'Added option to only do find and replace on the body so that if don't need to
'search through headers and footers, will only replace first instance and then
'return to the calling function
'20191216
    Dim rngStory As Object
    Dim lngJunk As Long
    Dim oShp As Shape
    
    'Fix the skipped blank Header/Footer problem
    lngJunk = worddoc.Sections(1).Headers(1).Range.storytype
    'Iterate through all story types in the current document
    For Each rngStory In worddoc.storyranges
      'Iterate through all linked stories
      Do
        SearchAndReplaceInStory rngStory, pFindTxt, pReplaceTxt
        If blBodyOnly = True Then
            Exit Sub
        End If
        On Error Resume Next
        Select Case rngStory.storytype
            Case 6, 7, 8, 9, 10, 11
                If rngStory.ShapeRange.Count > 0 Then
                    For Each oShp In rngStory.ShapeRange
                        If oShp.TextFrame.HasText Then
                            SearchAndReplaceInStory oShp.TextFrame.TextRange, _
                            pFindTxt, pReplaceTxt
                        End If
                    Next
                End If
            Case Else
                'Do Nothing
        End Select
        On Error GoTo 0
        'Get next linked story (if any)
        Set rngStory = rngStory.NextStoryRange
        Loop Until rngStory Is Nothing
    Next
End Sub
Code:
Public Sub SearchAndReplaceInStory(ByVal rngStory As Object, ByVal strSearch As String, ByVal strReplace As String)
'https://wordmvp.com/FAQs/Customization/ReplaceAnywhere.htm
'21091008
    With rngStory.Find
        .ClearFormatting
        .Replacement.ClearFormatting
        .Text = strSearch
        .Replacement.Text = strReplace
        .Wrap = 0 'wdFindContinue
        .Execute Replace:=1 '1-wdReplaceOne; 2-wdReplaceAll
    End With
End Sub
 

Attachments

  • NeedMoreReturns.PNG
    NeedMoreReturns.PNG
    1.3 KB · Views: 132
  • RemoveEmptyRowofSpaces.PNG
    RemoveEmptyRowofSpaces.PNG
    2.7 KB · Views: 26
  • AlignLetterNo.PNG
    AlignLetterNo.PNG
    9.2 KB · Views: 126

Pat Hartman

Super Moderator
Staff member
Local time
Today, 13:45
Joined
Feb 19, 2002
Messages
33,681
Are these word templates used by the Access app to fill bookmarks with data? If not, I don't understand how you got involved with something that should be the responsibility of the users. Fixing this stuff up using code in Access is a pretty scary concept. I always think of automating Word as playing pin the tail on the donkey or blind man's bluff as a kid.

When the template is created, it should be saved as a .dot file. That way when the user uses it, a new .docx file is actually created and that is where his changes are made. Only authorized people should actually change the .dot file to avoid just the issues you are trying to correct.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 12:45
Joined
Feb 28, 2001
Messages
21,151
Looking at this another way, I understand why you might say "a template but not a REAL template." Been there, done that - by making copies of the pseudo template and letting them hose that COPY of the file to tears. (Which they surely did.)

But there is this factor that is actually the same reason that we say "NEVER let a user see behind the scenes." Folks CANNOT leave "well enough" alone. They HAVE to put their personal brand on it, sort of like graffiti on a perfectly good, clean, blank wall. Same exact concept. Just like a dog can't trot by a fire hydrant without putting his own stink on it.

If you really have that much push-back from your users then here is the issue: What you are doing - or what you are expecting THEM to do - is not consistent with their image of the process that they are performing. It is (somehow) outside of their workflow. OR if it has to do with spelling or such, remember that not everyone is a polished writer. I know I'm not, despite my hobbyist writing. There is a conceptual mismatch somewhere.

With apologies to actor Strother Martin from Cool Hand Luke, "What we have heah... is a failure to communicate." Don't EXPECT them to do what you want. ASK them why they aren't doing what you want. You might gain an insight into what IS attainable.
 

sxschech

Registered User.
Local time
Today, 10:45
Joined
Mar 2, 2010
Messages
721
Pat and Doc Man, thanks for replying. Had hoped my brief explanation would have sufficed to avoid the natural suggestions you provided. Our dept gave up trying to get people to comply after all the negativity and push back from many people at a higher level. Initially, our policy was to kick back the files and ask them to redo on the correct file template. This did not go over well. I even spent cumulatively hour-wise at least a full day over several days in an email exchange with one person. Another came over and ranted and then made me go to his office and asked me to look on his screen with dual monitors and tell him item for item what was different between what he used and what was in the template since he could not see any visual difference. Even when I explained, that wasn't sufficient. It was quite something to behold due to the amount of time, not to mention the cost of their salary just so that they could avoid having to rekey or copy paste the text into the proper file. Word (not microsoft word) got back to my supervisor and she said we lost the battle, just correct the contents to how it should be and don't bother them with it going forward.

Since I had already set up the code to do some of the reviewing, I thought if I was close enough maybe there would be a suggestion on how to do those extra items. Anyway, not a big deal if current knowledge base doesn't support that. I'll continue manually adjusting what the code is unable to do for now since it still does quite a bit of what needs to be done.
 

moke123

AWF VIP
Local time
Today, 13:45
Joined
Jan 11, 2013
Messages
2,602
Have you looked through Albert Kallal's Super easy merge word App?

The template documents never get changed and they can be regular .doc or .docx files.
It opens the word merge document hidden, executes the merge, opens a new document from the merged document, and closes the original template. Users never touch the original.

His code is well commented so it makes a pretty good read if nothing else.

edit: There is the ability to edit the originals but that option can be turned off.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 12:45
Joined
Feb 28, 2001
Messages
21,151
With regard to your original item #4 (hard returns)... Word doesn't think of them as returns. They are paragraph marks. Body text in Word is merely any "ordinary" text between two paragraph marks. Spacing and such are merely formatting changes of the text between such marks. You can make a selection by stepping across a paragraph mark and saving your starting location, then stepping again and save the ending location. Once you have a selection range, you can do things to it. Look through this referenced thread for a while. You might be able to see some ways to deal with paragraph markers.


No guarantees that this will be helpful but it reflects work I did on some difficult documents.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 13:45
Joined
Feb 19, 2002
Messages
33,681
You didn't answer the question regarding why you are involved. Is the Access application attempting to automate word to fill bookmarks? Also, if you are the IT person, it is quit likely that your salary is double that of the clerks who are refusing to follow procedures so from the company's perspective, you are not the one who should be fixing clerical errors.

The problem with you attempting to create code to fix silly mistakes is that you would never be able to anticipate all the potential errors so bad letters would still get created and it would be your fault because you were the last person to touch the file.

Clearly there is something going on that I am missing. Doc's suggestion to ask them why they were not able to follow procedures is a good one. You might get some insight into how to fix the templates so they would be more user friendly.

I have to admit, I'm a little hard-nosed about stuff like this. I am perfectly willing to modify the workflow or the template, whatever it takes to get the product the company is looking for but what you are describing is simply unacceptable.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 13:45
Joined
May 21, 2018
Messages
5,686
I am not here to argue how people should do their business. Your question would be better addressed in a Word forum since none of this is Access specific. It is simply Word object module question. No difference if called from Access or from Word. So forget all of the above worthless pontification. You simply have some Word object module questions IMO. I will let others argue your business model.
1. your code accepts all changed but does not turn it off. Look at the documents trackrevision property.
2. I am not at a computer. For the other cases, can you post one with problems and desired results? You can put bogus text if needed.

It is a pretty common practice to write Word "clean up" code, to reformat and correct a document. So you can spend weeks changing your business model, or once i get to my computer spend 15 minutes figuring the code out.
Or simply post in a Word forum asking about the functions and Word manipulations. Do not mention Access, templates, or your business model. Not relevant and as you can see opens an unnecessary can of worms.
 
Last edited:

MajP

You've got your good things, and you've got mine.
Local time
Today, 13:45
Joined
May 21, 2018
Messages
5,686
Not sure if this is of any value because your questions are basic Word manipulation and not Access / Word integration. However I show pushing data to Word from Access and pulling data from access into Word.

 

sxschech

Registered User.
Local time
Today, 10:45
Joined
Mar 2, 2010
Messages
721
I am not here to argue how people should do their business.

Appreciate that comment.

I took your suggestion and found this at the Microsoft site. Since all I need is not to display the revisions, I commented out the other statement I had in my code.
'https://docs.microsoft.com/en-us/office/vba/api/word.document.trackrevisions
Code:
worddoc.ShowRevisions = False

For the paragraph situation, even though is a hard code solution, I found over at
When I changed the vbcrlf to ^p, the extra paragraphs showed up the way I wanted.
Code:
FindReplaceAnywhere worddoc, "Regards," & "^p^p" & "Lorum Ipsum", "Regards," & "^p^p^p^p" & "Lorum Ipsum"

For the lowercase code that kept the first letter as upper case, I performed a second find and replace just on that and that "fixed" it. Of course, downside is the second replace since hard coded means, if the first letter changes from an L to some other letter, then it is useless.

Code:
FindReplaceAnywhere worddoc, stOrigText, LCase(stOrigText)
FindReplaceAnywhere worddoc, "cc: L", "cc: l"

If you still would like me to provide a before and after document, let me know.

I tried to read through the thread of your post#9, but was a bit more than I am ready to delve into.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 13:45
Joined
May 21, 2018
Messages
5,686
My point is that writing Word code to clean up a document is super common, so I am confused on all the Pontification and doom and gloom. For what you posted the procedures are pure Word. So they can be called from any VBA enabled Office Application (Word, Excel, PP, Outlook, Project...). Nothing is unique to Access calling Word, or at least I do not see any. Sure, it would be great if you never get the users to be allowed to make bad documents, but it is what it is.
I tried to read through the thread of your post#9, but was a bit more than I am ready to delve into
I do not think my posts are all that relevant. Your questions are specific to running code against the Word Object Model. My post are more about ways to push and pull data from Word and Access, but I think you are already doing that without problem. The one thing that may be unique and rarely ever demonstrated is building a Word template that pulls data from Access. This allows for an extremely complex Word format to pull data. Most cases push data from Access into Word. That does not seem like your problem. It looked like Access was simply calling your Word code and not integrating with Access data.

However, I am unclear what issues you are still having. Again I think these are pure Word VBA problems so other Word experts can answer better. Some on this forum are OK in Word VBA, but no experts are here as far as I have seen. I am an expert in Word to Access integration, but not on pure Word VBA. Other forums would be better for manipulating a Word document. If you can now restate what you are still having issues with it would help.

One thing. The Word Search/find methods are very robust. It can do pattern searches like Regular Expressions which is a super powerful way to search. If not familiar with RegExp (regular expressions)
In other words it searches for a Pattern and not a specific string. So going back to your original post it can search for IBI with any number of spaces before it. You do not have to call each possible number of spaces.
From your posts it sounds like you just need to make your Word code more flexible, reusable, and simplified. I can look at your code to do that. I will let the others debate if you need to change your business rules, but I can write VBA against any object model if you still need help.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 13:45
Joined
May 21, 2018
Messages
5,686
Take a look at wildcards that can shorten your code.
In this example I put various numbers of spaces before the word "IBI" and turned them all into one space then "IBI"

Code:
Public Sub removeBeforeIBI()
  Dim rng As Range
  Set rng = ActiveDocument.Content
  With rng.Find
    .Text = " @IBI"
    .MatchWildcards = True
    With .Replacement
    .Text = " IBI"
    End With
    .Execute Replace:=wdReplaceAll
  End With
End Sub

You can also do this looking for a vaiable number of carriage returns.
 

sxschech

Registered User.
Local time
Today, 10:45
Joined
Mar 2, 2010
Messages
721
MajP, thanks again. As you surmised, I am using access as a tool to take out some of the drudgery and apply some automation (rather than do it the way my department assumes doing it which is completely manual/hands on) to my work so it is not really being used as a database in the traditional sense such as entering, storing data and running reports or doing analysis.

I have used some regular expressions for other aspects of the text like fixing date formats and adding leading zeroes, however, haven't used it in the context you describe. Glad we were able to make some headway on this. ...thinking out loud...I'm concerned that using your example will still leave me with some manual intervention because the situation is that the place where I need to remove spaces should end up with no leading spaces and if I alter your code to remove the leading space, then would that not imply that it would remove the single space for the phrase elsewhere in the document where we want to preserve a space or maybe a couple of spaces since theoretically the search term could exist elsewhere in the document?

Maybe I'll look into some word forums. I mainly posted here because I was using access as the starting point and sometimes I found when copying code from a word vba example the code sometimes needs an adjustment as it may generate a compile error or seemingly ignore the command -- or maybe that is due to late binding?
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 13:45
Joined
May 21, 2018
Messages
5,686
I'm concerned that using your example will still leave me with some manual intervention because the situation is that the place where I need to remove spaces should end up with no leading spaces and if I alter your code to remove the leading space, then would that not imply that it would remove the single space for the phrase elsewhere in the document where we want to preserve a space or maybe a couple of spaces since theoretically the search term could exist elsewhere in the document?
Is there some kind of rule or more involved pattern. If doing this manually, how do you know when you want to remove the spaces before IBI and when to leave them? If it is a specific location you can define the range to only search there. For example you are searching for anything in the first section or before the 2nd paragraph.
 

sxschech

Registered User.
Local time
Today, 10:45
Joined
Mar 2, 2010
Messages
721
It is "usually" on the 7th and 8th lines. It is supposed to be on the same row as the date and the one after, but of course, sometimes people manage to mess that up too.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 13:45
Joined
May 21, 2018
Messages
5,686
Without seeing it I cannot tell if there is some logic to apply. Maybe it is a single line that only has the search word.
In these cases you sometimes can see the problem, but there is no common rule you can write. Then you may want to make it more like a wizard where it automates the task, but prompts you on what to do. So you might have a menu which assists you in clean up steps. And by the way this can be done all in word because you can make user forms in word. Lets say one task is removing excess spaces before certain words, but not all. In this case you click the button "Remove Excess Spaces". It prompts you for the search word. Then when it finds the word with spaces you can choose to replace with No Spaces, One Space, Two Spaces or leave as is. So it automates a lot of the work, but still allows the user to validate the change.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 13:45
Joined
May 21, 2018
Messages
5,686
I often see this in Access where people want some logic to clean up large tables. The problem is you can tell the data is bad when you see it, but there is no set of rules that can be written for all the cases. Or the logic would be so hard to write it would be hundreds of lines of code. Then instead of spending hours writing code you write some code that handles the majority of cases. Then you can build a form that steps you through the potentially bad records and lets you make a decision. In this case you may be able to get through thousands of records in a relatively short time.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 13:45
Joined
Feb 19, 2002
Messages
33,681
but of course, sometimes people manage to mess that up too.
You are never going to be able to anticipate all the mistakes they can make and once you take on this task, YOU will own the errors. Hopefully,, for your sake they won't cause the company to lose money or clients.
 

Users who are viewing this thread

Top Bottom