Excel macro works on all but one computer/laptop

[mactravel]

Good Afternoon,

I have an excel workbook that contains a worksheet named "Requisition". After the user populates the "Requisition" worksheet they click on a form control button to run a macro. The macro copies the data to a word document (to work around conditional formatting) and the word document is then attached to an outlook e-mail thus leaving the originator of the requisition to simply send the e-mail for approval.

The macro works and has done for 18 months or so. The macro works on every users computer/laptop EXCEPT on one persons laptop (excluding apple mac - a project for later). I have checked the laptop and it has the appropriate folders required to work. The name of the file is correct so that it is correctly recognised by the macro. The trust settings are the same as other users. Everything about the laptop that I can see appears to be set the same as everyone else. However, this particular laptop stops at the line where .Selection.InsertBreak is with the message Run-time error '4605' this method or property is not available because the object refers to a drawing object.

Why would this particular failure occur on one laptop but not 50 plus others?

Very much appreciate any pointers. Am I missing something re object libraries?

I have looked at this for many hours over many weeks and am at a loss. Please help.

Mike

[westconn1]

i can only assume that the selection is not as expected for some reason, which i can not guess at
i always recommend to avoid selecting or activating anything and to as far as possible avoid working with the selection object or active anything

for any further assistance (not that you got any so far), i think you would need to post the vba code, or even attach a workbook (zip first) with some sample data and the macro

[mactravel]

i can only assume that the selection is not as expected for some reason, which i can not guess at
i always recommend to avoid selecting or activating anything and to as far as possible avoid working with the selection object or active anything

for any further assistance (not that you got any so far), i think you would need to post the vba code, or even attach a workbook (zip first) with some sample data and the macro

Thanks Westconn1 for your speedy reply. I use select and activate so I can see how my development is progressing as I am a step by step to getting things automated type of 'developer'. IT is not my main role. I use VB to automate regular tasks where possible and where time allows. Often once it's working I don't often get time to go back and clean it up. I also use it to show users what is happening as they run the macro during training (a sign of my insecurity or to assure users - probably a bit of both).

Apologies but the excel file doesn't appear to want to upload (even zipped it is 539kb). So here is the code (I have stated 'IT STOPS HERE ON ONE PERSONS COMPUTER. EVERYONE ELSE IT WORKS at the point where the error message occurs on the 'rouge' laptop).

Option Explicit
Sub CreateSendPO()
Dim wdApp As Object
Set wdApp = CreateObject("Word.Application")
Dim outApp As Object
Set outApp = CreateObject("Outlook.Application")
Dim exApp As Object
Set exApp = CreateObject("Excel.Application")
Dim myDir As String
myDir = ActiveWorkbook.Path

With exApp
Dim poTo As String
poTo = Workbooks("PR and Budget Opex 2018.xlsm").Sheets("Requisition").Range("E5") & " PO dated " & Format(Now(), "yyyy-mm-dd hh-mm-ss") & ".docx"
End With

With wdApp
.Documents.Add
.Selection.PageSetup.Orientation = 1
.Visible = True
.Activate

Workbooks("PR and Budget Opex 2018.xlsm").Sheets("Requisition").Range("PO_Header").CopyPicture Appearance:=xlScreen, Format:=xlPicture
.Selection.PasteAndFormat (13)

Dim dat As Variant
Dim rng As Range
Dim i As Long
Dim val As String
Dim PO_rng As String

Set rng = Workbooks("PR and Budget Opex 2018.xlsm").Sheets("Requisition").Range("PO_Lines")
dat = rng
val = 1
For i = LBound(dat, 1) To UBound(dat, 1)

If dat(i, 1) <> "" Then
PO_rng = "PO_Line" & val
Workbooks("PR and Budget Opex 2018.xlsm").Sheets("Requisition").Range(PO_rng).CopyPicture Appearance:=xlScreen, Format:=xlPicture
.Selection.PasteAndFormat (13)
.Selection.ParagraphFormat.Spacebefore = 0
val = val + 1
Else
val = val + 1
End If
Next

Workbooks("PR and Budget Opex 2018.xlsm").Sheets("Requisition").Range("PO_Totals").CopyPicture Appearance:=xlScreen, Format:=xlPicture
.Selection.PasteAndFormat (13)
.Selection.InsertBreak 'IT STOPS HERE ON ONE PERSONS COMPUTER. EVERYONE ELSE IT WORKS

If Workbooks("PR and Budget Opex 2018.xlsm").Sheets("Requisition").Range("B35") <> "" Then
Workbooks("PR and Budget Opex 2018.xlsm").Sheets("Requisition").Range("PO_Virements").CopyPicture Appearance:=xlScreen, Format:=xlPicture
.Selection.PasteAndFormat (13)
Else
GoTo skipvire
End If


Set rng = Workbooks("PR and Budget Opex 2018.xlsm").Sheets("Requisition").Range("PO_Vire_Lines")
dat = rng
val = 1
For i = LBound(dat, 1) To UBound(dat, 1)

If dat(i, 1) <> "" Then
PO_rng = "PO_Virements" & val
Workbooks("PR and Budget Opex 2018.xlsm").Sheets("Requisition").Range(PO_rng).CopyPicture Appearance:=xlScreen, Format:=xlPicture
.Selection.PasteAndFormat (13)
val = val + 1
End If
Next
.Selection.InsertBreak

skipvire:

If Workbooks("PR and Budget Opex 2018.xlsm").Sheets("Requisition").Range("New_Supplier_Req") = "Y" Then
Workbooks("PR and Budget Opex 2018.xlsm").Sheets("Requisition").Range("PO_New_Supplier").CopyPicture Appearance:=xlScreen, Format:=xlPicture
.Selection.PasteAndFormat (13)
Else

End If

Workbooks("PR and Budget Opex 2018.xlsm").Sheets("Requisition").Range("PO_Sign_Off").CopyPicture Appearance:=xlScreen, Format:=xlPicture
.Selection.PasteAndFormat (13)


End With

ChDir "C:\Purchase Orders\"


With wdApp
.ActiveDocument.SaveAs2 fileName:="C:\Purchase Orders\" & poTo
.ActiveDocument.SendMail
End With

ChDir myDir

End Sub

Thanks
Mike

[jdc2000]

It would be helpful if you would make a test workbook with just 20 or so lines of data so we could test the macro. That should be small enough to zip and attach.

[mactravel]

It would be helpful if you would make a test workbook with just 20 or so lines of data so we could test the macro. That should be small enough to zip and attach.

Hi. I have tried to upload the file on numerous occasions but upload fails. The smallest I can get the file is 102kb (98kB compressed) which appears to fit the criteria of upload for .zip files. I have unprotected the sheet and removed all formulae but still failing to upload. Do I need to request facility to upload?

Thanks
Mike

[jdc2000]

Do you get an error message when the file fails to upload? If yes, post a screen capture or the contents of it.

[westconn1]

Do I need to request facility to upload?

not that i know of

do you have access to the computer that gives the error?
can you see what is selected when the error occurs?
is that different from other computers?
is that computer using the same version of word?

[mactravel]

I have stepped through the code on said laptop (the 'non-working' computer) and have discovered the following when compared to what happens on my laptop (the 'working' computers).

When objects are pasted into Word on the 'non-working' computer it appears much smaller than it does when the macro runs on a 'working' computer. Word is set to landscape and, on the 'working' computers, the objects generally fill the page between the margins (0-24). However, on the 'non-working' computer the object appears landscape but much smaller only filling from margin (0-16) - not a major problem in itself but probably something I could address by stipulating a size perhaps.

The second anomaly is that when subsequent objects are pasted to the word document on a 'working' computer the objects are pasted below the previous object. On the 'non-working' computer subsequent objects are pasted on top of the previous pasting. After a certain set of objects are pasted I insert a page break. On the 'working' computers where the objects have been pasted below the previous the page break is inserted as intended and the macro goes on to complete the task intended. On the 'non-working' computer the page break fails with the error message. This is possibly where the problem lies (although why only on one computer). I will investigate inserting a line break or carriage return after each object is posted and hopefully that will resolve the issue.

Thank you for your time and guidance.

[westconn1]

you could try collapsing the selection after each pasteandformat

looks like it might be something to do with word settings

[techgnome]

I'm starting to suspect the default Word settings on the one computer. Sounds like maybe this person has a messed up document template, or has a Word macro that's also running and causing interference.

-tg