Macro Recording inside embedded Word Object?

[rsexsmith]

I am running Excel 2000 on Win2000. I have a very limited knowledge base in regards to VB, however what I'm attempting to do should be (I think) very simple.

We use a workbook to track student progress, and there is one sheet for each lessson. One half of the sheet uses a standard spreadsheet to track grades, and the other half of the sheet has an embedded word box we use to expand on the grades with comments. I am trying to record a macro which will copy the contents from the spreadsheet cell, and then paste it into the word box. As I record the macro, the recording stops as soon as I access the embedded word object, so opening the object and pasting is then absent in the macro.
I hope this makes sense and that someone can point me in the right direction.
Cheers.

 

[CraigM]

Try using this code. I can't claim to know alot about it, but it seems to work alright for me:

GetObject(, "Word.Application").Selection = Range("A1")

 

[rsexsmith]

I have been able to get most of my macro to work for me. It's a very simple one - all I want to do is highlight a cell, copy 2 relative cells, then open the embedded word object and paste the copied cells.
I have been able to get to the point of opening the object, but I cannot figure out how to use the paste command in the macro.
My current macro is this:

With Selection.Interior
.ColorIndex = 8
.Pattern = xlSolid
End With
ActiveCell.Range("A1:B1").Select
ActiveCell.Offset(0, 1).Range("A1").Activate
Selection.Copy
ActiveSheet.Shapes("Object 8").Select
Selection.Verb Verb:=xlPrimary

At this point I'm sitting in the object ready to paste. I tried recording a macro inside the object and then pasting the contents of that macro into the excel macro, but all I get is an error message. I just can't control the word document from the excel macro.
If anyone could help me figure out how to do this, I'd really appreciate it.
Cheers.