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:
When used on another machine I get
On the following line:
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
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:
When used on another machine I get
On the following line:
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