Hi there, please help.
I am using Word VBA to import a range of data (may include chart) from Excel. In Word, I used Inlineshape object to act as a Excel object. Once the data is imported, user needs to be able to modify if they wish; however, the file is not linked to original file. I have problem importing the data into excel. The pastespecial method somehow does not work. Here are the code:
Sub Test7()
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
On Error GoTo Err_Test
Application.ScreenUpdating = False
Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Open(FileName)
xlBook.Application.Visible = False
xlBook.Application.WindowState = xlMinimized
Dim InShape As InlineShape
Dim objXL As Object
Dim gXL As Excel.Worksheet
ActiveDocument.InlineShapes.AddOLEObject ClassType:="Excel.Sheet.8", LinkToFile _
:=False, DisplayAsIcon:=False
Set InShape = ActiveDocument.InlineShapes(1)
InShape.Activate
Set objXL = InShape.OLEFormat.Object
Set gXL = objXL.ActiveSheet
' Method 1 succeed only when in debug mode
xlBook.Application.Range("A30:P88").Copy Destination:=gXL.Range("A1")
' Method 2 Fails completely
xlBook.Application.Range("C30:P88").Copy
gXL.PasteSpecial xlPasteValues
xlApp.CutCopyMode = False
SendKeys "{esc}", True
Exit_Test:
xlBook.Close False
xlApp.CutCopyMode = False
xlApp.Quit
Set xlBook = Nothing
Set xlApp = Nothing
Set InShape = Nothing
Set xlObj = Nothing
Application.ScreenUpdating = True
Exit Sub
Err_Test:
MsgBox Err.Description
GoTo Exit_Test
End Sub
I have method 1 and method 2, but none of them seems to work.
Thank you for your help.