Hi Oxgrid family, ----> z Ozgrid
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
Code
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" 'removed actual file path for confidentiality and replaced with X
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