VBA paste from Excel to Word ends sub
The overall aim is to take RTF formatted text in Excel and convert it to HTML. I have been unable thus far to get Excel to respond in a sensible way so tried using Word. I am able to convert in Word without issue so want to automate the process by copying the cell out Excel, pasting into Word and then running code to convert the formatting into the way I want.
I am running into an issue where when I paste into Word from Excel VBA, the paste is successful but then it jumps to End Sub.
Sub webtext(r As String) Range(r).Copy Dim wordApp As Word.Application Dim wordDoc As Word.Document Set wordApp = CreateObject("word.Application") Set wordDoc = wordApp.Documents.Add wordApp.Visible = True wordApp.Activate wordApp.Selection.PasteSpecial 'Any code here is missed out z = MsgBox("tada") 'this will never trigger x=5 'this will never set With wordDoc 'Operations on the text pasted would happen here 'but vba never gets here. End With 'Code jumps to here End Sub
I have tried using both wordDoc and wordApp, and both paste and pastespecial but it always has the same result.
Does anyone have any ideas as to what is going on here or how to stop it from always ending after the paste.
EDIT: I have tested this and it worked on Office 2010. It does not work on Office 2013.
It worked fine for me as is
But suggest you try these tweaks to see if this works (AppActivate is redundant).
Sub StartIt() Call webtext("a1:a10") End Sub
Sub webtext(r As String) Range(r).Copy Dim wordApp As Word.Application Dim wordDoc As Word.Document Set wordApp = New Word.Application Set wordDoc = wordApp.Documents.Add wordApp.Visible = True With wordDoc .ActiveWindow.Selection.Paste End With End Sub