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.
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.
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.
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)
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
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