Getting embeded Excel worksheets with macros in Word to work

[dwight.yorke]

I am currently trying to get a macro which works fine in MS Excel to
work as an embeded object in MS Word. I have a feeling it has something

to do with the references. The refernces currently selected are:

MS Excel 9.0 Object Library
MS Word 9.0 Object Library
OLE Automation
MS Office Object Library
Visual Basic for Applications
and Normal.


VBA gives the Compile Error: Method or Data Member not found


Any help is greatly appreciated
-Dwight

 

[Nick Hodge]

Dwight

It will be difficult to know what is making the error without seeing the
code and what automation objects you are using. This determines what
libraries you will need to add.

The list looks like it should work for basic VBA in Excel or Word

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
(e-mail address removed)

 

[dwight.yorke]

Function LastRow(sh As Worksheet)
On Error Resume Next
LastRow = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function


Function bIsBookOpen(ByRef szBookName As String) As Boolean
On Error Resume Next
bIsBookOpen = Not (Application.Workbooks(szBookName) Is Nothing)
End Function

Sub copy_to_another_workbook()
Dim sourceRange As Range
Dim destrange As Range
Dim destWB As Workbook
Dim smallrng As Range
Dim I As Integer
Dim lr As Long

Application.ScreenUpdating = False
If bIsBookOpen("test.xls") Then
Set destWB = Workbooks("test.xls")
Else
Set destWB = Workbooks.Open("h:\test.xls")
End If

I = 1
lr = LastRow(destWB.Worksheets("Sheet1")) + 1
For Each smallrng In ThisWorkbook.Worksheets("Sheet1"). _
Range("B1:B1, B5:B5, E2:E2, B2:B2, K2:K2").Areas

Set destrange = destWB.Worksheets("Sheet1").Cells(lr, I)
smallrng.Copy
destrange.PasteSpecial xlPasteValues, , False, False
Application.CutCopyMode = False
I = I + smallrng.Rows.Count
Next smallrng
destWB.Close True
Application.ScreenUpdating = True
End Sub


There is the code. What it is supposed to do is take data from Cells
B1, B5, E2, etc. from an embedded worksheet in a word document an place
it in seperate excel file called test.xls. The macro works fine from
excel to excel but not as as embedded object in Word.