Getting embeded Excel worksheets with macros in Word to work
 

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 a

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

Getting embeded Excel worksheets with macros in Word to work
 

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
HIS


wrote in message
oups.com...
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 a

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

Getting embeded Excel worksheets with macros in Word to work
 

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.

I haven't tested this, but the first thing I'd do is add the application object "Excel." before each excel object declaration.

i.e.
Dim destWB As Excel.Workbook
and not
Dim destWB As Workbook

Also - I'd avoid using Excel constants such as xlPasteValues. I'd suggest looking up the constant value then declaring it as a number.

If this doesn't work as is, you may also need to fiddle with <Application.|<Creator.|<Parent.|... accessor objects (in varying combinations) to specify to the VBA engine that it's the Excel object embedded within the current Application that you're referring to.

Sorry I can't be more specific than that - I haven't got time to research it fully at the moment. Suffice to say that I've used the Creator and Parent properties a number of times in varying configurations when working between mixed-application documents. The correct sequence to use is not always obvious.

Regards,
BizMark