zalmyc
01-20-2012, 10:43 PM
I was hoping somebody could help me with this. I've set up a code in Excel VBA to copy from Excel and paste into word (courtesy of vbaexpress). I'm trying to create a simple error handler in which errors are first routed to the error handler code, and a message box pops up saying that there are errors. This works fine. However, I also want the user to be able to choose to continue anyway - for example, if the user chooses Yes to the message box, it changes the error handling in the code to On Error Resume ContinueAnyway (sort of like Resume Next, but to resume back at the top of the code). The problem is it puts it into a loop. When the error is present, the message box pops up. When I click Yes, it pops up again, etc. Here's the code. I'm using Excel 2010 on Windows 7. Any ideas what I'm doing wrong or how to make it work? Thanks!
Sub PasteToWord()
'set a reference to the Word Object library from your Excel spreadsheet
Dim objWord As Word.Application
Set objWord = CreateObject("Word.Application")
On Error GoTo ErrorHandler
ProceedAnyway:
Sheets("Sheet0").Range("A1:A3").Copy
With objWord
.Documents.Open ("C:\Users\Zalmy\Desktop\PasteDoc.docx")
.ActiveDocument.Bookmarks("FirstBookmark").Select
.Selection.PasteAndFormat (wdFormatPlainText)
.Visible = True
End With
Application.CutCopyMode = False
Sheets("Sheet1").Range("D2:E4").Copy
With objWord
.ActiveDocument.Bookmarks("SecondBookmark").Select
.Selection.PasteAndFormat (wdFormatPlainText)
.Visible = True
End With
Application.CutCopyMode = False
Sheets("Sheet1").Range("K2:L4").Copy
With objWord
.ActiveDocument.Bookmarks("ThirdBookmark").Select
.Selection.PasteAndFormat (wdChartPicture)
.Visible = True
End With
Application.CutCopyMode = False
Exit Sub
ErrorHandler:
Application.CutCopyMode = False
If MsgBox("A copy/paste error task was encountered. Continue anyway?", vbYesNo) = vbYes Then
Resume ProceedAnyway
End If
End Sub
Sub PasteToWord()
'set a reference to the Word Object library from your Excel spreadsheet
Dim objWord As Word.Application
Set objWord = CreateObject("Word.Application")
On Error GoTo ErrorHandler
ProceedAnyway:
Sheets("Sheet0").Range("A1:A3").Copy
With objWord
.Documents.Open ("C:\Users\Zalmy\Desktop\PasteDoc.docx")
.ActiveDocument.Bookmarks("FirstBookmark").Select
.Selection.PasteAndFormat (wdFormatPlainText)
.Visible = True
End With
Application.CutCopyMode = False
Sheets("Sheet1").Range("D2:E4").Copy
With objWord
.ActiveDocument.Bookmarks("SecondBookmark").Select
.Selection.PasteAndFormat (wdFormatPlainText)
.Visible = True
End With
Application.CutCopyMode = False
Sheets("Sheet1").Range("K2:L4").Copy
With objWord
.ActiveDocument.Bookmarks("ThirdBookmark").Select
.Selection.PasteAndFormat (wdChartPicture)
.Visible = True
End With
Application.CutCopyMode = False
Exit Sub
ErrorHandler:
Application.CutCopyMode = False
If MsgBox("A copy/paste error task was encountered. Continue anyway?", vbYesNo) = vbYes Then
Resume ProceedAnyway
End If
End Sub