VBA functions in one workbook, but not another, no errors found

Question

I have an .xlsm workbook where this VBA (see below) interacts with a Word Document to replace field codes. Seems straight forward, and it worked great...

Then I copied and pasted the code into a new .xlsm, checked the cells, sheet names, and contents, all is the same! The VBA will run, and I get no errors. However, with the new .xlsm, the field codes will no longer be replaced in the document! I have not found anything online which is helpful... Thoughts?

as a side note, I'm not sure if this is related or not, but my "num lock" turns on/off at times during testing this script/function.

Sub Exl_to_Wrd_FieldReplace()
'
' Exl_to_Wrd_FieldReplaceMacro
'
' Setup info for document and captures default DIR for Document
    Dim wordapp As Object
    Dim folderPath As String
    Dim wordDoc As String
    folderPath = Application.ThisWorkbook.Path
    wordDoc = folderPath & "\TestDoc.docx"
'
' Assign Excel cell values for Field Code Text Replacement
    Dim CaseNum As String: CaseNum = Range("C36").Value
    Dim P1LastName As String: P1LastName = Range("C41").Value
    Dim P2FInl As String: P2FInl = Range("C53").Value
    Dim P2LastName As String: P2LastName = Range("C54").Value
    Dim P2ID As String: P2ID = Range("C55").Value
'
' Open Word application and document
    Set wordapp = CreateObject("word.Application")
    wordapp.Documents.Open (wordDoc) 'Uses path from above & Doc name
    wordapp.Visible = True
    wordapp.Activate
'
' Converts Field Vaule text to Field Codes (i.e. ALT + F9)
    SendKeys "%{F9}"
'
' Starts the Find Command to replace field code with hard coded text
    With wordapp.ActiveDocument.Content.Find
'
' Finds 1st field code
        .Text = "^d DOCPROPERTY  IBA|CaseNumber  \* MERGEFORMAT"
        .ClearFormatting
        .Replacement.Text = CaseNum
        .Execute Replace:=wdReplaceAll
' Finds 2nd field code
        .Text = "^d DOCPROPERTY  IBA|P1LastName  \* MERGEFORMAT"
        .ClearFormatting
        .Replacement.Text = P1LastName
        .Execute Replace:=wdReplaceAll
' Finds 3rd field code
        .Text = "^d DOCPROPERTY  IBA|P2FirstInitial  \* MERGEFORMAT"
        .ClearFormatting
        .Replacement.Text = P2FInl 
        .Execute Replace:=wdReplaceAll
' Finds 4th field code
        .Text = "^d DOCPROPERTY  IBA|P2LastName  \* MERGEFORMAT"
        .ClearFormatting
        .Replacement.Text = P2LastName 
        .Execute Replace:=wdReplaceAll
' Finds 5th field code
        .Text = "^d DOCPROPERTY  IBA|P2Number  \* MERGEFORMAT"
        .ClearFormatting
        .Replacement.Text = P2ID
        .Execute Replace:=wdReplaceAll
'        
    End With
'
' Converts Field Codes back to Field Vaule text (i.e. ALT + F9)
    SendKeys "%{F9}"
'
' It is ok to leave doc open, user needs to do final check/edit before save
'
End Sub

I checked both excel sheets, links, VBA and Xml, all is the same.

Why are you replacing the field codes rather than just updating the document property values so that the fields return the values you want? — Rory, Mar 31 '22 at 10:39
We have a system going offline for a long period of time, based on the volume of documents needed to process, we feel a replacement of field codes is more efficient during this time. The documents in the end will be PDF, so the hard coding is acceptable. — Rob_CH_, Mar 31 '22 at 12:15
I honestly don't see how this is more efficient, especially if it doesn't work. ;) — Rory, Mar 31 '22 at 14:39
@Rory Everything is less efficient when VBA doesn't work. The reality is, It worked in the first workbook I created, now it doesn't in a new workbook with the same code, same names, and same directory. I'm asking the forum to help to understand the volatility of the code. — Rob_CH_, Apr 01 '22 at 07:09

score 0 · Answer 1 · answered Mar 31 '22 at 10:37

0

Using SendKeys usually is highly unreliable. To toggle the display of fields in word, you can use ShowFieldCodes, which is a Property of a View-object, and that is a property of a Window.

With wordApp.ActiveDocument.ActiveWindow.View
    .ShowFieldCodes = True                 ' Shows the Code
    .ShowFieldCodes = False                ' Shows the Content 
    .ShowFieldCodes = Not .ShowFieldCodes  ' Toggles the setting (this is what Alt+F9 does) 
End With

answered Mar 31 '22 at 10:37

FunThomas

23,043
3
18
34

Thank you @FunThomas ! I just tried this, but I have the same issue. With your proposal, it works the same way as Alt+F9 did. I had issues earlier with .showFieldCodes hence the SendKeys. In any case, the document toggles between show and hide codes, but still does not replace the text. Is there a better way to select the entire FieldCode which contains a certain text, instead of the entire explicit name? it seems the .Find and .Replace is not consistent in this case between workbooks. – Rob_CH_ Mar 31 '22 at 12:16

VBA functions in one workbook, but not another, no errors found

1 Answers1