Open Embedded Word Document/Edit/Print to PDF

grahamd55

New Member
Joined
Mar 30, 2023
Messages
1
Office Version
  1. 365
Platform
  1. Windows
Hello, I've seen similar boards discussing this topic but I have a slightly different Issue that I cannot seem to find an answer for.

I have code that works on my machine. It works great. Its purpose is to open and embedded Word document Modify it with values out of the spreadsheet and then print the word document to a PDF. However, Once I try to use the exact same spreadsheet on another machine (not mine). I get errors that I can't seem to shake.

Code:

Sub PrintLabelsPriSec()
Dim P As Integer
Dim WkbName As String
Dim FileName As String
Dim MNE As String
Dim LZOP As String
Dim OLE As OLEObject
Dim WDObj As Object
Dim WDApp As Object
Dim WDDoc As Word.document

'Prints Relay Labels
Application.ScreenUpdating = False
Set ActiveBook = ActiveWorkbook
WkbName = ActiveSheet.Name
MNE = ThisWorkbook.Sheets("Inputs").Range("MNE_").Value
LZOP = ThisWorkbook.Sheets("Inputs").Range("LZOP_").Value
FilName = MNE & "_" & LZOP & "_" & WkbName & "_LABELS.PDF"

P = MsgBox("Would you like to print relay labels?", 3, "Print Relay Labels")

If P = 6 Then

Worksheets(WkbName & " LABELS").Activate

Set WDObj = ThisWorkbook.Sheets(WkbName & " LABELS").OLEObjects("_" & WkbName & "_LABELS")
WDObj.Select
Selection.Verb Verb:=xlOpen
WDObj.Object.Application.Visible = False


Set WDApp = GetObject(, "Word.Application")
Set WDObj = ThisWorkbook.Sheets(WkbName & " LABELS").OLEObjects("_" & WkbName & "_LABELS")
WDObj.Activate
WDObj.Object.Application.Visible = False
Set WDDoc = WDApp.ActiveDocument
WDApp.Visible = True
WDApp.ActiveDocument.Activate

LEDNUM = 0
PBNUM = 0
'Loops through embedded document to count how many LEDs and Pushbuttons are available
For Each bkmk In WDApp.ActiveDocument.Bookmarks

BkMkName = bkmk.Name
If InStr(BkMkName, "LED") > 0 Then
LEDNUM = LEDNUM + 1
ElseIf InStr(BkMkName, "PB") > 0 Then
PBNUM = PBNUM + 1
End If

Next bkmk
'Loops through each Bookmark in embedded document and replaces them with defined LED Labels
For i = 1 To LEDNUM
With WDApp.ActiveDocument.Bookmarks("LED" & i)
Set bkmRng = .Range
bkmName = .Name
NewText = ThisWorkbook.Sheets(WkbName & " LABELS").Range("_LED" & i).Value
.Range.Text = NewText
bkmRng.End = bkmRng.Start + Len(NewText)
WDApp.ActiveDocument.Bookmarks.Add Name:=bkmName, Range:=bkmRng
End With
Next i
'Loops through each Bookmark in embedded document and replaces them with defined PB Labels
For i = 1 To PBNUM
With WDApp.ActiveDocument.Bookmarks("PB" & i)
Set bkmRng = .Range
bkmName = .Name
NewText = ThisWorkbook.Sheets(WkbName & " LABELS").Range("_PB" & i).Value
.Range.Text = NewText
bkmRng.End = bkmRng.Start + Len(NewText)
WDApp.ActiveDocument.Bookmarks.Add Name:=bkmName, Range:=bkmRng
End With
Next i

'Prints document as PDF
WDDoc.PrintOut , , , "C:\Temp\" & FilName
WDApp.ActiveDocument.Close
WDApp.Quit
MsgBox "Relay labels saved to C:\Temp\" & FilName



Set WDApp = Nothing
Set WDDoc = Nothing
Set WDObj = Nothing

Worksheets(WkbName).Activate

End If

excel version that works:
1680208645480.png


When used on another machine I get

1680209050871.png

On the following line:
1680209091632.png

1680209191993.png

Both machines have the same version/Build of Excel/vba So I'm at a loss as to why it would work on one machine and not another.

Any Help would be greatly appreciated.

Thanks,

DG
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

Forum statistics

Threads
1,223,900
Messages
6,175,276
Members
452,629
Latest member
SahilPolekar

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top