I am writing a macro in Word that will copy a large range from an Excel document and paste it into an existing table in the Word document. The macro places the cursor in the table, opens the the specified Excel document, copies the range, and closes the Excel document, leaving the data on the clipboard. As soon as the document closes, Word throws Run-Time Error 4605, "Command not available," on the line where I attempt to paste the range.
Here's the code:
Code:
Dim XLApp As Object
Dim XLDoc As Object
Dim WDApp As Object
Dim filepath As String
filepath = InputBox("Enter the filepath of the spreadsheet:", "Enter filepath", "C:\")
Set XLApp = CreateObject("Excel.Application")
Set WDApp = Word.Application
XLApp.Visible = True
XLApp.Workbooks.Open filepath
'Copy data range (ending cell is an arbitrary large number)
XLApp.CutCopyMode = True
XLApp.ActiveWorkbook.Worksheets(1).Range("A2:G650").Select
XLApp.Selection.Copy
XLApp.ActiveWorkbook.Close
XLApp.Application.Quit
'Paste the range, merging with the existing table
With WDApp
Selection.PasteAppendTable '*****Error thrown here
End With
I have tried replacing the contents of the With WDApp block with the following code, attempting to access the clipboard:
Code:
Dim MyData As MSForms.DataObject
Dim strClip As Variant
Set MyData = New MSForms.DataObject
MyData.GetFromClipboard
strClip = MyData.GetText
strClip.PasteAppendTable
But this code throws the same error on the line strClip.PasteAppendTable.
When I end the code (after the error), "append table" does appear as a special paste option on the Ribbon button. I'm not sure why VBA can't find the command. Any help/advice/insight is greatly appreciated.
07-15-2013, 06:45 AM
PasteAppendTable not available (Run-Time Error 4605)
Similar Threads
PasteAppendTable Question
Windows 7 64bit 
Office 2010 32bit
Linear Mode
