Section of code errors out for other users, but works fine for me

[bcf0123]

Hello - I have some code that does a number of things and all works fine for me, but not for others. For others, it opens Word but does not populate any data and errors out. I'm newer to this so no clue why this works for me, but not others and I'd like to understand for future coding.

Thanks for taking a look.

Here is the part of code that is giving the error on "wrdApp.Selection.Paste" line

Sheets("Sch1A").Range("Print_Area").Copy

    With objWord

        wrdApp.Selection.Paste

This is not the full set of code, but this is the piece that contains the error and related variables.

'Below is where the embedded word doc opens and pastes in the code
Dim wrdApp As Word.Application

Set wrdApp = CreateObject("Word.Application")

Dim sh As Shape

Dim objWord As Object, objNewDoc As Object ''Word.Document

'Dim objOLE As New OLEObject

Dim objOLE As OLEObject

Dim wSystem As Worksheet

Dim cell As Range

 

Set wSystem = Worksheets("Schedule variables")

''The shape holding the object from 'Create from file'

''Object 2 is the name of the shape

Set sh = wSystem.Shapes("PageBreak")

''The OLE Object contained

Set objOLE = sh.OLEFormat.Object

'Instead of activating in-place, open in Word

objOLE.Verb xlOpen

Set objWord = objOLE.Object 'The Word document

 

Dim objUndo As Object 'Word.UndoRecord

'Be able to undo all editing performed by the macro in one step

Set objUndo = objWord.Application.UndoRecord

objUndo.StartCustomRecord "Edit In Word"

   

Sheets("Sch1A").Range("Print_Area").Copy

    With objWord

        wrdApp.Selection.Paste

        wrdApp.Selection.InsertBreak

    End With

   

'Add footer

wrdApp.ActiveWindow.ActivePane.View.SeekView = wdSeekCurrentPageFooter

wrdApp.Selection.Font.Size = 7

wrdApp.Selection.TypeParagraph

wrdApp.Selection.TypeText ThisWorkbook.Sheets("Schedule variables").Range("S1").Text

'wrdApp.Selection.TypeText vbTab & vbTab & "             " & ThisWorkbook.Sheets("Schedule variables").Range("O5").Text

wrdApp.Selection.TypeParagraph

wrdApp.Selection.Font.Size = 7

wrdApp.Selection.TypeText ThisWorkbook.Sheets("Schedule variables").Range("S2").Text

wrdApp.Selection.TypeParagraph

wrdApp.Selection.Font.Size = 7

wrdApp.Selection.TypeText ThisWorkbook.Sheets("Schedule variables").Range("S3").Text

'wrdApp.Selection.TypeParagraph

'wrdApp.Selection.TypeText vbTab & vbTab & "             " & ThisWorkbook.Sheets("Schedule variables").Range("O7").Text

wrdApp.ActiveWindow.ActivePane.View.SeekView = 0

 

Sheets("Sch1B").Range("Print_Area").Copy

    With objWord

        wrdApp.Selection.Paste

        wrdApp.Selection.InsertBreak

    End With

   

Sheets("Sch2").Range("Print_Area").Copy

    With objWord

        wrdApp.Selection.Paste

        wrdApp.Selection.InsertBreak

    End With

   

Sheets("Sch3").Range("Print_Area").Copy

    With objWord

        wrdApp.Selection.Paste

        wrdApp.Selection.InsertBreak

    End With

   

'Password protect and only allow track changes in Word document

'wrdApp.ActiveDocument.Protect password:="wildcard", NoReset:=False, Type:= _

'       wdAllowOnlyComments, UseIRM:=False, EnforceStyleLock:=False

 

'Save as client name to same path the Excel file is saved and undo everything for the embedded document to be clean

With objWord

    objWord.SaveAs2 ActiveWorkbook.Path & "\" & Sheets("Schedule variables").Range("S1").Value

    objUndo.EndCustomRecord

    Set objUndo = Nothing

    objWord.Undo

    .Application.Quit False

End With

 

Set objWord = Nothing

Set WordDoc = Nothing

Set WordApp = Nothing

 

'TURN BACK ON IN FINAL CODE

'Sheets("Schedule variables").Visible = False

'Sheets("Sch1A").Visible = False

'Sheets("Sch1B").Visible = False

'Sheets("Sch2").Visible = False

'Sheets("Sch3").Visible = False

'ThisWorkbook.Protect password:="wildcard"

 

Application.ScreenUpdating = True

 

'Call EmailFile

 

'Show message box where schedule was saved down

MsgBox Sheets("Schedule variables").Range("S1").Text & " has been saved in this folder " & ActiveWorkbook.Path

 

End Sub

 

[Joe4]

If you go into the VB Editor on your computer, and go to the Tools menu, and click on References, do you see an listing checked that looks something like:
Microsoft Word 16.0 Object Library
(may actually show a different number)

Now check on one of the computers where it does not work. My guess is that they do not have this library selected.
Find it in the list (or the closest match) and select it.
Then try running it again and it should now work on their computer.

 

[bcf0123]

Yes it's turned on. The error I'm getting is Run-time'91' Object variable or With block variable not set.

 

[Joe4]

Yes it's turned on. The error I'm getting is Run-time'91' Object variable or With block variable not set.

That often indicates that you have a "With" statement without a matching "End With" statement.
But if that was the case, I would suspect that would be an issue on your computer too, and not just theirs.

Are you all using the same version of Office?

When you look at the libraries selected on your computer (under Tools -> References), are ALL same references selected on the other computer, or are any missing?

Are you all testing this on the same file?

 

[bcf0123]

That often indicates that you have a "With" statement without a matching "End With" statement.
But if that was the case, I would suspect that would be an issue on your computer too, and not just theirs.

Are you all using the same version of Office?

When you look at the libraries selected on your computer (under Tools -> References), are ALL same references selected on the other computer, or are any missing?

Are you all testing this on the same file?

Yes same references, Office version and we're using the same file.

 

[Joe4]

Those are the most common causes of those sort of errors.
Without having access to the complete code and related files, there is really nothing else I can say.

 

[bcf0123]

Those are the most common causes of those sort of errors.
Without having access to the complete code and related files, there is really nothing else I can say.

Please DM me and let me know how I can send you the file. I cannot post it to a public message board.

 

[Joe4]

To be consistent with the forum rules here (Message Board Rules), especially rule 4, we recommend that users "dummy up" the data first to remove any sensitive data first, and then either post it using the pertinent parts of it using this tool here: XL2BB - Excel Range to BBCode, or upload it to a file sharing site and provide links to the file.

 

[Joe4]

I get an error 13 "Type Mismatch" on this line:

Set objOLE = sh.OLEFormat.Object

I don't really use OLE at all, so I am probably not the best person to try to debug this issue.