Excel VBA code that Copy/Pastes into a word document is causing my code to fail

user_9924544 0
Viewed 61

I have an excel sheet Which is generating a word document (which then gets converted into PDF)

The code loops through various rows of data and creates a page in word based on the data Part of the loop takes a Graph from Excel and pastes it into the word page.

The code all works fine, and it generates the word document the way I want it. But the copy/paste of the chart is causing me intermittent issues.

The strange behaviour is that : 1 - My code will suddenly exit before it's finished looping through the data. 2 - I can't step through the code using F8 in break mode, as soon as I get to the code that does the pasting, the code just runs everything. 3 - if I put a break point just after the paste, and let the code stop and press f5 continuously, then I don't get the code suddenly exiting.

(Sorry it's such a long explanation)

This is a snippet of my code where the problem is occuring

            IndividualChartSheet.ChartObjects("IndividualCompletionChart").Copy
            DoEvents
            objRange.PasteSpecial Link:=False, DataType:=wdPasteMetafilePicture, Placement:=wdWrapSquare, DisplayAsIcon:=False
            Application.CutCopyMode = False
            DoEvents
            
            Set myShape = objDoc.InlineShapes.Item(objDoc.InlineShapes.Count).ConvertToShape
            myShape.WrapFormat.Type = wdWrapBehind
            myShape.RelativeVerticalPosition = wdRelativeVerticalPositionPage
            myShape.Top = InchesToPoints(2)
            myShape.Left = InchesToPoints(-0.4)

objRange is a reference to a range in my word object.

I can get the code to run, by putting a breakpoint on the Application.CutCopyMode = False and then pressing F5. But I'm hoping to pass this tool on to other users, so I can't need to fix this.

I added in the DoEvents and the CutCopyMode = False after some searching. I also tried moving the paste command into a seperate function. But that didn't work.

ms-word vba excel
1 Answers

In case anyone is interested I finally go to the bottom of it.

Such a weird one that it took me a really long time to figure out.

Basically part of my code that was constructing the word document was also adding in hyperlinks so the the person looking at my final output (word document converted to a pdf file) could jump around the document.

My hyperlinks and bookmarks were based on employee names and word didn't like me having non alphanumeric characters in the hyperlink/bookmark names. Once I removed those the code runs through without any problems now.

user_99245440
Related