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 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

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
[email protected]HIS


<[email protected]> wrote in message
news:[email protected]...
>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
>

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


[email protected] Wrote:
> 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.


--
BizMark