Runtime Error 462 when Running Code to Export Excel Values into Word Bookmarks

[pastelprincess]

Hi Mr.Excel.com family,

I'm hoping for some direction on how to correct my VBA code. I wrote the below code to pull certain pieces of information from an Excel file and insert them into specific bookmarks within a Word template. I've verified that the Bookmarks exist and are valid, and am pretty sure that I'm missing something obvious as I don't code in VBA much. When I run this code, I get a Runtime Error 462 (The remote server machine does not exist or is not available).

Any help would be appreciated, and thank you! Nikki

Sub test()

Dim wb As Workbook: Set wb = ThisWorkbook
Dim ws As Worksheet
Dim FilePath As String
Dim wdApp As Word.Application
Dim wdDoc As Word.Document

FilePath = "X/Local SOW_Template- Macro V12.dotm" 'replaced actual file path with X for confidentiality'
Workbooks(ThisWorkbook.Name).Activate

Dim FileOnly As String
Dim x As Integer
x = 12
Dim Result As Double
FileOnly = ThisWorkbook.Name

Set wdApp = CreateObject("Word.Application")

With wdApp
    .Visible = True
    .Activate
    .Documents.Open FilePath

Set wdDoc = wdApp.Documents.Open(FilePath)

Dim password As String
password = "password"
For Each ws In Worksheets
ws.Unprotect password:=password
Next ws

Set ws = wb.Sheets("Agency_Deliverables")
With wdDoc.ActiveDocument
    .Bookmarks("Agency_Name").Range.Text = ws.Range("C4").Value
    .Bookmarks("Agency_Name_2").Range.Text = ws.Range("C4").Value
    .Bookmarks("Agency_Name_3").Range.Text = ws.Range("C4").Value
    .Bookmarks("File_Name").Range.Text = FileOnly
End With

Set ws = wb.Sheets("Summary")
With wdDoc.ActiveDocument

    .Bookmarks("Agency_Costs").Range.Text = ws.Range("H4").Value
    .Bookmarks("SOW_Pass_Through_Costs").Range.Text = ws.Range("I4").Value
    .Bookmarks("Total_SOW_Costs").Range.Text = ws.Range("J4").Value

    criteria = Range("J4")
    Result = criteria \ x
    .Bookmarks("Agency_Monthly_Fees").Range.Text = Result

    ws.Range("B2:J15").Copy
    .Bookmarks("Screenshot").Range.PasteExcelTable LinkedToExcel:=False, WordFormatting:=False, RTF:=True

End With

Set ws = wb.Sheets("Co_Op_Information")

With wdDoc.ActiveDocument
.Bookmarks("Co_Op_Number").Range.Text = ws.Range("B2").Value
.Bookmarks("Co_Op_Number_2").Range.Text = ws.Range("B2").Value
.Bookmarks("Co_Op_Legal_Name").Range.Text = ws.Range("B1").Value
.Bookmarks("Co_Op_Legal_Name_2").Range.Text = ws.Range("B1").Value
End With

End With

Set wdApp = Nothing
Set wdDoc = Nothing

End Sub

 

[DanteAmor]

Hi and welcome to MrExcel:

In which line of the macro do you have the error?

You must verify that your word document exists in the path and with the name.
("X/Local SOW_Template- Macro V12.dotm")

I adjusted some lines of your code and it works with my test data.
Run the code in debug mode, pressing the F8 key, so that you can observe the execution of each line of the process.
You will notice that if the file exists, then it will open, show it to you, and begin to replace each of the marks.

Sub test()
  Dim wb As Workbook
  Dim ws As Worksheet
  Dim FilePath As String, FileOnly As String
  Dim wdApp As Word.Application
  Dim x As Integer
 
  FilePath = "X/Local SOW_Template- Macro V12.dotm" 'replaced actual file path with X for confidentiality'
  Set wb = ThisWorkbook
  x = 12
  FileOnly = wb.Name
 
  Set wdApp = CreateObject("Word.Application")
  With wdApp
    .Visible = True
    .Activate
    .Documents.Open FilePath
  End With
 
  With wdApp.ActiveDocument
    Set ws = wb.Sheets("Agency_Deliverables")
      .Bookmarks("Agency_Name").Range.Text = ws.Range("C4").Value
      .Bookmarks("Agency_Name_2").Range.Text = ws.Range("C4").Value
      .Bookmarks("Agency_Name_3").Range.Text = ws.Range("C4").Value
      .Bookmarks("File_Name").Range.Text = FileOnly
 
    Set ws = wb.Sheets("Summary")
      .Bookmarks("Agency_Costs").Range.Text = ws.Range("H4").Value
      .Bookmarks("SOW_Pass_Through_Costs").Range.Text = ws.Range("I4").Value
      .Bookmarks("Total_SOW_Costs").Range.Text = ws.Range("J4").Value
      .Bookmarks("Agency_Monthly_Fees").Range.Text = ws.Range("J4").Value / x  'note: Must be / not \
     
      ws.Range("B2:J15").Copy
      .Bookmarks("Screenshot").Range.PasteExcelTable LinkedToExcel:=False, WordFormatting:=False, RTF:=True
 
    Set ws = wb.Sheets("Co_Op_Information")
      .Bookmarks("Co_Op_Number").Range.Text = ws.Range("B2").Value
      .Bookmarks("Co_Op_Number_2").Range.Text = ws.Range("B2").Value
      .Bookmarks("Co_Op_Legal_Name").Range.Text = ws.Range("B1").Value
      .Bookmarks("Co_Op_Legal_Name_2").Range.Text = ws.Range("B1").Value
  End With
 
  Set wdApp = Nothing
End Sub

 

[pastelprincess]

Hi DanteAmor,

Grateful for your assistance with this, and direction to run in debug mode.

I've verified that the Word template file opens properly when the code is executed, and code works correctly until the red-colored line below- I get a runtime error 438 (Object Doesn't Support this Property or Method):

Set ws = wb.Sheets("Agency_Deliverables")
[COLOR=rgb(184, 49, 47)]With wdDoc.ActiveDocument[/COLOR]
    .Bookmarks("Agency_Name").Range.Text = ws.Range("C4").Value
    .Bookmarks("Agency_Name_2").Range.Text = ws.Range("C4").Value
    .Bookmarks("Agency_Name_3").Range.Text = ws.Range("C4").Value
    .Bookmarks("File_Name").Range.Text = FileOnly
End With

Any suggestions on what this could be triggering this error?

I'm at my wit's end here and would gladly make a donation to your charity of choice as a token of my appreciation!

 

[DanteAmor]

Edit.

 

[DanteAmor]

Any suggestions on what this could be triggering this error?

my suggestion is that you try my code.

apply debug mode in my code

Must be

With wdApp.ActiveDocument

But try my code.

 

[Fluff]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Runtime Error 462 when Running Code to Export Excel Values into Word Bookmarks - OzGrid Free Excel/VBA Help Forum
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[pastelprincess]

my suggestion is that you try my code.

apply debug mode in my code

Must be

With wdApp.ActiveDocument

But try my code.

This worked perfectly for me! Thank you very much for the time you spent reviewing, DanteAmor.

I do have one additional question- I am trying to make sure that the numerical values for Summary Sheet cells "H4", "I4", "J4" and "J4"/12 are exported into the Word bookmarks with two decimal places only (i.e. $0.00). I took the approach of defining strings that correspond to these cells (H4, I4, J4 and Result) and trying to apply a numerical format to them, but this doesn't seem to work and they keep exporting into Word with 6 decimal places.

Do you have any suggestions on how to amend the code to ensure that they only have two decimal places?

Sub test()
  Dim wb As Workbook
  Dim ws As Worksheet
  Dim FilePath As String, FileOnly As String
  Dim wdApp As Word.Application
  Dim x As Integer
 
  FilePath = "https://collaborate.mcd.com/sites/SourcingOperations848-REF692USLocalCo-opScopeSupport/Shared Documents/REF692 US Local Co-op Scope Support/Macro-Enabled Files/Local SOW_Template- Macro V12.dotm"
  Set wb = ThisWorkbook
  x = 12
  FileOnly = wb.Name

Set wdApp = CreateObject("Word.Application")
  With wdApp
    .Visible = True
    .Activate
    .Documents.Open FilePath
  End With

With wdApp.ActiveDocument
    Set ws = wb.Sheets("Agency_Deliverables")
      .Bookmarks("Agency_Name").Range.Text = ws.Range("C4").Value
      .Bookmarks("Agency_Name_2").Range.Text = ws.Range("C4").Value
      .Bookmarks("Agency_Name_3").Range.Text = ws.Range("C4").Value
      .Bookmarks("File_Name").Range.Text = FileOnly

    Set ws = wb.Sheets("Summary")
    
    Dim H4 As Double
    H4 = ws.Range("H4").Value
    ws.Range("H4").NumberFormat = "0.00"
    ws.Range("H4").Value = H4
    
    Dim I4 As Double
    I4 = ws.Range("I4").Value
    ws.Range("I4").NumberFormat = "0.00"
     ws.Range("I4").Value = I4

    Dim J4 As Double
    J4 = ws.Range("J4").Value
    ws.Range("J4").NumberFormat = "0.00"
    ws.Range("J4").Value = J4
    
    Dim Result As Double
    Result = ws.Range("J4").Value / x
    ws.Range("J4").NumberFormat = "0.00"

.Bookmarks("Agency_Costs").Range.Text = H4
      .Bookmarks("SOW_Pass_Through_Costs").Range.Text = I4
      .Bookmarks("Total_SOW_Costs").Range.Text = J4
      .Bookmarks("Agency_Monthly_Fees").Range.Text = Result
      
      ws.Range("B1:J15").CopyPicture
      .Bookmarks("Screenshot").Range.PasteSpecial
      
    Set ws = wb.Sheets("Co_Op_Information")
      .Bookmarks("Co_Op_Number").Range.Text = ws.Range("B2").Value
      .Bookmarks("Co_Op_Number_2").Range.Text = ws.Range("B2").Value
      .Bookmarks("Co_Op_Legal_Name").Range.Text = ws.Range("B1").Value
      .Bookmarks("Co_Op_Legal_Name_2").Range.Text = ws.Range("B1").Value
      
End With
 
  Set wdApp = Nothing

End Sub